基于Excel的房貸還款模型構建與分析

程冉

[摘 要]房貸使用何種還款方式及利弊差異，大多“準房奴”無所適從。文章首先對常見房貸還款方式進行介紹，再結合Excel財務函數功能構建了還款模型，不僅從細節上對每期還款數據進行了計算，還從整體上進行了展示。

[關鍵詞]還款模型；Excel；財務函數

10 13939/j cnki zgsc 2015 51 154

1 引 言

“有房才有家，有房才有根。”是絕大多數國人對于買房而不租房過日子的論調。房價如此的高，貸款買房成為必然。貸款方式多種多樣，如何選擇，如何計算又成為買房路上的攔路虎。Excel是數據處理最有效的工具，使用Excel構建還款模型、展示結果、分析成因就顯得順理成章。房貸還款方式主要分為等額本金、等額本息和公積金自由三種還款方式。

2 貸款方式及對應Excel函數介紹

2 1 房貸還款方式概述及差異比較

等額本金、等額本息、公積金自由三種還款方式各有優缺點，如表1所示。

表1 三種還款方式差異比較

[]特點[]缺點[]適用人群

等額本金[]本金保持相同，利息逐月遞減，月還款數遞減[]貸款人起初還款壓力較大，但是隨時間的推移每月還款數也越來越少[]適合有一定經濟基礎，能承擔前期較大還款壓力，且有提前還款計劃的借款人

等額本息[]本金逐月遞增，利息逐月遞減，月還款數不變[]支出利息較多，還款初期利息占月供款的大部分，隨本金逐漸返還，本金占每月供款的比重增加[]每月歸還相同的款項，方便安排收支，適合經濟條件不允許前期還款投入過大，收入處于較穩定狀態的借款人

公積金自由[]只要不低于最低還款額，借款人可以自由設定每月的還款金額[]還款人一直按最低還款額進行還款，最后一期還款額數目較大，通常為貸款數額的10%～20%[]適用于公積金貸款且收入逐年遞增的中青年人群，月還款額調整較頻繁

2 2 Excel財務函數介紹

Excel中包含多種類型函數，其中財務函數可進行常見財務計算，如確定貸款的支付額、投資的未來值或凈現值，以及債券或息票的價值，功能十分強大。結合實例，函數PMT、IPMT、PPMT分別用于計算等額本息還款額、利息、本金。

3 還款模型構建

3 1 案例

某人擬購置一處房產，需從銀行貸款80萬元，公積金月貸款利率為：3 54167‰，貸款30年，試構建還款模型，比較等額本金、等額本息及自由還款方式的差異，從總還款額、支付的利息及每期還款額等幾個方面說明。

3 2 構建模型

模型如下圖所示，在進行公式錄入前先來介紹幾個公式：

支付的利息=期初余額×貸款利率

本期還款額=支付的利息+償還的本金

期末余額=期初余額-償還的本金

下一期期初余額=上一期期末余額

基于上述公式，期初余額已知，則支付的利息確定，根據不同還款方式確定本期還款額或償還的本金即可確定其余所有項目。

等額本金償還方式，償還的本金=貸款額/總付款期數；等額本息償還方式，本期還款額=PMT（$B$4，$B$6，-$B$3），第三個參數pv為負值，支付的利息=IPMT（$B$4，A377，$B$6，-$B$3），償還的本金=PPMT（$B$4，A377，$B$6，-$B$3）；自由還款方式，最低還款額為合同規定的最低值，案例設定為3641元，其余項可由公式計算得到。

房貸還款模型圖

4 結果與分析

根據房貸還款模型對案例進行計算，結果如下圖、表2所示。顯見，自由還款方式所要支付的利息最多，且最后一期需還￥217，413 46；等額本金方式最初還款額為￥5，055 56，逐年遞減，最后一期為￥2，230 09；等額本息方式每期還款額固定為￥3，935 52。

5 小 結

通過使用Excel函數，構建了房貸還款模型，充分展示了三種房貸還款方式的差異。模型實現了計算過程自動化，只需輸入貸款額、利率、年限即可由此計算出三種還款方式的每期數值。模型既從細節上展示了每期的還款額、利息和本金，又從整體上給出了三種方式的利弊差異。

總的來說，模型的構建闡明了三種房貸還款方式的特點及適用對象，為無所適從的“準房奴”提供了詳盡的數據支持。

表2 三種還款方式對比表

還款方式還款總額支付的利息償還的本金

等額本金￥1，311，417 15￥511，417 15￥800，000 00

等額本息￥1，416，787 56￥616，787 56￥800，000 00

自由還款￥1，524，532 46￥724，532 46￥800，000 00

參考文獻：

[1]王海林 Excel財務管理建模與應用[M].北京：電子工業出版社，2014

[2]張山風，周鳳 Excel財務函數應用解析[J].辦公自動化雜志，2010（178）：47-50

[3]呂志明 個人住房貸款策略及模型構建[J].財會通訊，2007（4）：25-26