Excel数据分析-财务分析

您可以通过简单的方式使用 Excel 执行财务分析。Excel为您提供了多种财务功能，例如PMT，PV，NPV，XNPV，IRR，MIRR，XIRR等，使您能够快速得出财务分析结果。

在本章中，您将了解在何处以及如何使用这些函数进行分析。

什么是年金？

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

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

一系列未来付款的现值

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

• PV − 通过使用利率和一系列未来付款（负值）和收入（正值）来计算投资的现值。至少一个现金流必须为正数，至少一个必须为负数。

• NPV − 通过使用贴现率和一系列定期未来付款（负值）和收入（正值）来计算投资的净现值。

• XNPV − 计算不一定是定期的现金流量表的净现值。

请注意 -

• PV现金流必须是恒定的，而NPV现金流可以是可变的。

• PV现金流可以在期初或期末，而NPV现金流必须在期末。

• 净现值现金流必须是定期的，而XNPV现金流不一定是定期的。

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

例

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

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

您可以使用 Excel 函数 PV −

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

若要计算每年年底付款的现值，请省略类型或指定 0 作为类型。

若要计算每年年底付款的现值，请指定 1 作为类型。

您将获得以下结果 -

因此

• 如果您现在付款，则需要支付现值的 32,000。
• 如果您选择在年底付款的年度付款，则需要支付现值的28， 793。
• 如果您选择在年底付款的年度付款，则需要支付现值的 32,536。

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

什么是电磁干扰？

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

贷款的电磁干扰

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

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

• 计算每月利率（年利率/12）
• 计算每月付款次数（年数 * 12）
• 使用光电倍增器功能计算电磁干扰

正如你所观察到的，

• 现值（PV）是贷款金额。
• 未来价值（FV）为0，因为在期限结束时，贷款金额应为0。
• 类型为 1，因为 EMI 在每个月初支付。

您将获得以下结果 -

每月支付贷款本金和利息

EMI包括利息和部分本金支付。随着时间的增加，EMI的这两个分量会发生变化，从而降低平衡。

要得到

• 您每月付款的利息部分，您可以使用Excel IPMT功能。

• 支付您每月付款的本金部分，您可以使用Excel PPMT功能。

例如，如果您以每年 1% 的利率贷款 000,000,8，为期 16 个月。您可以获得 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利率功能查找利率 -

您将获得 8% 的结果。

计算贷款期限

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

使用Excel NPER功能查找付款数量

您将获得 12 个月的结果。

投资决策

当您想进行投资时，您可以比较不同的选项并选择产生更好回报的选项。净现值有助于比较一段时间内的现金流并决定哪一个更好。现金流可以定期、定期或不定期发生。

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

从现在起n年内不同时间点收到的一系列现金流的净现值（n可以是一小部分）为1/（1 + r）ⁿ，其中r是年利率。

考虑在 3 年内进行以下两项投资。

从表面上看，投资 1 看起来比投资 2 更好。但是，只有当您知道截至今天的投资的真正价值时，您才能决定哪种投资更好。您可以使用 NPV 函数来计算回报。

可能发生现金流

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

NPV函数假设现金流在年底。如果现金流发生在不同的时间，那么您必须考虑该特定因素以及NPV的计算。

假设现金流发生在年底。然后，您可以立即使用NPV功能。

您将获得以下结果 -

当您观察到投资 2 的净现值高于投资 1 的净现值时。因此，投资2是更好的选择。您得到这个结果是因为与投资 2 相比，投资 1 的现金流出流量在后期。

年初的现金流

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

您将获得以下结果 -

年中现金流

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

您将获得以下结果 -

不定期的现金流

如果要计算不规则现金流的净现值，即随机发生的现金流，则计算有点复杂。

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

• 使用日期和现金流排列数据。

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

• 使用 XNPV 函数计算净现值。

您将获得以下结果 -

假设今天的日期是 15 年 2015 月 0^日。如您所观察的，所有现金流的日期都是较晚的日期。如果要查找截至今天的净现值，请将其包含在顶部的数据中，并为现金流指定 <>。

您将获得以下结果 -

内部收益率

投资的内部收益率（IRR）是净现值为0的利率。它是正现金流的现值完全补偿负现金流的利率值。当贴现率是内部收益率时，投资是完全无动于衷的，即投资者既没有收益也没有损失。

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

正如您在利率值 10% 和 11% 之间观察到的那样，NPV 的标志会发生变化。当您将利率微调至 10.53% 时，净现值接近 0。因此，内部收益率为10.53%。

确定项目现金流的内部收益率

您可以使用Excel函数IRR计算现金流的内部收益率。

如您在上一节中看到的那样，内部收益率为 10.53%。

对于给定的现金流，内部收益率可能 -

• 存在且独特
• 存在和多个
• 不存在

独特的内部收益率

如果IRR存在并且是唯一的，则可以使用它在几种可能性中选择最佳投资。

• 如果第一个现金流为负，则意味着投资者有钱并想投资。然后，内部收益率越高越好，因为它代表了投资者收到的利率。

• 如果第一个现金流为正，则意味着投资者需要资金并正在寻找贷款，则内部收益率越低越好，因为它代表了投资者支付的利率。

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

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

多个内部收益率

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

您将获得以下结果 -

您可以观察到有两个内部收益率 - -9.59% 和 216.09%。您可以验证这两个计算净现值的 IRR。

对于 -9.59% 和 216.09%，NPV 均为 0。

无内部收益率

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

您将获得所有猜测值 #NUM 的结果。

结果 #NUM 意味着所考虑的现金流没有内部收益率。

现金流模式和内部收益率

如果现金流只有一个迹象变化，例如从负到正或从正到负，那么保证唯一的内部收益率。例如，在资本投资中，第一个现金流将为负，而其余现金流将为正。在这种情况下，存在唯一的内部收益率。

如果现金流中有多个迹象变化，则内部收益率可能不存在。即使它存在，也可能不是唯一的。

基于内部收益率的决策

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

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

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

NPV和IRR之间的这种类型的冲突可能是由于以下原因之一而引起的：

• 项目规模大不相同，或
• 现金流的时间不同。

规模差异较大的项目

如果要通过 IRR 做出决定，项目 A 的回报为 100，项目 B 的回报为 50。因此，对项目A的投资看起来是有利可图的。但是，由于项目规模的差异，这是一个错误的决定。

考虑 −

• 你有1000个可以投资。

• 如果您在项目 A 上投入全部 1000，您将获得 100 的回报。

• 如果你在项目B上投资100，你手里仍然有900，你可以投资另一个项目，比如项目C.假设你在项目C上得到20%的回报，那么项目B和项目C的总回报是230，这在盈利能力上遥遥领先。

因此，在这种情况下，NPV是更好的决策方式。

具有不同现金流时间的项目

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

不规则间隔现金流的内部收益率 （XIRR）

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

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

修正内部收益率 （MIRR）

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

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

正如您所观察到的，NPV 多次为 0，导致多个 IRR。此外，再投资率没有考虑在内。在这种情况下，您可以使用修改后的 IRR （MIRR）。

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

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