Excel Data Analysis 简明教程

Excel Data Financial Analysis

你可以轻松地使用 Excel 执行财务分析。Excel 为你提供了多个财务函数，例如 PMT、PV、NPV、XNPV、IRR、MIRR、XIRR 等，使你可以快速得出财务分析结果。

You can perform financial analysis with Excel in an easy way. Excel provides you several financial functions such as PMT, PV, NPV, XNPV, IRR, MIRR, XIRR, and so on that enable you to quickly arrive at the financial analysis results.

在本章中，你将学习在何处以及如何对分析使用这些函数。

In this chapter, you will learn where and how you can use these functions for your analysis.

What is Annuity?

年金是以一个连续期间内支付的一系列固定现金形式进行的。例如，退休储蓄、保险金、住房贷款、抵押贷款等。在年金函数中 -

An annuity is a series of constant cash payments made over a continuous period. For example, savings for retirement, insurance payments, home loan, mortgage, etc. In annuity functions −

A positive number represents cash received.
A negative number represents cash paid out.

Present Value of a series of Future Payments

现值为未来一系列付款现在价值的总和。可以使用 Excel 函数计算现值 -

The present value is the total amount that a series of future payments is worth now. You can calculate the present value using the Excel functions −

PV − Calculates the present value of an investment by using an interest rate and a series of future payments (negative values) and income (positive values). At least one of the cash flows must be positive and at least one must be negative.
NPV − Calculates the net present value of an investment by using a discount rate and a series of periodic future payments (negative values) and income (positive values).
XNPV − Calculates the net present value for a schedule of cash flows that is not necessarily periodic.

Note that -

Note that −

PV cash flows must be constant whereas NPV cash flows can be variable.
PV cash flows can be either at the beginning or at the end of the period whereas NPV cash flows must be at the end of the period.
NPV cash flows must be periodic whereas XNPV cash flows need not be periodic.

在本部分中，您将了解如何使用 PV。您将在后面的章节中了解 NPV。

In this section, you will understand how to work with PV. You will learn about NPV in a later section.

Example

假设您正在购买冰箱。销售人员告诉您冰箱的价格为 32000，但您可以选择在 8 年内支付这笔款项，年利率为 13%，年付款为 6000。您还可以选择在每年的年初或年末付款。

Suppose you are buying a refrigerator. The salesperson tells you that the price of the refrigerator is 32000, but you have an option to pay out the amount in 8 years with an interest rate of 13% per annum and yearly payments of 6000. You also have an option to make the payments either at the beginning or end of each year.

您想知道哪种选择对您更有利。

You want to know which of these options is beneficial for you.

您可以使用 Excel 函数 PV -

You can use Excel function PV −

PV (rate, nper, pmt, [fv ], [type])

要计算每一年的末尾支付的现值，请省略 type 或指定 type 的 0。

To calculate present value with payments at the end of each year, omit type or specify 0 for type.

要计算每年末尾支付的现值，请为类型指定 1。

To calculate present value with payments at the end of each year, specify 1 for type.

您将获得以下结果−

You will get the following results −

因此，

Therefore,

If you make the payment now, you need to pay 32,000 of present value.
If you opt for yearly payments with payment at the end of the year, you need to pay 28, 793 of present value.
If you opt for yearly payments with payment at the end of the year, you need to pay 32,536 of present value.

您可以清楚地看到，选项 2 对您有好处。

You can clearly see that option 2 is beneficial for you.

What is EMI?

Investopedia 将等额分期付款 (EMI) 定义为“借款人每个日历月的特定日期向贷款人支付的固定金额。等额分期付款用于每月偿还本金和利息，以便在指定年限内全额偿还贷款。”

An Equated Monthly Installment (EMI) is defined by Investopedia as "A fixed payment amount made by a borrower to a lender at a specified date each calendar month. Equated monthly installments are used to pay off both interest and principal each month, so that over a specified number of years, the loan is paid off in full."

EMI on a Loan

在 Excel 中，您可以使用 PMT 函数计算贷款的 EMI。

In Excel, you can calculate the EMI on a loan with the PMT function.

