Excel 数据财务分析

您可以轻松地使用 Excel 进行财务分析。Excel 提供了 PMT、PV、NPV、XNPV、IRR、MIRR、XIRR 等多种财务函数，使您能够快速得出财务分析结果。

在本节中，您将学习在何处以及如何使用这些函数进行分析。

什么是年金？

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

正数表示收到的现金。
负数表示支付的现金。

一系列未来支付的现值

现值是一系列未来支付现在总共值多少钱。您可以使用 Excel 函数计算现值 -

PV - 使用利率和一系列未来支付（负值）和收入（正值）计算投资的现值。至少一个现金流必须为正，至少一个必须为负。
NPV - 使用贴现率和一系列定期未来支付（负值）和收入（正值）计算投资的净现值。
XNPV - 计算非周期性现金流计划的净现值。

请注意 -

PV 现金流必须是恒定的，而 NPV 现金流可以是可变的。
PV 现金流可以在期初或期末，而 NPV 现金流必须在期末。
NPV 现金流必须是周期性的，而 XNPV 现金流不必是周期性的。

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

示例

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

您想知道这些选项中哪一个对您有利。

您可以使用 Excel 函数 PV -

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

要计算在每年的年末付款的现值，请省略 type 或为 type 指定 0。

要计算在每年的年初付款的现值，请为 type 指定 1。

您将得到以下结果 -

因此，

如果您现在付款，您需要支付 32,000 的现值。
如果您选择每年在年末付款，您需要支付 28,793 的现值。
如果您选择每年在年初付款，您需要支付 32,536 的现值。

您可以清楚地看到选项 2 对您有利。

什么是 EMI？

等额本息还款 (EMI) 由 Investopedia 定义为“借款人按指定日期每月向贷方支付的固定付款金额。等额本息还款用于每月偿还利息和本金，以便在规定的数年内，贷款全部偿还。”

贷款的 EMI

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

假设您想申请 5000000 的住房贷款，年利率为 11.5%，贷款期限为 25 年。您可以按如下方式找到您的 EMI -

计算每月利率（年利率/12）
计算每月付款次数（年数 * 12）
使用 PMT 函数计算 EMI

如您所见，

现值 (PV) 是贷款金额。
未来值 (FV) 为 0，因为在期限结束时贷款金额应为 0。
Type 为 1，因为 EMI 在每月的月初支付。

您将得到以下结果 -

贷款的每月本金和利息支付

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

获取

每月付款的利息部分，您可以使用 Excel IPMT 函数。
每月付款的本金支付部分，您可以使用 Excel PPMT 函数。

例如，如果您以 16% 的年利率获得了 1,000,000 的贷款，期限为 8 个月。您可以获取 8 个月内 EMI、递减的利息金额、增加的本金支付金额和减少的贷款余额的值。在 8 个月结束时，贷款余额将为 0。

请按照以下步骤操作。

步骤 1 - 按如下方式计算 EMI。

这导致 EMI 为 13261.59 卢比。

步骤 2 - 接下来，计算 8 个月内 EMI 的利息和本金部分，如下所示。

您将得到以下结果。

两个期间之间支付的利息和本金

您可以计算两个期间（包括）之间支付的利息和本金。

使用 CUMIPMT 函数计算第 2 个月和第 3 个月之间累积支付的利息。
将第 2 个月和第 3 个月的利息值加起来验证结果。
使用 CUMPRINC 函数计算第 2 个月和第 3 个月之间累积支付的本金。
将第 2 个月和第 3 个月的本金值加起来验证结果。

您将得到以下结果。

您可以看到您的计算结果与您的验证结果匹配。

计算利率

假设您贷款 100,000，您想在 15 个月内还清，每月最多支付 12000。您可能想知道您需要支付的利率。

使用 Excel RATE 函数查找利率 -

您将得到 8% 的结果。

计算贷款期限

假设您以 10% 的利率贷款 100,000。您希望每月最多支付 15,000。您可能想知道您需要多长时间才能还清贷款。

使用 Excel NPER 函数查找付款次数

您将得到 12 个月的结果。

投资决策

当您想进行投资时，您会比较不同的选择并选择收益更高的选择。净现值可用于比较一段时间内的现金流并确定哪一个更好。现金流可以以定期、周期性的间隔或不规则的间隔发生。

首先，我们考虑定期、周期性现金流的情况。

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

考虑以下 3 年内的两项投资。

从表面上看，投资 1 比投资 2 好。但是，只有在您知道投资的真实价值（截至今天）时，才能决定哪项投资更好。您可以使用 NPV 函数计算收益。

