Excel数据财务分析

您可以轻松地使用Excel执行财务分析. Excel为您提供了多种财务功能,如PMT,PV,NPV,XNPV,IRR,MIRR,XIRR等,使您能够快速得出财务分析结果.

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

什么是年金?

年金是一系列常数持续现金支付.例如,退休,保险金,房屋贷款,抵押等的储蓄.年金函数 : 去;

  • 正数表示现金收到.

  • 负数表示已付现金.

一系列未来付款的现值

现值是一系列未来付款现在的总价值.您可以使用Excel函数 : 去来计算当前值;

  • PV : 使用利率和一系列未来支付(负值)和收入(正值)计算投资的现值.至少有一个现金流必须是正数,至少有一个必须是负数.

  • NPV : 使用贴现率和一系列定期未来付款(负值)和收入(正值)计算投资的净现值.

  • XNPV : 计算不一定是周期性的现金流量表的净现值.

注意 : 去;

  • 光伏现金流量必须保持不变,而净现金流量可以变动.

  • 光伏现金流可以在期初或期末,而NPV现金流必须在期末.

  • 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对您有利.

什么是EMI?

每日分期付款(EMI)由Investopedia定义为"借款人在每个日历月的指定日期向贷款人支付的固定金额.每月等额分期付款用于每月还清利息和本金,因此在指定的年限内,贷款将全额还清."

贷款上的EMI

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

假设您希望每年获得5000000的住房贷款利率为11.5%,贷款期限为25年.您可以通过以下方式找到您的EMI;

  • 计算利率每月(每年利率/12)

  • 计算每月付款次数(年数和日期; 12)

  • 使用PMT功能计算EMI

使用PMT函数

如您所见,

  • 现值(PV)是贷款金额.

  • Future在贷款金额应为0的期限结束时,价值(FV)为0.

  • Ty pe是1,因为EMI是在每个月初支付的.

您将得到以下结果 :

现在和将来的价值

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

EMI包括利息和部分本金支付.随着时间的增加,EMI的这两个组成部分会发生变化,从而减少余额.

获得

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

  • 支付每月付款的主要部分,您可以使用Excel PPMT功能.

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

按照以下程序执行.

第1步  : 去;按如下方式计算EMI.

计算EMI

这导致Rs的EMI. 13261.59.

EMI结果

第2步 : 接下来计算8个月EMI的利息和主要部分,如下所示.

计算利息和本金

您将获得以下结果.

计算利息和主要结果

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

您可以计算两个期间(包括两个期间)的利息和本金.

  • 使用CUMIPMT函数计算2 nd 和3 rd 月之间的累积利息.

  • 验证结果总结2 nd 和3 rd 个月的利息值.

  • 使用CUMPRINC函数计算在2 nd 和3 rd 月之间支付的累积本金.

  • 验证结果总结了2 nd 和3 rd 月的主要值.

总结

您将获得以下结果.

总结结果

您可以看到您的计算与您的验证结果一致.

计算利率

假设您贷款100,000,并希望在15个月内偿还,最高每月支付12000.您可能想知道你需要支付的利率.

使用Excel RATE函数查找利率 :

Summing Up

您将获得8%的结果.

Summing Up Result

计算贷款期限

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

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

Excel Nper Function

您将得到12个月的结果.

Excel Nper功能结果

投资决策

当您想进行投资时,您比较不同的选项,并选择产生更好回报的选项.净现值在比较一段时间内的现金流量和决定哪一个更好时是有用的.现金流可以定期,定期或不定期发生.

首先,我们考虑定期,定期现金流的情况.

从现在起n年内不同时间点收到的一系列现金流的净现值(n可以是分数)是 1/(1 + r) n ,其中r是年利率.

在3年期间考虑以下两项投资.

投资决策

面值,投资1看起来比投资2好.但是,您可以决定哪个只有当你知道今天投资的真正价值时,投资才会更好.您可以使用NPV函数计算收益.

可以发生现金流

  • 每年年底.

  • 每年年初.

  • 每年年中.

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

假设现金流量发生在年末.然后你可以立即使用NPV函数.

NPV Function

您将得到以下结果 :