假设您想获得一笔年利率为 11.5%、贷款期限为 25 年的 5000000 的房屋贷款。您可以按照以下步骤计算您的 EMI−

Suppose, you want to take a home loan of 5000000 with an annual interest rate of 11.5% and the term of the loan for 25 years. You can find your EMI as follows −

Calculate interest rate per month (Interest Rate per Annum/12)
Calculate number of monthly payments (No. of years * 12)
Use PMT function to calculate EMI

如您所见，

As you observe,

Present Value (PV) is the loan amount.
Future Value (FV) is 0 as at the end of the term the loan amount should be 0.
Type is 1 as the EMIs are paid at the beginning of each month.

您将获得以下结果−

You will get the following results −

Monthly Payment of Principal and Interest on a Loan

EMI 包括利息和一部分本金还款。随着时间的推移，EMI 的这两个组成部分会发生变化，从而减少余额。

EMI includes both-interest and a part payment of principal. As the time increases, these two components of EMI will vary, reducing the balance.

若要获得

To get

The interest part of your monthly payments, you can use the Excel IPMT function.
The payment of principal part of your monthly payments, you can use the Excel PPMT function.

例如，如果您以年利率 16% 借入了一笔 1,000,000 的贷款，期限为 8 个月。您可以获得 EMI 的值、递减的利息金额、递增的本金偿还金额以及过去 8 个月的递减贷款余额。在 8 个月结束时，贷款余额将为 0。

For example, if you have taken a loan of 1,000,000 for a term of 8 months at the rate of 16% per annum. You can get values for the EMI, the decreasing interest amounts, the increasing payment of principal amounts and the diminishing loan balance over the 8 months. At the end of 8 months, loan balance will be 0.

请按照以下给定的程序操作。

Follow the procedure given below.

Step 1 − 计算 EMI 如下。

Step 1 − Calculate the EMI as follows.

得出EMI为13261.59卢比。

This results in an EMI of Rs. 13261.59.

Step 2 −如下所示，接下来计算8个月中EMI的利息和本金部分。

Step 2 − Next calculate the interest and principal parts of the EMI for the 8 months as shown below.

你将得到以下结果。

You will get the following results.

Interest and Principal paid between two Periods

在两个时段（含）内，可以计算已支付的利息和本金。

You can compute the interest and principal paid between two periods, inclusive.

Compute the cumulative interest paid between 2nd and 3rd months using the CUMIPMT function.
Verify the result summing up the interest values for 2nd and 3rd months.
Compute the cumulative principal paid between 2nd and 3rd months using the CUMPRINC function.
Verify the result summing up the principal values for 2nd and 3rd months.

你将得到以下结果。

You will get the following results.

你可以看到，你的计算结果与验证结果一致。

You can see that your calculations match with your verification results.

Calculating Interest Rate

假设你贷款了100,000卢比，并且希望以不超过12000卢比的月供，在15个月内还完贷款。你可能会想知道需要支付多少的利率。

Suppose you take a loan of 100,000 and you want to pay back in 15 months with a maximum monthly payment of 12000. You might want to know the interest rate at which you have to pay.

使用Excel RATE函数，找出利率−

Find the interest rate with the Excel RATE function −

你将会得到8%的结果。

You will get the result as 8%.

Calculating Term of Loan

假设你以10%的利率贷款了100,000卢比。你想设置不超过15,000卢比的月供。你可能会想知道多久才能还清贷款。

Suppose you take a loan of 100,000 at the interest rate 10%. You want a maximum monthly payment of 15,000. You might want to know how long it will take for you to clear the loan.

用NPER的Excel函数找出付款笔数

Find the number of payments with Excel NPER function

你将会得到12个月的结果。

You will get the result as 12 months.

Decisions on Investments

当你想要进行一项投资时，你需要比较不同的选项，并选择能产生较高收益的选项。净现值对于比较某个时间段的现金流以及判断哪种方式较好的很有帮助。这些现金流可以以规律、定期的时间间隔产生，或不规律的时间间隔产生。

When you want to make an investment, you compare the different options and choose the one that yields better returns. Net present value is useful in comparing cash flows over a period of time and deciding which one is better. The cash flows can occur at regular, periodical intervals or at irregular intervals.

