基于Ｅｘｃｅｌ的等額還款與等本還款計算模型

［摘 要］ 等額還款與等本還款是按揭貸款中兩種最常見的還款方式，筆者構建了基于Excel的等額還款與等本還款計算模型，模型由貸款參數與數據區域兩部分組成。通過該模型可以非常容易得到貸款周期內各期需要償還的本金、利息，總還款額，提前還貸時的還款額，貸款利率變動時的還款額等重要信息，有助于事前制定合理的按揭貸款計劃。

［關鍵詞］ 等額還款；等本還款；計算模型；Excel

［中圖分類號］F232；F275.1［文獻標識碼］A［文章編號］1673-0194（2008）06-0041-03

1 引 言

在現代社會中，隨著居民收入水平的提高以及消費觀念的轉變，按揭貸款購房、購車應運而生。按揭貸款的還款方式有兩種——等額還款法與等本還款法。二者的主要區別在于等本還款法在整個貸款周期內支付的利息總和相對較少，提前還貸時“合算”，但主要缺點是前期還貸壓力很大。對于按揭者而言，不僅需要知道上述區別，還要把它們的區別量化。況且我國目前正處于一個加息周期中，有比較明確的加息預期，按揭者希望在貸款前進行敏感性分析以了解利率在目前基礎上每上升若干基點，還款額將增加多少，這樣有利于做出合適的貸款計劃。面對諸如此類的問題，筆者構建了基于Excel的等額還款與等本還款模型，能提供按揭者需要的各種數據，比如：每期的還貸額，每期償還的本金、利息，在某一期提前還貸時需要償還的金額等。當貸款利率等因素發生變動時，只需在模型中的貸款參數欄填入變動后的數值，上述各種按揭者需要的數據將自動更新，使用起來非常便利。

2 模型的構建

2. 1等額還款模型

等額還款模型包括兩部分，一是貸款參數部分，由貸款年利率、貸款月利率、貸款期限、貸款金額4個參數組成。其中貸款月利率等于貸款年利率除以12，由系統以公式的方式產生計算值，勿以人工方式填入，以防在除不盡時產生比較大的計算誤差，其余的三要素則以手工填入。二是各種還款數據區域，包括實際期次、計算期次、期初尚欠的貸款本金、本期需償還的利息、截至本期末累計償還的利息，本期需償還的本金，截至本期末累計償還的本金、本期還款之和、本期末尚欠的貸款本金等9個要素。此模型構建完畢后可以達到如下效果：在貸款參數中更換不同的數值，數據區域將自動更新。下面本文以一個實例來說明如何設計等額還款計算模型：小王利用公積金按揭貸款購房，貸款金額200 000元，年利率5.04%，貸款期限15年。

2. 1. 1要素間的鉤稽關系

要素1——實際期次。用來反映實際的還款時間，即第幾個月。本要素在“計算期次”的基礎上根據公式產生。

要素2——計算期次。用來計算“本期還款之和”、“本期需償還的本金”、“ 本期需償還的利息”等所需要的參數。本欄目的數據由人工利用Excel的填充功能產生，非由公式計算生成。

要素3——期初尚欠的貸款本金。第一期初尚欠的貸款本金等于總貸款金額，以后各期期初尚欠的金額等于上期期末尚欠的貸款本金。

要素4——本期需償還的利息。根據計息的基本原理，每期末的應付利息等于當期期初的貸款本金乘以當期的貸款利率。所以表1的D7單元格的公式為“=C7*$D$2”。或者直接用Excel財務函數IPMT，在D7單元格填入公式“=IPMT（$D$2，B7，$D$3，－$D$4）”。值得注意的是Excel財務函數涉及資金流入與流出的問題，資金流入用正數表示，資金流出用負數表示，本文在貸款金額D4前加上負號則保證計算出來的還款金額是正數，否則計算結果將以負數顯示。

要素5——截至本期末累計償還的利息。第一期利息的累計值等于第一期的實際值，以后各期利息的累計值等于上期的累計值加當期的實際值。

