���������u�ٸպ� ���������u�٪k�O�N���������b�U�ڴ����v�١A�C���v�٪��������ۦP�A�ӨC�����u�٪��Q���o�]�ֿn���u�٤������v����֦Ӵ�֡A�]���A�C�����u�٪����Q�M�|�V�ӶV�֡C �бz��J�H�U��ơA���U�i�� ��j��Y�i�i��պ�G
�Ƶ��G���պ�G�ȨѰѦҤ��ΡC �^�W�� 有關本息平均攤還的貸款應用相當多,諸如房屋貸款、信用貸款以及車貸都屬於這方面的應用。Excel也提供了許多相關的函數,這篇只要介紹本息攤還有關的函數有PMT、IPMT、PPMT、CUMIPMT以及CUMPRINC。這些函數主要計算下列功能:
何謂本息平均攤還本息平均攤還的意思是,貸款者向債權者借了一筆款項,以約定的年利率,在約定的期間內每期以固定金額償還本金及利息。每期除了利息外還償還部分本金,一直到最後一期將本金還清。也就是說第一期到最後一期所繳的金額都一樣。 例如信用貸款30萬元,年利率6.5%,以每月為一期繳納利息及本金,總共分24期(2年)還清。本息平均攤還特色是每期除了繳納利息外,還得償還本金,但是每一期的本金加上利息的金額都一樣。下圖所示24期所繳納的金額,咖啡色部份是利息,青色部份是本金部分,可以看出每期兩者加總的金額都一樣(13,364),但是利息及本金的比例每期都不一樣,前期利息的份量比較多,愈到後期本金的比例就比較大。 PMT函數每期繳款金額可以用PMT函數輕易算出,只要輸入貸款條件當參數,立刻可求出每期該繳的本息平均攤還金額。 PMT的參數如下: PMT(rate,nper,pv, fv, 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 下載Excel範例可以看出每期的貸款餘額一路下降,到最後一期的貸款餘額(儲存格B31)恰好等於零!也就是算法無誤。 IPMT及PPMT函數除了反推法外,Excel提供了IPMT函數可以立即知道任何一期所繳的利息是多少,以及PPMT函數可以計算任何一期所償還的本金是多少。IPMT及PPMT函數的參數如下: IPMT(rate, per, nper,
pv, fv, type) 這兩個函數和PMT函數唯一不一樣的是多了一個第二參數per,就是要算第幾期的意思啦。例如我們要知道上圖攤還表第4期的利息部分金額是多少,可以用IPMT函數來計算: 同樣很容易的,也可以用PPMT函數求出第4期償還本金部分是多少: 攤還表也可以直接利用IPMT及PPMT,來計算每期所要繳交的利息以及償還本金的金額。讀者可以下載本範例的Excel,裡面有兩個攤還表,一個是反推法做的,另一個是用IPMT及PPMT計算的,可以發現兩者數值是一模一樣。 CUMIPMT及CUMPRINC函數Excel財務函數不只可以計算某一期的本金及利息外,也可計算兩期之間總共繳納的利息是多少,或者償還了多少本金。CUMIPMT可以求出兩期之間總共繳了多少利息;CUMPRINC則可以求出兩期之間總共償還多少本金。這兩個函數的參數分別如下: CUMIPMT(rate,nper,pv,start_period,end_period,type) 這兩個函數所有的參數都必須填入,不可省略。rate, nper, pv, type是貸款條件,start_perido及end_perido分別代表所要計算起始期數及結束期數。
還有這兩個函數必須安裝「分析工具箱」,否則會傳回#NAME? 錯誤。 1) 工具/增益集 例如範例的攤還表,如果想知道第2期至第6期總共繳交了多少利息,只要將貸款條件月利率6.5%/12,24期,貸款金額300000以及所要計算的期數2~6期,輸入函數: 同樣的,如果想知道第2期至第6期總共償還了多少本金,只要將貸款條件月利率6.5%/12,24期,貸款金額300000以及所要計算的期數2~6期,輸入函數: 第n期之貸款餘額
如果我們想知道某一期的貸款餘額,Excel並沒有提供這樣的函數,但是可以配合CUMPRINC函數來計算得到所要的答案。例如想知道第10期末時,貸款餘額還剩多少?這時可以用貸款金額扣除掉1~10期所有償還的本金就可以了。例如本範例第10期之貸款餘額也可以由下列方式求得: 如何計算總利息總繳利息計算方式如下: 例如範例中每期繳款13363.8754300534元,24期總共繳交 我們也可以用CUMIPMT函數來計算總利息,只要將起始期數設定為1,終止期數設定為最後一期24就可以了: 相關文章 EXCEL投資理財應用 |