首先，我们来看 regular, periodical cash flows 的情况。

First, we consider the case of regular, periodical cash flows.

在n年中的不同时间点收到的现金流的净现值（n可以是分数）为 1/(1 + r)n ，其中的r是年利率。

The net present value of a sequence of cash flows received at different points in time in n years from now (n can be a fraction) is 1/(1 + r)n, where r is the annual interest rate.

考虑以下在3年时间段范围内的两种投资。

Consider the following two investments over a period of 3 years.

从表面上看，投资 1 看起来比投资 2 好。然而，只有当你了解截至今天的投资的真实价值时，你才能决定哪项投资更好。你可以使用 NPV 函数来计算回报。

At face value, Investment 1 looks better than Investment 2. However, you can decide on which investment is better only when you know the true worth of the investment as of today. You can use the NPV function to calculate the returns.

现金流可能发生

The cash flows can occur

At the end of every year.
At the beginning of every year.
In the middle of every year.

NPV 函数假设现金流在年底发生。如果现金流在不同的时间发生，则你必须在 NPV 计算中考虑到特定因子。

NPV function assumes that the cash flows are at the end of the year. If the cash flows occur at different times then you have to take into account that particular factor along with the calculation with NPV.

假设现金流在年底发生。然后你可以直接使用 NPV 函数。

Suppose the cash flows occur at the end of the year. Then you can straight away use the NPV function.

您将获得以下结果−

You will get the following results −

正如你观察到的，投资 2 的 NPV 高于投资 1 的 NPV。因此，投资 2 是一个更好的选择。你之所以得到这个结果，是因为与投资 1 相比，投资 2 的现金流出在以后的期间。

As you observe NPV for Investment 2 is higher than that for Investment 1. Hence, Investment 2 is a better choice. You got this result as cash out flows for Investment 2 are at later periods as compared to that of Investment 1.

Cash Flows at the Beginning of the Year

假设现金流在每年的年初发生。在这种情况下，你不应在 NPV 计算中包含第一个现金流，因为它已经表示当前价值。你需要将第一个现金流添加到从其余现金流中获得的 NPV 中以获得净现值。

Suppose the cash flows occur at the beginning of every year. In such a case, you should not include the first cash flow in NPV calculation as it already represents the current value. You need to add the first cash flow to the NPV obtained from rest of the cash flows to get the net present value.

您将获得以下结果−

You will get the following results −

Cash Flows in the Middle of the Year

假设现金流在每年的年中发生。在这种情况下，你需要将从现金流中获得的 NPV 乘以 $\sqrt{1+r}$ 以获得净现值。

Suppose the cash flows occur in the middle of every year. In such a case, you need to multiply the NPV obtained from the cash flows by $\sqrt{1+r}$ to get the net present value.

您将获得以下结果−

You will get the following results −

Cash Flows at Irregular Intervals

如果你想计算具有不规则现金流的净现值，即现金流在任意时间发生，则计算会有点复杂。

If you want to calculate the net present value with irregular cash flows, i.e. cash flows occurring at random times, the calculation is a bit complex.

但是，在 Excel 中，你可以使用 XNPV 函数轻松进行此类计算。

However, in Excel, you can easily do such a calculation with XNPV function.

Arrange your data with the dates and the cash flows.

Note − 数据中的第一个日期应该是所有日期中最早的日期。其他日期可以按任何顺序出现。

Note − The first date in your data should be the earliest of all the dates. The other dates can occur in any order.

Use the XNPV function to calculate the net present value.

您将获得以下结果−

You will get the following results −

cash flows at irregular intervals result

假设今天的日期是 2015 年 3 月 15 日。正如你所观察到的，所有现金流日期都是以后的日期。如果你想找出今天的净现值，请在数据顶部包含它，并为现金流指定 0。

Suppose today’s date is 15th March, 2015. As you observe, all the dates of cash flows are of later dates. If you want to find the net present value as of today, include it in the data at the top and specify 0 for the cash flow.

您将获得以下结果−

You will get the following results −

Internal Rate of Return (IRR)

