用 3 个 Excel 财务函数解决复杂财务计算

原文标题：《这 3 个 Excel 财务函数，又是被低估了的函数！》

本文作者：小花

本文编辑：竺兰

最近小花遇到一个有趣的问题，是来自一位老朋友的灵魂发问：

怎么在月交年金和民间互保金融中做出选择？

这两个理财产品的基本情况如下：

月交年金：

每月缴纳 1000 元，年化利率 3%，2 年期，到期一次性提取本息。

互保金融：

每月缴纳本金 1000 元，每月本金均按 10% 计息，2 年期。同一产品共有 24 人参与，每个月必须有 1 人领取其他人缴纳的全部本息，领取之后的次月，必须支付 100 元 / 月的利息。

如何比较这两种理财产品的优劣呢？

我们可以从终值法、收益率法和 IRR 法来考虑这个问题，顺便给大家分享一些财务函数用法。

1、终值法

终值分析法是在确定了基准年、折算利率、现金流程以后，计算方案（项目）的净效益终值。终值越高，方案的经济可行性越大。

等额年金到期后能够收到多少本息呢？这一问题，可以用终值函数 FV 来计算。

年金终值公式：

=-FV(B3/12,C3,A3)

可见，如果每个月缴纳 1000 元年金，利率按 3% 计算，2 年后，一共可以收到本息 24,703 元。

「互保金融」的终值（假设不滚动投资）与领取本息的先后有关系，越晚提取，收益越大。其终值计算如下：

=S2*U2+S2*T2*(V2-1)-S2*T2*(U2-V2)

从终值法来看，如果能够确保领取期在 16 期以后，「互保金融」的收益大于月交年金，当领取期早于 16 期，「互保金融」的收益小于月交年金。

终值法适用投资规模相当的投资方案。

2、收益率法

当我把这个初步的结论告诉我这位老朋友时，他难以置信地问到：

为什么「互保金融」的名义利率有 10%，却只有 1/3 的参与者收益高于月交年金，后者的收益率只有 3%？

这就涉及到收益率的计算。

收益率法就是将投资项目的年平均净收益率与该项投资的资金成本加以比较，判断投资是否可取，然后在可取投资方案中选择收益率大的投资方案的一种投资决策方法。

显而易见，月交年金的收益率与名义利率一致，为 3%。

那么，「互保金融」的各领取期收益率是多少呢？

我们可以用 RATE 函数来计算。

「互保金融」的收益率计算如下：

=RATE(U2-S2,0,W2,0)*12

从收益率法来看，虽然「互保金融」的名义利率高达 10%，但是实际上，只有当参与人在 16 期以后领取本息，收益率才能达到 3%，在此之前领取的，收益率均低于月交年金的收益率 3%。可见，「互保金融」收益率还是有水分的。

但就此评价「互保金融」收益不如月交年金，有失公允，原因在于，月交年金要到期后才能收回本息，而「互保金融」则可能提前实现资金流入，如果这部分资金再次被用于投资，「互保金融」则将大有改善。

财务管理上，常用时间价值来解释这种差异，可使用内部报酬率 IRR 和净现值 NPV 来衡量某项投资的动态收益。

以下，我们选用 NPV 来对比这两个理财产品，IRR 比较法不适用本案例。

3、净现值法

计算净现值前，我们需要列出两个产品各期的现金流，再使用 NPV 函数来计算。

月交年金每月缴纳 1000 元，缴纳 24 期后，一次性收回本息 24,703 元，其现金流及 NPV 计算如下：

=NPV(B3/12,B2:Y2)

此处直接选用月交年金的利率作为折现率，其净现值应当为 0。在计算「互保金融」净现值时，我们继续选用这一折现率，如果后者净现值大于 0，则表示后者的投资收益更优，否则更差。

「互保金融」由于存在多种可能的提取期，所以计算该产品的 IRR 时，我们需要借助模拟运算表来实现。

「互保金融」NPV 计算：

❶ 以提取期为变量，列示基于变量计算的各期现金流。

本金的现金流，正数表示缴款，负数表示领取。

=IF(E$4=$G$2,-$D$2*23,$D$2)

利息的现金流，正数表示缴款，负数表示领取。

=IFS(E4<$G$2,0,E4=$G$2,-$D$2*$E$2*(E$4-1),E4>$G$2,$D$2*$E$2)

合并现金流：

❷ 计算单一提取期下的 IRR。

=NPV(H2/12,E7:AB7)

❸ 使用模拟运算表计算不同提取期对应的 NPV。

① 列示需要的领取期变量值，并链接首行对应的结果值。

② 选择链接行、变量值区域和结果值区域，按下图步骤完成模拟运算。

模拟运算结果如下：

从 NPV 角度来看，「互保金融」与月交年金的收益不相伯仲。

前者的所有参与人中，其比较收益优劣参半，这是因为领取期较早的参与人能够更快地获得现金流入用于滚动投资，在一定程度上对冲了负担高额利率地损失。

综上，在无法实现滚动投资或者追求确定性的情况下，应该选择月交年金进行投资，该产品的投资收益稳定、平均静态收益更高。

如果追求更高的动态收益或能够在提取本息后进行二次投资，应该选择「互保金融」进行理财，后者有机会实现更高的净现值和静态收益。

以上，就是小花由一个理财产品选择问题延伸而来，讲解的几个 Excel 财务公式和功能的用法，包括：

❶ FV 函数和 RATE 函数

❷ NPV 函数

❸ 模拟运算表

如果你是一位财会工作者，或者对财务测算有需求或兴趣，这些公式和实战案例将对你有所帮助。

小花还会持续分享其他 Excel 财务收益测算实战案例，用实战助你打通公式和工具使用的任督二脉，敬请期待！

广告声明：文内含有的对外跳转链接（包括不限于超链接、二维码、口令等形式），用于传递更多信息，节省甄选时间，结果仅供参考，IT之家所有文章均包含本声明。