基于Excel的房貸還款模型構(gòu)建與分析

程冉

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

[關(guān)鍵詞]還款模型；Excel；財務(wù)函數(shù)

10 13939/j cnki zgsc 2015 51 154

1 引 言

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

2 貸款方式及對應(yīng)Excel函數(shù)介紹

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

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

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

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

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

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

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

2 2 Excel財務(wù)函數(shù)介紹

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

3 還款模型構(gòu)建

3 1 案例

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

3 2 構(gòu)建模型

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

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

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

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

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

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

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

房貸還款模型圖

4 結(jié)果與分析

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

5 小 結(jié)

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

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

表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財務(wù)管理建模與應(yīng)用[M].北京：電子工業(yè)出版社，2014

[2]張山風(fēng)，周鳳 Excel財務(wù)函數(shù)應(yīng)用解析[J].辦公自動化雜志，2010（178）：47-50

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