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

羅小蘭

(南京金肯職業(yè)技術(shù)學(xué)院，江蘇 南京 211156)

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

羅小蘭

(南京金肯職業(yè)技術(shù)學(xué)院，江蘇 南京 211156)

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

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

Excel是功能豐富、普遍使用的電子表格，提供了一系列財(cái)務(wù)函數(shù)幫助人們解決實(shí)際問(wèn)題，理清思路后，使用相關(guān)函數(shù)建立邏輯關(guān)系就能成功構(gòu)建模型。

一、準(zhǔn)備

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

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

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

x=Am+Amβ

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

2.Excel中相關(guān)財(cái)務(wù)函數(shù)介紹。雖然有公式，在計(jì)算每月還款額時(shí)仍然很復(fù)雜繁瑣，計(jì)算等額本息Excel提供了PMT、PPMT、IPMT函數(shù)，分別用來(lái)計(jì)算每期的還款額、每期本金、每期利息，以PMT函數(shù)為例：

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

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

二、思路

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

三、實(shí)際操作

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

圖1

圖2

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

圖3

應(yīng)付本金；應(yīng)付利息；應(yīng)付本息經(jīng)計(jì)算為負(fù)值，表示資金的流出。(圖4)

2.邏輯關(guān)系及公式編寫

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

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

圖4

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

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

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

3.注意事項(xiàng)及技巧

Excel電子表格的一大優(yōu)勢(shì)是可以復(fù)制公式到其他單元格，電子表格會(huì)按照對(duì)應(yīng)的邏輯關(guān)系自動(dòng)匹配，為我們減少了相當(dāng)?shù)墓ぷ髁?。在操作中需要注意的有?1)財(cái)務(wù)函數(shù)計(jì)算結(jié)果根據(jù)資金的流向不同結(jié)果為正值或負(fù)值，與其他數(shù)據(jù)計(jì)算時(shí)需要注意；(2)Excel中默認(rèn)為相對(duì)應(yīng)用，必要時(shí)可用類似“$A$1”的形式表示絕對(duì)引用；(3)如縮短還款期限，原末期單元格公式無(wú)意義，會(huì)顯示錯(cuò)誤值，可用IFERROR、ISNA等函數(shù)調(diào)整。

四、實(shí)例

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

圖5-1

圖5-2