怪老子ETF線上課程

Excel財務函數-貸款相關

日期: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),但是利息及本金的比例每期都不一樣,前期利息的份量比較多,愈到後期本金的比例就比較大。

cashflow

PMT函數

每期繳款金額可以用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
... 依此類推(如下圖所示)

下載Excel範例

schedule

可以看出每期的貸款餘額一路下降,到最後一期的貸款餘額(儲存格B31)恰好等於零!也就是算法無誤。

IPMT及PPMT函數

除了反推法外,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的數值一模一樣。

IPMT

同樣很容易的,也可以用PPMT函數求出第4期償還本金部分是多少:
=PPMT(6.5%/12, 4, 24, 300000)
= -11,931
跟上圖儲存格C11的數值是一樣的。

PPMT

攤還表也可以直接利用IPMT及PPMT,來計算每期所要繳交的利息以及償還本金的金額。讀者可以下載本範例的Excel,裡面有兩個攤還表,一個是反推法做的,另一個是用IPMT及PPMT計算的,可以發現兩者數值是一模一樣。

CUMIPMT及CUMPRINC函數

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_peridoend_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元(如下圖粉紅色儲存格)。

cumipmt

同樣的,如果想知道第2期至第6期總共償還了多少本金,只要將貸款條件月利率6.5%/12,24期,貸款金額300000以及所要計算的期數2~6期,輸入函數:
=CUMPRINC(6.5%/12, 24, 300000, 2, 6, 0)
= -59,655
(負值代表現金流出)
便可得到2~6期已償還本金之加總59,655元(如下圖粉紅色儲存格)。

cumprinc

第n期之貸款餘額

如果我們想知道某一期的貸款餘額,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
可以看出兩者答案完全相吻合。

相關文章

EXCEL投資理財應用
房屋貸款規劃
房屋貸款近階討論
多段式房貸利率如何比較




■ 本網站內容儘可能精確完整,但不保證無誤。若做為投資依據,風險請自行斟酌 ,本網站不負賠償之責任。
■ 網站所有資料均為版權所有,非經書面允許請勿轉載或使用。