日期:2009-03-12
有關本息平均攤還的貸款應用相當多,諸如房屋貸款、信用貸款以及車貸都屬於這方面的應用。Excel也提供了許多相關的函數,這篇只要介紹本息攤還有關的函數有PMT、IPMT、PPMT、CUMIPMT以及CUMPRINC。這些函數主要計算下列功能:
函數名稱 |
計算下列金額 |
PMT | 每期繳款金額(本金+利息) |
IPMT | 第n期的利息金額 |
PPMT | 第n期的本金金額 |
CUMIPMT | 兩期間(m~n)的利息金額總和 |
CUMPRINC | 兩期間(m~n)的本金金額總和 |
本息平均攤還的意思是,貸款者向債權者借了一筆款項,以約定的年利率,在約定的期間內每期以固定金額償還本金及利息。每期除了利息外還償還部分本金,一直到最後一期將本金還清。也就是說第一期到最後一期所繳的金額都一樣。
例如信用貸款30萬元,年利率6.5%,以每月為一期繳納利息及本金,總共分24期(2年)還清。本息平均攤還特色是每期除了繳納利息外,還得償還本金,但是每一期的本金加上利息的金額都一樣。下圖所示24期所繳納的金額,咖啡色部份是利息,青色部份是本金部分,可以看出每期兩者加總的金額都一樣(13,364),但是利息及本金的比例每期都不一樣,前期利息的份量比較多,愈到後期本金的比例就比較大。
每期繳款金額可以用PMT函數輕易算出,只要輸入貸款條件當參數,立刻可求出每期該繳的本息平均攤還金額。 PMT的參數如下:
PMT(rate,nper,pv, fv, type)
名稱 |
意義 |
範例參數值 |
rate | 每期利率 |
6.5% / 12 |
nper | 總期數 |
24 |
pv | 貸款金額 |
300000 |
fv | 年金終值 |
省略(0) |
type | 金額的給付時點 |
省略(期末?付) |
因為PMT函數是年金通用函數,fv和type於貸款計算時可以省略,所以暫時不提比較不會複雜。所以這例子每月繳款金額:
=pmt(6.5%/12, 24, 300000) = -13,364 (負值代表現金流出,就是每期必須支付金額)
要注意的是利率要和期數一致,本範例為每月為一期,那麼利率就必須用『月』利率,亦就是年利率除以12,這就是為何rate參數用6.5%/12的原因。
我最喜歡用『反推法』來做攤還表,為何說是反推法呢?就是先用PMT函數算出每期繳款金額(下圖儲存格B4),然後從第一期所繳的利息及償還本金,一路反算貸款餘額,直到最後一期的貸款餘額為零為止。這種方法驗算方式是:最後一期的貸款餘額若為零,就知道正確無誤。其實這種算法是最符合直覺,也最為讓人接受。下圖便是用反推法算出的攤還表。
算法是每一期都以“前期”的貸款餘額為基礎,計算當期該繳的利息。然後將每期繳款金額扣除當期利息後,就是當期所償還的本金了。那麼“前期”的貸款餘額扣除掉當期所償還本金,當然就變成當期的貸款餘額了。然後由第一期開始一路往下算,到最後一期的貸款餘額當然一定是零,否則就是哪裡有錯了。
根據這做法:
第一期:利息=300000*(6.5%/12) =
1,625;償還本金=13364-1625=11,739;貸款餘額=300000-11739=288,261
第二期:利息=288261*(6.5%/12) = 1,561;償還本金=13364-1561=11,802;貸款餘額=288261-11802=276,459
... 依此類推(如下圖所示)
可以看出每期的貸款餘額一路下降,到最後一期的貸款餘額(儲存格B31)恰好等於零!也就是算法無誤。
除了反推法外,Excel提供了IPMT函數可以立即知道任何一期所繳的利息是多少,以及PPMT函數可以計算任何一期所償還的本金是多少。IPMT及PPMT函數的參數如下:
IPMT(rate, per,
nper, pv, fv, type)
PPMT(rate, per,
nper, pv, fv, type)
這兩個函數和PMT函數唯一不一樣的是多了一個第二參數per,就是要算第幾期的意思啦。例如我們要知道上圖攤還表第4期的利息部分金額是多少,可以用IPMT函數來計算:
=IPMT(6.5%/12, 4, 24, 300000)
= -1,433
跟上圖儲存格D11的數值一模一樣。
同樣很容易的,也可以用PPMT函數求出第4期償還本金部分是多少:
=PPMT(6.5%/12, 4, 24, 300000)
= -11,931
跟上圖儲存格C11的數值是一樣的。
攤還表也可以直接利用IPMT及PPMT,來計算每期所要繳交的利息以及償還本金的金額。讀者可以下載本範例的Excel,裡面有兩個攤還表,一個是反推法做的,另一個是用IPMT及PPMT計算的,可以發現兩者數值是一模一樣。
Excel財務函數不只可以計算某一期的本金及利息外,也可計算兩期之間總共繳納的利息是多少,或者償還了多少本金。CUMIPMT可以求出兩期之間總共繳了多少利息;CUMPRINC則可以求出兩期之間總共償還多少本金。這兩個函數的參數分別如下:
CUMIPMT(rate,nper,pv,start_period,end_period,type)
CUMPRINC(rate,nper,pv,start_period,end_period,type)
這兩個函數所有的參數都必須填入,不可省略。rate, nper, pv, type是貸款條件,start_perido及end_perido分別代表所要計算起始期數及結束期數。
參數 | 意義 | 備註 |
rate | 每期利率 | 貸款條件 |
nper | 總期數 | 貸款條件 |
pv | 貸款金額 | 貸款條件,pv只能輸入正值,不接受負值 |
start_period | 計算的起始期數 | |
end_period | 計算的終止期數 | |
type | 0:期末付款 1:期初付款 |
貸款條件,通常為0 |
還有這兩個函數必須安裝「分析工具箱」,否則會傳回#NAME? 錯誤。
1) 工具/增益集
2) 將分析工具箱打勾
3) 按確定
例如範例的攤還表,如果想知道第2期至第6期總共繳交了多少利息,只要將貸款條件月利率6.5%/12,24期,貸款金額300000以及所要計算的期數2~6期,輸入函數:
=CUMIPMT(6.5%/12, 24, 300000, 2, 6, 0)
= -7,164 (負值代表現金流出)
便可得到2~6期利息之加總7,164元(如下圖粉紅色儲存格)。
同樣的,如果想知道第2期至第6期總共償還了多少本金,只要將貸款條件月利率6.5%/12,24期,貸款金額300000以及所要計算的期數2~6期,輸入函數:
=CUMPRINC(6.5%/12, 24, 300000, 2, 6, 0)
= -59,655 (負值代表現金流出)
便可得到2~6期已償還本金之加總59,655元(如下圖粉紅色儲存格)。
如果我們想知道某一期的貸款餘額,Excel並沒有提供這樣的函數,但是可以配合CUMPRINC函數來計算得到所要的答案。例如想知道第10期末時,貸款餘額還剩多少?這時可以用貸款金額扣除掉1~10期所有償還的本金就可以了。例如本範例第10期之貸款餘額也可以由下列方式求得:
=300000 + CUMPRINC(6.5%/12, 24, 300000, 1,
10, 0) --------因為CUMPRINC結果是負值所以前面用加號
=179,708
總繳利息計算方式如下:
總繳利息 = 每期本息繳款*總期數 - 貸款金額
例如範例中每期繳款13363.8754300534元,24期總共繳交
=24*13363.8754300534
= 320,733
扣除掉貸款金額30萬,全部利息等於20,733元。
我們也可以用CUMIPMT函數來計算總利息,只要將起始期數設定為1,終止期數設定為最後一期24就可以了:
=CUMIPMT(6.5%/12, 24, 300000, 1, 24, 0)
= -20,733
可以看出兩者答案完全相吻合。