一项投资的内部收益率 (IRR) 是 NPV 为 0 时的利率。它是正现金流的现值正好补偿负现金流的利率值。当贴现率为 IRR 时，该投资完全无所谓，即投资者既不赚钱也不亏钱。

Internal Rate of Return (IRR) of an investment is the rate of interest at which NPV is 0. It is the rate value for which the present values of the positive cash flows exactly compensate the negative ones. When the discount rate is the IRR, the investment is perfectly indifferent, i.e. the investor is neither gaining nor losing money.

考虑以下现金流、不同的利率和对应的 NPV 值。

Consider the following cash flows, different interest rates and the corresponding NPV values.

正如你在利率 10% 和 11% 的值之间观察到的，NPV 的符号发生变化。当你将利率微调到 10.53% 时，NPV 几乎为 0。因此，IRR 为 10.53%。

As you can observe between the values of interest rate 10% and 11%, the sign of NPV changes. When you fine-tune the interest rate to 10.53%, NPV is nearly 0. Hence, IRR is 10.53%.

Determining IRR of Cash Flows for a Project

可以使用Excel函数IRR计算现金流的IRR。

You can calculate IRR of cash flows with Excel function IRR.

如你之前看到的那样IRR是10.53%

The IRR is 10.53% as you had seen in the previous section.

对于给定的现金流，IRR可能会-

For the given cash flows, IRR may −

exist and unique
exist and multiple
not exist

Unique IRR

如果IRR存在并且唯一，则可用于在若干种可能性中选择最佳投资。

If IRR exists and is unique, it can be used to choose the best investment among several possibilities.

If the first cash flow is negative, it means the investor has the money and wants to invest. Then, the higher the IRR the better, since it represents the interest rate the investor is receiving.
If the first cash flow is positive, it means the investor needs money and is looking for a loan, the lower the IRR the better since it represents the interest rate the investor is paying.

要判断IRR是否是唯一的，就改变猜测值并计算IRR。如果IRR保持不变，那么它是唯一的。

To find if an IRR is unique or not, vary the guess value and calculate IRR. If IRR remains constant then it is unique.

正如你所观察的那样，IRR对不同的猜测值具有唯一的一个值。

As you observe, the IRR has a unique value for the different guess values.

Multiple IRRs

在某些情况下，你可能有许多IRR。考虑以下现金流。使用不同的猜测值计算IRR。

In certain cases, you may have multiple IRRs. Consider the following cash flows. Calculate IRR with different guess values.

您将获得以下结果−

You will get the following results −

你可以看到有两种IRR—— -9.59%和216.09%。你可以通过计算净现值来验证这两个IRR。

You can observe that there are two IRRs - -9.59% and 216.09%. You can verify these two IRRs calculating NPV.

对于 -9.59%和216.09%，净现值都是0。

For both -9.59% and 216.09%, NPV is 0.

No IRRs

在某些情况下，你可能没有IRR。考虑以下现金流。使用不同的猜测值计算IRR。

In certain cases, you may not have IRR. Consider the following cash flows. Calculate IRR with different guess values.

对于所有猜测值，你都将得到结果#NUM。

You will get the result as #NUM for all the guess values.

结果＃NUM表示所考虑的现金流没有IRR。

The result #NUM means that there is no IRR for the cash flows considered.

Cash Flows Patterns and IRR

如果现金流中只有一个符号变化，例如从负到正或从正到负，那么就保证有一个唯一的IRR。例如，在资本投资中，第一个现金流将会是负值，而其余现金流将是正值。在这种情况下，会出现唯一的IRR。

If there is only one sign change in the cash flows, such as from negative to positive or positive to negative, then a unique IRR is guaranteed. For example, in capital investments, the first cash flow will be negative, while the rest of the cash flows will be positive. In such cases, unique IRR exists.

如果现金流中有多个符号变化，IRR可能就不存在。即使存在，也可能不唯一。

If there is more than one sign change in the cash flows, IRR may not exist. Even if it exists, it may not be unique.

Decisions based on IRRs

许多分析师更喜欢使用IRR，并且它是一个流行的盈利能力衡量标准，因为作为百分比，它易于理解并且易于与所需回报进行比较。然而，使用IRR做决策时存在一些问题。如果你按IRR排序，并根据这些排名做出决策，你最终可能会做出错误的决策。

