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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

　　下面，我們?cè)龠M(jìn)階一下，來個(gè)敏感性分析2.0難度！

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

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

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

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

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

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