NPV功能结果

As您观察到投资2的NPV高于投资1的NPV.因此,投资2是更好的选择.您得到这个结果,因为投资2的现金流量在后期与投资1相比.

年初现金流量

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

开始年度的现金流量

您将获得以下结果 :

年初现金流量结果

年中现金流量

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

中年现金流量

您将获得以下结果 :

中年结果中的现金流量

不规则间隔的现金流量

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

然而,在Excel中,您可以轻松地使用XNPV函数进行此类计算.

  • 使用日期和现金流安排您的数据.

注意 : 您数据中的第一个日期应该是所有日期中最早的日期.其他日期可以按任何顺序发生.

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

不规则间隔的现金流量

您将获得以下结果 :

不规则间隔的现金流量结果

假设今天的日期是15 th 2015年3月.如您所见,现金流的所有日期都是以后的日期.如果您想查找截至今天的净现值,请将其包含在顶部的数据中,并为现金流指定0.

包含日期

您将获得以下结果 :

包括日期结果

内部收益率(IRR)

投资的内部收益率(IRR)是NPV为0的利息.正现金流的现值恰好补偿负值的利率值.当贴现率是内部收益率时,投资完全无动于衷,即投资者既不赚钱也不赔钱.

考虑以下现金流量,不同利率和相应的NPV值.

内部收益率

正如您可以观察到的值之间利率10%和11%,NPV变化的迹象.当您将利率微调至10.53%时,NPV接近0.因此,IRR为10.53%.

确定项目现金流的IRR

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

计算IRR

您在上一节中看到的IRR为10.53%.

对于给定的现金流量,IRR可以 : 去;

  • 存在并且唯一

  • 存在且多个

  • 不存在

独特的IRR

如果IRR存在并且是唯一的,它可用于在多种可能性中选择最佳投资.

  • 如果第一笔现金流为负数,则意味着投资者有资金并想投资.那么,内部收益率越高越好,因为它代表了投资者所获得的利率.

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

To找出IRR是否唯一,改变猜测值并计算IRR.如果IRR保持不变,则它是唯一的.

唯一IRR

As你观察到,IRR对于不同的猜测值有一个独特的值.

唯一值

多个IRR

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

多个IRR

你会得到以下结果 :

多个IRR结果

你可以观察到有两个内部收益率 -  -9.59%和216.09%.您可以验证这两个计算NPV的IRR.

Calculating NPV

-9.59%和216.09%,NPV为0.

无IRR

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

没有IRR

你会得到所有猜测值的结果为#NUM.

没有IRR结果

结果#NUM意味着所考虑的现金流没有IRR.

现金流模式和IRR

如果只有一个签署现金流量的变化,例如从负面到正面或从正面到负面,然后保证独特的内部收益率.例如,在资本投资中,第一笔现金流量为负数,而其余现金流量为正数.在这种情况下,存在唯一的IRR.

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

基于IRR的决策

许多分析师更喜欢使用IRR,这是一个受欢迎的盈利指标因为,作为一个百分比,它很容易理解,并且易于与所需的回报进行比较.但是,在使用IRR做出决策时存在某些问题.如果您根据IRR排名并根据这些等级做出决策,您最终可能会做出错误的决定.

您已经看到NPV将使您能够做出财务决策.但是,当项目互相排斥时,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.假设您获得20的回报在项目C上的百分比,则项目B和项目C的总回报为230,这在盈利能力方面遥遥领先.

因此,在这种情况下,NPV是一种更好的决策方式.

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

不同的现金流量

同样,如果您考虑IRR来决定,项目B将是您的选择.但是,项目A具有更高的净现值,是一个理想的选择.

不规则间隔现金流量的内部收益率(XIRR)

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

 XIRR

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

内部汇率

修改后的内部收益率(MIRR)

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

例如,考虑下面给出的现金流量 :

MIRR

如您所见,NPV不止一次为0,导致多个IRR.此外,不考虑再投资率.在这种情况下,您可以使用修改后的IRR(MIRR).

Modified IRR

您将获得7%的结果,如下所示 :

修改的IRR结果

注意 : 与IRR不同,MIRR将始终是独一无二的.