貸款利率又降了，會如何影響貸款金額？

　　自2019年起，我國就開啟了降息通道，一年期LPR從2019年的4.25%降到2023年8月21日公布的最新利率3.45%；五年期LPR從4.85%降到了4.2%。這不禁讓我們搓搓小手，算一算一路降息，到底能省多少貸款！

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

　　我記得曾經(jīng)給一個朋友幫忙測算一個投資項目的凈利潤和銷量的關(guān)系，僅用1分鐘就給投資人呈現(xiàn)出各種銷量水平下，項目的盈利水平，深得投資人的賞識！當時就感覺自帶光環(huán)，高人一籌啊~

　　其實敏感性分析的應(yīng)用非常廣泛，小到貸款買房，大到估值投資，都少不了敏感性分析這一招鮮的神助攻！掌握了這個一招鮮的本領(lǐng)，保你上得投行，下得廚房！

　　閑話不多嘮，直接上干貨！

　　我們先用最貼近生活的案例——貸款利率的變化如何影響貸款金額來講解如何用Excel做敏感性分析。

　　第一步，在Excel中搭建已知信息和基本計算邏輯。

　　假設(shè)房價為人民幣500萬，首付比例為20%，貸款期限為20年，貸款利率為5%，采用等額本息法，以月為單位還款。先計算一下在這個前提下，每月的還款金額、本息總償還金額以及利息償還金額。

　　眾所周知，投行默認使用無網(wǎng)格線形式的Excel，這里附贈大家一個去掉Excel中網(wǎng)格線的小技巧。我們可以使用快捷鍵“Alt+w+v+g”快速去掉Excel中的網(wǎng)格線。已知信息和基本計算邏輯的搭建如下圖：

　　由于首付20%，實際貸款金額為500萬的80%，即400萬。5%是年化利率，而貸款是按月償還的，故需要利用月利率（即年利率除以12）計算月供金額。使用Excel中年金計算公式PMT（利率，期限，終值）計算得到月供為人民幣26,398.23。那么，240個月的償還期內(nèi)，總共需要償還人民幣6,335,575,10（=26,398.23×240），其中利息部分是人民幣2,335,575,10（=總額6,335,575,10–本金4,000,000）。

　　為了更清晰地解釋結(jié)果區(qū)域的計算過程，C列展示了B列單元格的計算公式，方便大家自己動手練習。

　　另外，在投行的建模工作中，默認所有的已知信息用藍色字體表示，所有計算結(jié)果用黑色字體表示。所以，輸入變量區(qū)域使用藍色字體，色號為“0,0,255”。

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

　　以5個基點為步長，從5%的房貸基準利率開始逐漸遞減至4%（利率變動區(qū)間受截圖區(qū)域的限制所致，感興趣的小伙伴可以適當放寬利率變動的區(qū)間）。通過敏感性分析，計算利率的變化如何影響月供金額、本息償還總額，以及利息償還總額。

　　需要注意的是，B列的利率是自變量（X），用絕對數(shù)字表示；而C15，D15和E15是因變量（Y），需要通過引用單元格的方式（見14行的公式），讓Excel知道這三個因變量計算的邏輯。這樣，Excel就可以推算在不同利率水平下，月供、本息償還總額，以及利息償還總額的金額了。

　　選中B15至E36整個區(qū)域，利用快捷鍵“Alt+a+w+t”調(diào)出模擬運算功能。

　　在“輸入引用列的單元格”引用輸入變量區(qū)域中的貸款利率單元格B6，點擊確定。

　　瞬間，C16至E36整個區(qū)域的數(shù)據(jù)計算完成！我們可以通過第一行5%利率水平計算出來的結(jié)果和第一步中用公式計算出來的結(jié)果進行對比，檢驗敏感性分析的結(jié)果是否正確。下圖第一個紅框中，第16行是敏感性分析計算出來的當利率為5%的時候，月供、本息償還總額，以及利息償還總額的金額。這三個金額和第15行用公式計算出來的結(jié)果一致，說明敏感性分析的結(jié)果正確。

　　通過觀察發(fā)現(xiàn)，隨著利率的下調(diào)，月供、本息償還總額，以及利息償還總額的金額都有明顯下降。

　　下面，我們再進階一下，來個敏感性分析2.0難度！

　　敏感性分析可以同時分析兩個自變量的變化對一個因變量的影響。比如，我想知道利率和首付比例的變化，對本息償還總額的影響。類似地，搭建敏感性分析框架：

　　這種情況下，列（利率）和行（首付比例）都是自變量，均可用絕對數(shù)字表示。特別需要注意的是，在敏感性分析區(qū)域的左上角B40單元格中，通過引用因變量B11，告訴Excel計算因變量的邏輯，而不是直接輸入6,335,575,10這個絕對數(shù)字。這樣，Excel才能夠通過利率、首付比例和本息償還總額之間的數(shù)理關(guān)系推算結(jié)果。

　　選中B40至F61區(qū)域，利用快捷鍵“Alt+a+w+t”調(diào)出模擬運算功能。在“輸入引用列的單元格”引用輸入變量區(qū)域中的貸款利率單元格B6，在“輸入引用行的單元格”引用輸入變量區(qū)域中的首付比例單元格B4，點擊確定。

　　瞬間，C41至F61整個區(qū)域的數(shù)據(jù)計算完成！同樣的，可以通過比較C41單元格的結(jié)果和B40單元格的結(jié)果檢驗敏感性分析的正誤。下圖中的數(shù)據(jù)展現(xiàn)了在不同利率水平和首付比例下，貸款的本息償還總額的變化趨勢和程度。

　　敏感性分析還可以測算不同單價、不同銷量水平對應(yīng)的息稅前利潤或者凈利潤，感興趣的小伙伴可以打開Excel試試吧！

　　內(nèi)容來源高頓金融分析師。