贷款利率的变化如何影响贷款金额来讲解：如何用Excel做敏感性分析？-会计网

贷款利率的变化如何影响贷款金额来讲解：如何用Excel做敏感性分析？

发布时间：2022-09-28 责编：高顿金融分析师

打印

当当当当~CFA手把手实务操作指南又来喽！

很多小伙伴在投行、券商、PE、VC等各大金融机构工作中，但凡涉及到估值建模的工作，必不可少的一个分析流程就是——敏感性分析（Sensitivity Analysis）。

我记得曾经给一个朋友帮忙测算一个投资项目的净利润和销量的关系，仅用1分钟就给投资人呈现出各种销量水平下，项目的盈利水平，深得投资人的赏识！当时就感觉自带光环，高人一筹啊~

其实敏感性分析的应用非常广泛，小到贷款买房，大到估值投资，都少不了敏感性分析这一招鲜的神助攻！掌握了这个一招鲜的本领，保你上得投行，下得厨房！

闲话不多唠，直接上干货！

我们先用最贴近生活的案例——贷款利率的变化如何影响贷款金额来讲解如何用Excel做敏感性分析。

自2019年起，我国就开启了降息通道，一年期LPR从2019年的4.25%降到2022年8月22日公布的最新利率3.65%；五年期LPR从4.85%降到了4.3%。此次降息之后，房贷的最优利率（首次购买普通住房）可以低至4.10%。无论是房贷基准利率（4.30%），还是房贷最优利率（4.10%），都创下了历史新低！

这不禁让我们搓搓小手，算一算一路降息，到底能省多少贷款！

⭕第一步，在Excel中搭建已知信息和基本计算逻辑。

假设房价为人民币500万，首付比例为20%，贷款期限为20年，贷款利率为5%，采用等额本息法，以月为单位还款。先计算一下在这个前提下，每月的还款金额、本息总偿还金额以及利息偿还金额。

众所周知，投行默认使用无网格线形式的Excel，这里附赠大家一个去掉Excel中网格线的小技巧。我们可以使用快捷键“Alt + w + v + g”快速去掉Excel中的网格线。已知信息和基本计算逻辑的搭建如下图：

由于首付20%，实际贷款金额为500万的80%，即400万。5%是年化利率，而贷款是按月偿还的，故需要利用月利率（即年利率除以12）计算月供金额。使用Excel中年金计算公式PMT（利率，期限，终值）计算得到月供为人民币26,398.23。那么，240个月的偿还期内，总共需要偿还人民币6,335,575,10（= 26,398.23×240），其中利息部分是人民币2,335,575,10（= 总额6,335,575,10 – 本金4,000,000）。

为了更清晰地解释结果区域的计算过程，C列展示了B列单元格的计算公式，方便大家自己动手练习。

另外，在投行的建模工作中，默认所有的已知信息用蓝色字体表示，所有计算结果用黑色字体表示。所以，输入变量区域使用蓝色字体，色号为“0,0,255”。

⭕第二步，搭建敏感性分析的框架。

以5个基点为步长，从5%的房贷基准利率开始逐渐递减至4%（利率变动区间受截图区域的限制所致，感兴趣的小伙伴可以适当放宽利率变动的区间）。通过敏感性分析，计算利率的变化如何影响月供金额、本息偿还总额，以及利息偿还总额。

需要注意的是，B列的利率是自变量（X），用绝对数字表示；而C15，D15和E15是因变量（Y），需要通过引用单元格的方式（见14行的公式），让Excel知道这三个因变量计算的逻辑。这样，Excel就可以推算在不同利率水平下，月供、本息偿还总额，以及利息偿还总额的金额了。

选中B15至E36整个区域，利用快捷键“Alt + a + w + t”调出模拟运算功能。

在“输入引用列的单元格”引用输入变量区域中的贷款利率单元格B6，点击确定。

瞬间，C16至E36整个区域的数据计算完成！我们可以通过第一行5%利率水平计算出来的结果和第一步中用公式计算出来的结果进行对比，检验敏感性分析的结果是否正确。下图第一个红框中，第16行是敏感性分析计算出来的当利率为5%的时候，月供、本息偿还总额，以及利息偿还总额的金额。这三个金额和第15行用公式计算出来的结果一致，说明敏感性分析的结果正确。

通过观察发现，随着利率的下调，月供、本息偿还总额，以及利息偿还总额的金额都有明显下降。

🔝下面，我们再进阶一下，来个敏感性分析2.0难度！

敏感性分析可以同时分析两个自变量的变化对一个因变量的影响。比如，我想知道利率和首付比例的变化，对本息偿还总额的影响。类似地，搭建敏感性分析框架：

这种情况下，列（利率）和行（首付比例）都是自变量，均可用绝对数字表示。特别需要注意的是，在敏感性分析区域的左上角B40单元格中，通过引用因变量B11，告诉Excel计算因变量的逻辑，而不是直接输入6,335,575,10这个绝对数字。这样，Excel才能够通过利率、首付比例和本息偿还总额之间的数理关系推算结果。

选中B40至F61区域，利用快捷键“Alt + a + w + t”调出模拟运算功能。在“输入引用列的单元格”引用输入变量区域中的贷款利率单元格B6，在“输入引用行的单元格”引用输入变量区域中的首付比例单元格B4，点击确定。