要素6——本期需償還的本金。在等額還款中每期需要償還的本金采用倒推的方式，即本期需償還的本金=本期還款之和－本期需償還的利息。所以表1的F7單元格的公式為“= H7－D7”。或者直接用Excel財務函數PPMT，在F7單元格填入公式“=PPMT（$D$2，B7，$D$3，－$D$4）”。

要素7——截至本期末累計償還的本金。參照要素5。

要素8——本期還款之和。等額還款從實質上講是年金，根據資金時間價值的基本原理，每期的還款額等于貸款金額除以相應的年金現值系數（P/A，i，n）或者乘以資本回收系數（A/P，i，n）。（A/P，i，n）= i÷[1－（1+i）-n]，i為貸款利率，n為貸款期限。于是可以在表1的H7單元格填入公式“=$D$2/（1－（1+$D$2） ^ （-$D$3））*$D$4”。或者直接用Excel財務函數PMT，在H7單元格填入公式“=PMT（$D$2，$D$3，－$D$4）”。從邏輯上講這是等額還款方式下的首要因素。

要素9——本期末尚欠的貸款本金。本要素實質上就是按揭者在本期提前還貸所需向銀行支付的金額，也是計算下期利息的基礎。它的計算可以用這樣兩種方式，一是：期初尚欠的本金－本期需償還的本金；二是：公積金貸款金額－截至本期累計償還的本金。因此在表1的I7單元格填入公式“=C7－F7”或者“=$D$4－G7”。

2. 1. 2公式列表與填充

綜上所述，表1第一期（1）~（9）各欄的公式依次為：

A7=B7（B列的數據由人工利用Excel的填充功能產生，非由公式計算生成）

C7=$D$4；D7=C7*$D$2 或者 D7=IPMT（$D$2，B7， $D$3， －$D$4）；

E7= D7；F7=H7－D7或者F7=PPMT（$D$2，B7，$D$3，

－$D$4）；G7=F7；

H7=$D$2/（1－（1+$D$2） ^ （-$D$3））*$D$4 或者H7=PMT（$D$2，$D$3，－$D$4）；

I7=C7－F7。

由于存在“已償還利息累計、已償還本金累計以及本期期初尚欠的貸款本金等于上期期末尚欠的貸款本金”的鉤稽關系，暫時還不能把各公式往下填充，需要在表1第二期的部分欄目填寫反映上述關系的公式后才能把完整的要素之間的鉤稽關系全部描述出來。表1第二期（3）、（5）、（7）欄的公式依次為：C8=I7；E8=E7+D8；G8=G7+F8。

至此，要素之間的鉤稽關系已經描述清楚，只要把各公式填充到底即可。值得注意的是，按照從左到右的順序填充各公式，填充過程中個別欄目會出現都為0的情況，當把表1的各欄目都填充完畢后就會出現正確結果。此外，在運用上述各公式時一定要注意絕對引用與相對引用的問題，不能弄錯。

本模型也可以用來解決組合貸款（公積金貸款+商業貸款）條件下各要素的計算，只需要仿照表1再設計一張表即可，見表2。

另外，由于表格較大，為了方便查閱，可以定位于C7單元格，然后選擇“窗口”菜單中的“凍結窗格”功能。

2. 2等本還款模型

2. 2. 1要素間的鉤稽關系

模型的結構與表1完全一樣，要素間的鉤稽關系與等額還款方式基本一致，這里不再重復。兩種還款方式的區別在于“本期需償還的本金”、“本期還款之和”這兩個要素。等本還款方式下，本期需償還的本金等于貸款總額除以期限，然后在此基礎上加本期需償還的利息得到本期還款之和。等額還款方式下則根據年金原理先計算出每期的還款之和，然后以本期還款之和扣除當期應償還的利息倒推當期需償還的本金。

2. 2. 2公式列表與填充

綜上所述，表3第一期（1）~（9）各欄的公式依次為：

A7=B7 （B列的數據由人工利用Excel的填充功能產生，非由公式計算生成）

C7= $D$4；D7=C7*$D$2；E7= D7；F7=$D$4/$D$3；G7=F7；H7=D7+F7；I7=C7－F7。

