基于Excel的通用按揭還款模型研究

羅小蘭

(南京金肯職業技術學院，江蘇 南京 211156)

基于Excel的通用按揭還款模型研究

羅小蘭

(南京金肯職業技術學院，江蘇 南京 211156)

隨著時代發展和大眾消費心理的變遷，“借錢消費、分期付款”模式逐漸被越來越多的人所接受，按揭還款在住房、汽車等耐用品消費中漸成主流。非財會專業的社會大眾很難區分等額本息、等額本金之類術語的含義，遑論數字背后基于貨幣時間價值的內在邏輯。即使是專業人士，面對利率調整、還款期限、提前還本等變化，快速便捷計算出每期還款額也并非易事。本文介紹一種方法，利用普遍使用的Excel電子表格構建一個本金余額、剩余期限、貸款利率都可變的通用模型，基本涵蓋現實生活中可能出現的各種情形。

按揭還款；通用模型；多變量；excel

Excel是功能豐富、普遍使用的電子表格，提供了一系列財務函數幫助人們解決實際問題，理清思路后，使用相關函數建立邏輯關系就能成功構建模型。

一、準備

1.按揭分期的理論基礎。按揭(Mortgage)是指以房地產等實物資產或有價證券、契約等作抵押，獲得銀行貸款并依合同分期付清本息，貸款還清后銀行歸還抵押物。分期付款的形式一般有兩種：(1)等額本息。等額本息的特點是在還款期內每期(月)的還款本息之和固定，適合每月資金流較為穩定的人群(如工薪階層)，每期還款額x的計算公式為：

x=Aβ(1+β)m(1+β)m-1

其中A為貸款總額，β為月利率，m為總還款期數；(2)等額本金。即每期按相等的金額償還貸款本金，利息按剩余本金計算并逐月結清，兩者合計即為每期的還款額，由于占用銀行資金在時間、金額上較等額本息方式少，能夠減少利息支出，每期還款額x的計算公式為：

x=Am+Amβ

其中A為貸款總額，m為總還款期數，Am為第m期本金余額，β為月利率。

2.Excel中相關財務函數介紹。雖然有公式，在計算每月還款額時仍然很復雜繁瑣，計算等額本息Excel提供了PMT、PPMT、IPMT函數，分別用來計算每期的還款額、每期本金、每期利息，以PMT函數為例：

X=PMT(rate,nper,pv,fv,type)

其中，rate為每期利率，nper為總期數，pv為本金，fv為終值(此處可省略)，type為類型，默認值為0(普通年金)。PPMT、IPMT用法類似，可參閱Excel幫助。

二、思路

考慮貼近生活實際，本金余額、利率、剩余期限可能在任何一期發生變動，為保證模型的通用性和靈活性，采用逐期處理的方式來進行處理，分別計算每期償還本金、利息、本金余額。具體步驟為：(1)計算本金余額；(2)根據本期利率和剩余還款期限分別計算本期償還本金、利息；(3)下一期以此類推迭代計算。

三、實際操作

1.表格設計。(1)初始值。主要變量有：貸款總額；年利率(%)，計算中除以12得月利率；還款期限(年)，計算中乘以12得總期數；還款方式，方便輸入避免出錯可選菜單“數據-數據有效性-設置-有效性條件”，在“允許”中選擇“序列”，并在下方文本框中輸入“等額本息，等額本金”(見圖1、圖2)。

圖1

圖2

(2)表格項目。時間，表示實際年月份；期數，表示當期在總期數中的位置；剩余期數，表示預期還款時間；減少期數，提前還款時可約定減少還款期；利率，一般每年初調整為最近利率水平；本金余額，表示當前占用銀行資金額度；提前還本，表示當期提前還款額；(圖3)

圖3

應付本金；應付利息；應付本息經計算為負值，表示資金的流出。(圖4)

2.邏輯關系及公式編寫

(1)本金余額。第1期直接引用初始值，本例中公式為“=N3”；第2期以后本金余額=上期本金余額-上期已還本金-上期提前還本額，如第2期公式為“=F2+H2+G2”(還款為負值，故用加號)。

(2)剩余期數。第1期末時，剩余期數為總期數減1，公式為“=N5*12-1”，第2期后可能有縮短還款期，公式為“=C2-D2-1”。

圖4

(3)應還本金。根據還款方式的不同，應還本金應用不同的公式，等額本息時本期應還本金為“=PPMT(E2/1200,1,C2+1,F2)”，等額本金時每期本金為“=-F2/(C2+1)”。為減少人工干預，使用IF函數自動判斷，公式為：“=IF($N$2=‘等額本金’,-F2/(C2+1),PPMT(E2/1200,1,C2+1,F2))”。

(4)應還利息。同上，使用IF函數自動判斷當前選擇的還款方式，公式為：“=IF($N$2=‘等額本金’,-F2*E2/1200,IPMT(E2/1200,1,C2+1,F2))”。

(5)應還本息。即每期的本金與利息之和，可用PMT函數計算，也可直接相加。

3.注意事項及技巧

Excel電子表格的一大優勢是可以復制公式到其他單元格，電子表格會按照對應的邏輯關系自動匹配，為我們減少了相當的工作量。在操作中需要注意的有：(1)財務函數計算結果根據資金的流向不同結果為正值或負值，與其他數據計算時需要注意；(2)Excel中默認為相對應用，必要時可用類似“$A$1”的形式表示絕對引用；(3)如縮短還款期限，原末期單元格公式無意義，會顯示錯誤值，可用IFERROR、ISNA等函數調整。

四、實例

以貸款100萬元20年，年利率5.65，分別采用等額本金、等額本息方式，模擬提前還款、縮短還款期情形測試該模型均計算正確，如有需要還可進一步完善實現組合貸款試算。(圖5-1、圖5-2)

圖5-1

圖5-2