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

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

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

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

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

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

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

　　第一步，在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，點擊確定。

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

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

　　內容來源高頓金融分析師。