Many analysts prefer to use IRR and it is a popular profitability measure because, as a percentage, it is easy to understand and easy to compare to the required return. However, there are certain problems while making decisions with IRR. If you rank with IRRs and make decisions based on these ranks, you may end up with wrong decisions.

你已经看到净现值将使你能够做出财务决策。然而，当项目相互排斥时，IRR和净现值并不总是会导致同样的决策。

You have already seen that NPV will enable you to make financial decisions. However, IRR and NPV will not always lead to the same decision when projects are mutually exclusive.

Mutually exclusive projects 是那些选择一个项目排除接受另一个项目。当被比较的项目相互排斥时，净现值和IRR之间可能出现排名冲突。如果你必须在项目A和项目B之间进行选择，净现值会建议接受项目A，而IRR可能建议项目B。

Mutually exclusive projects are those for which the selection of one project precludes the acceptance of another. When projects that are being compared are mutually exclusive, a ranking conflict may arise between NPV and IRR. If you have to choose between project A and project B, NPV may suggest acceptance of project A whereas IRR may suggest project B.

由于以下原因之一可能会出现 NPV 和 IRR 之间此类型的冲突 -

This type of conflict between NPV and IRR may arise because of one of the following reasons −

The projects are of greatly different sizes, or
The timing of the cash flows are different.

Projects of significant size difference

如果您想通过 IRR 做出决策，则项目 A 的收益率为 100，项目 B 的收益率为 50。因此，投资项目 A 看起来很划算。但是，由于项目规模不同，这是一个错误的决定。

If you want to make a decision by IRR, project A yields a return of 100 and Project B a return of 50. Hence, investment on project A looks profitable. However, this is a wrong decision because of the difference in the scale of projects.

考虑 -

Consider −

You have 1000 to invest.
If you invest entire 1000 on project A, you get a return of 100.
If you invest 100 on project B, you will still have 900 in your hand that you can invest on another project, say project C. Suppose you get a return of 20% on project C, then the total return on project B and project C is 230, which is way ahead in profitability.

因此，在这些情况下，NPV 是做出决策的更好方法。

Thus, NPV is a better way for decision making in such cases.

Projects with different cash flows timings

同样，如果您考虑通过 IRR 来决策，项目 B 将会是选择。但是，项目 A 具有更高的 NPV，并且是理想的选择。

Again, if you consider IRR to decide, project B would be the choice. However, project A has a higher NPV and is an ideal choice.

IRR of Irregularly Spaced Cash Flows (XIRR)

有时您的现金流可能是间隔无规律。在这种情况中，您不能使用 IRR，因为 IRR 需要间距相等的间隔。您可以使用 XIRR，它会考虑现金流的日期以及现金流。

Your cash flows may sometimes be irregularly spaced. In such a case, you cannot use IRR as IRR requires equally spaced time intervals. You can use XIRR instead, which takes into account the dates of the cash flows along with the cash flows.

得到的内部收益率为 26.42%。

The Internal Rate of Return that results in is 26.42%.

Modified IRR (MIRR)

考虑当您的融资利率与再投资率不同的时候。如果您通过 IRR 计算内部收益率，它假设融资和再投资的利率相同。此外，您也可能获得多个 IRR。

Consider a case when your finance rate is different from your reinvestment rate. If you calculate Internal Rate of Return with IRR, it assumes same rate for both finance and reinvestment. Further, you might also get multiple IRRs.

例如，考虑下面显示的现金流 -

For example, consider the cash flows given below −

正如您所看到的，NPV不止一次为 0，导致多个 IRR。此外，再投资率未被考虑在内。在这些情况下，您可以使用修正后的 IRR (MIRR)。

As you observe, NPV is 0 more than once, resulting in multiple IRRs. Further, reinvestment rate is not taken into account. In such cases, you can use modified IRR (MIRR).

您将获得以下显示的 7% 的结果 -

You will get a result of 7% as shown below −

Note - 与 IRR 不同，MIRR 始终是唯一的。

Note − Unlike IRR, MIRR will always be unique.