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

程冉

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

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

10 13939/j cnki zgsc 2015 51 154

1 引 言

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

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

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

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

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

[]特點(diǎn)[]缺點(diǎn)[]適用人群

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

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

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

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

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

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

3 1 案例

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

3 2 構(gòu)建模型

模型如下圖所示，在進(jìn)行公式錄入前先來介紹幾個(gè)公式：

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

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

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

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

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

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

房貸還款模型圖

4 結(jié)果與分析

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

5 小 結(jié)

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

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

表2 三種還款方式對(duì)比表

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

等額本金￥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

參考文獻(xiàn)：

[1]王海林 Excel財(cái)務(wù)管理建模與應(yīng)用[M].北京：電子工業(yè)出版社，2014

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

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