表3第二期（3）、（5）、（7）各欄的公式依次為：C8=I7；E8=E7+D8；G8=G7+F8。要素之間的鉤稽關系描述清楚后，把各公式填充到底即可，結果見表3。

2. 3兩種還款方式比較

2. 3. 1等額償還法的前期還款壓力小

比較表1與表3明顯可以發現這一點，直到第79期，等本還款法下的每月還款金額才下降到1 587.11元，接近等額還款下的每月1 585.76元。從第80期開始等本償還方式的還款壓力低于等額還款方式，越往后壓力越小。

2. 3. 2在整個貸款周期內，等額還款方式支付的利息多

在15年的貸款期內，等本償還方式共支付利息

76 020.00元，等額償還方式共支付利息85 436.4元，后者相對前者多支付了12.39% 。

2. 3. 3提前還貸時，等本償還方式相對經濟

如果按揭者在第五年末（第60期）選擇提前償還貸款，等額方式下需償還給銀行149 232.14元（表1的第（9）欄），等本方式下需償還給銀行133 333.33元，后者相對前者少付10.65% 。

3 貸款參數發生變化時模型的運用

是指當按揭者開始還款一段時間后，遇到諸如國家調整貸款利率、提前償還了部分貸款等情況，如何運用上述模型自動產生下一期的各種數據。解決這類問題的基本思想是：把貸款參數變動后的還款計算當作一筆新的按揭貸款來對待，在模型的貸款參數欄中輸入新的參數值，模型會自動更新數據。本文假定國家在第60期（第五年末）調整貸款利率至5.58%，新的利率從第61期開始執行，按揭者為此還在第60期提前償還了貸款30 000元。以等額還款為例說明如下：從表1中可以看出，第60期末尚欠貸款本金149 232.14元，提前償還30 000元后仍欠119 232.14元。可以把它看成一筆新的按揭貸款，本金119 232.14元，利率5.58%，期限120期（180－60）。在表1的貸款參數欄的D1、D3、D4單元格分別輸入5.58%、120、119 232.14，并把表1中的第127~186行全部選中并刪除，即保留原模型的第7~126行共120期的數據（要注意原有數據的備份與保存）。此時模型自動更新了數據，更新后的結果見表4。

需要說明的是，為了使累計的數據具有連續性，本文更改了E7、G7的計算公式，否則“截至本期末累計償還的利息”、 “截至本期末累計償還的本金”兩欄將重新開始累計。E7單元格的公式改為E7=D7+44 377.61（44 377.61是指截至第60期已累計償還的利息，見表1），G7單元格的公式由原來的改為G7=F7+50 767.86（50 767.86是指截止第60期已累計償還的本金，見表1），這兩個公式修改好即可，不需要再往下填充。此外，為了使實際期次反映真實情況，把A7單元格的公式改為“=B7+60”并往下填充，由此不難理解為什么在模型中設計“實際期次”與“計算期次”這樣兩個項目。由于本模型不是計算機編程，在發生貸款要素變動時需要對原有的部分公式稍加修改，這是其不盡人意之處，盡管公式的修改簡單快捷。

4 小 結

用Excel構建按揭貸款計算模型簡單易行，它可以向按揭各方提供各種重要的數據。從上面的論述中可以發現，“本期還款之和”、“本期需償還的本金”、“本期需償還的利息”是模型中最關鍵的3個因素。在等額還款模型中，按照“確定每期還款額”、“確定當期應付利息”、“倒推當期還本金額”的順序來進行。而在等本還款模型中則按照“確定每期還款本金”、“確定當期應付利息”、“確定當期還款額”的順序來進行。當發生貸款要素變動時，只需對原模型稍作修改便可以自動產生新貸款參數下的各種數據。

主要參考文獻

［1］ 史玉磊等. Excel函數與圖表實用大全［M］. 北京：電子工業出版社，2007.

［2］ 宋燕，劉丹. Excel實用財務系統設計［M］. 北京：電子工業出版社，2007.