现金流可以发生

每年年底。
每年年初。
每年年中。

NPV 函数假设现金流发生在年底。如果现金流发生在不同的时间，则必须将该特定因素与 NPV 的计算一起考虑在内。

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

您将得到以下结果 -

如您所见，投资 2 的 NPV 高于投资 1。因此，投资 2 是更好的选择。您得到此结果是因为投资 2 的现金流出发生在比投资 1 稍后的时间段。

每年年初的现金流

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

您将得到以下结果 -

年中现金流

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

您将得到以下结果 -

不规则间隔的现金流

如果您想计算不规则现金流的净现值，即在随机时间发生的现金流，则计算会稍微复杂一些。

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

使用日期和现金流整理您的数据。

注意 - 数据中的第一个日期应为所有日期中最早的日期。其他日期可以按任意顺序出现。

使用XNPV函数计算净现值。

您将得到以下结果 -

Cash Flows at Irregular Intervals Result

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

您将得到以下结果 -

内部收益率（IRR）

投资的内部收益率（IRR）是指净现值为0时的利率。它是使正现金流的现值正好抵消负现金流的利率。当贴现率为IRR时，投资完全无差异，即投资者既不赚钱也不亏钱。

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

正如您在利率10%和11%的值之间观察到的那样，净现值的符号发生了变化。当您将利率微调至10.53%时，净现值接近0。因此，IRR为10.53%。

确定项目的现金流的IRR

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

IRR为10.53%，正如您在上一节中看到的。

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

存在且唯一
存在且多个
不存在

唯一IRR

如果IRR存在且唯一，则可以用来在多种可能性中选择最佳投资。

如果第一个现金流为负，则表示投资者拥有资金并希望进行投资。然后，IRR越高越好，因为它代表了投资者获得的利率。
如果第一个现金流为正，则表示投资者需要资金并正在寻找贷款，IRR越低越好，因为它代表了投资者支付的利率。

要确定IRR是否唯一，请更改猜测值并计算IRR。如果IRR保持不变，则它是唯一的。

正如您所观察到的，对于不同的猜测值，IRR具有唯一的值。

多个IRR

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

您将得到以下结果 -

您可以观察到有两个IRR - -9.59%和216.09%。您可以通过计算净现值来验证这两个IRR。

对于-9.59%和216.09%，净现值均为0。

无IRR

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

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

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

现金流模式和IRR

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

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

基于IRR的决策

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

您已经了解到净现值将使您能够做出财务决策。但是，当项目互斥时，IRR和净现值并不总是会导致相同的决策。

互斥项目是指选择一个项目就排除了另一个项目的接受。当比较的项目是互斥的时，净现值和IRR之间可能会出现排名冲突。如果您必须在项目A和项目B之间进行选择，净现值可能建议接受项目A，而IRR可能建议选择项目B。

净现值和IRR之间可能出现这种冲突的原因之一是 -

项目的规模差异很大，或者
现金流的时间安排不同。

规模差异显著的项目

如果您想通过IRR做出决策，项目A的回报率为100，项目B的回报率为50。因此，对项目A的投资看起来很可观。但是，这是由于项目规模差异而导致的错误决策。

考虑 -

您有1000元可供投资。
如果您将全部1000元投资于项目A，您将获得100元的回报。
如果您将100元投资于项目B，您仍然有900元在手，可以投资于另一个项目，比如项目C。假设您在项目C上获得20%的回报，那么项目B和项目C的总回报为230，在盈利能力方面遥遥领先。

因此，在这种情况下，净现值是更好的决策方法。

现金流时间安排不同的项目

同样，如果您考虑使用IRR来决定，项目B将是选择。但是，项目A的净现值更高，是理想的选择。

不规则间隔现金流的IRR（XIRR）

您的现金流有时可能是不规则间隔的。在这种情况下，您不能使用IRR，因为IRR需要等间距的时间间隔。您可以改用XIRR，它考虑了现金流的日期以及现金流本身。

产生的内部收益率为26.42%。

修正内部收益率（MIRR）

考虑一下您的融资利率与再投资利率不同的情况。如果您使用IRR计算内部收益率，它假设融资和再投资的利率相同。此外，您还可能获得多个IRR。

例如，考虑以下给出的现金流 -

正如您所观察到的，净现值不止一次为0，导致多个IRR。此外，没有考虑再投资率。在这种情况下，您可以使用修正内部收益率（MIRR）。

您将得到如下所示的7%的结果 -

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

打印页面