基于VBA的住房貸款規劃模型設計與實現

［摘 要］ 本文利用Excel中的VBA功能，設計住房貸款規劃模型。利用所設計模型，在輸入貸款額、期限和利率后，可計算等額本息還款法或等額本金還款法各期的還款額、還款額中的本金部分和利息部分、累計還款本金、累計還款利息。利用該模型，還可以計算提前還貸情況下，已經償還的本金、尚未償還的本金、提前還款后每期還款額、還款本金及利息。

［關鍵詞］ 住房貸款模型；等額本金還款法；等額本息還款法

doi : 10 . 3969 / j . issn . 1673 - 0194 . 2011 . 17. 017

［中圖分類號］F232 ［文獻標識碼］A ［文章編號］1673 - 0194（2011）17- 0032- 03

隨著家庭財富的不斷積累，個人理財逐漸被更多的家庭所接受與關注。雖然在政府一系列調控措施下房屋投資性需求被抑制，但房屋的剛性需求仍然很強。基于房貸政策的進一步縮緊，如何設計房貸，成為許多家庭理財的一個重要方面。金融機構提供的住房貸款期限、利率、還償方式等各不相同，存在許多可選擇的方案，如何簡便計算，成為一種迫切的需求。本文結合等額本息、等額本金兩種方式，基于ＶＢＡ設計還貸模型，解決不同還貸方案的測算，以及各影響中因素變化后，對還款額的影響。

一、還貸方法介紹

住房貸款的規劃涉及貸款額度、貸款期限、貸款利率、還款方式、提前還貸、利率變動、延期還款等因素。住房貸款一般金額較大，如果期限太長，借款人需要支付較多的利息；若期限太短，則每期還款額高，可能使借款人陷入財務危機，所以借款人應制定合理的還款方式，這樣，選擇不同的還款方式，計算相應的每期還款額，并考慮提前還貸或延期還款等情況，做好還款規劃，既可以償還債務又合理分配各期的債務負擔。在計算各期還款額時，常用的方法是等額本息還款法與等額本金還款法。等額本息還款法是指在貸款期限內每月以相等的金額平均償還貸款本金和利息的還款方法。其計算公式如下：

２．系統實現

在基本數據區域中輸入貸款額、期限和利率，并在等額本息或等額本金區域中輸入要計算的期數，單擊計算按鈕，就可以計算出兩種方法下的該月還款額、其中的本金與利息，到該月為止已經償還的本金合計和利息合計。

計算按鈕的代碼如下：

Ｐｒｉｖａｔｅ Ｓｕｂ ＣｏｍｍａｎｄＢｕｔｔｏｎ１＿Ｃｌｉｃｋ（）

Ｄｉｍ ｂｊ Ａｓ Ｓｉｎｇｌｅ， ｌｘ Ａｓ Ｓｉｎｇｌｅ， ｂｊｈｊ Ａｓ Ｓｉｎｇｌｅ， ｌｘｈｊ Ａｓ Ｓｉｎｇｌｅ

ＴｅｘｔＢｏｘ４．ＳｅｔＦｏｃｕｓ

ＴｅｘｔＢｏｘ７ ＝ Ｒｏｕｎｄ（Ｐｍｔ（利率 ／ １２， 期限， －貸款額， ， １）， ２）

Ｃａｌｌ ａｂｃ（Ｖａｌ（ＴｅｘｔＢｏｘ４．Ｖａｌｕｅ）， ｂｊ， ｌｘ， ｂｊｈｊ， ｌｘｈｊ）

ＴｅｘｔＢｏｘ５ ＝ ｂｊ

ＴｅｘｔＢｏｘ６ ＝ ｌｘ

ＴｅｘｔＢｏｘ８ ＝ ｂｊｈｊ

ＴｅｘｔＢｏｘ９ ＝ ｌｘｈｊ

ｂｊ ＝ ０： ｌｘ ＝ ０： ｂｊｈｊ ＝ ０： ｌｘｈｊ ＝ ０

Ｃａｌｌ ｃｄｅ（Ｖａｌ（ＴｅｘｔＢｏｘ４．Ｖａｌｕｅ）， ｂｊ， ｌｘ， ｂｊｈｊ， ｌｘｈｊ）

ＴｅｘｔＢｏｘ１１ ＝ ｂｊ

ＴｅｘｔＢｏｘ１２ ＝ ｌｘ

ＴｅｘｔＢｏｘ１４ ＝ ｂｊｈｊ

ＴｅｘｔＢｏｘ１５ ＝ ｌｘｈｊ

ＴｅｘｔＢｏｘ１３ ＝ ｌｘ ＋ ｂｊ

Ｅｎｄ Ｓｕｂ

等額本息的計算過程代碼：

Ｐｒｉｖａｔｅ Ｓｕｂ ａｂｃ（ａ Ａｓ Ｉｎｔｅｇｅｒ， ｂｊ Ａｓ Ｓｉｎｇｌｅ， ｌｘ Ａｓ Ｓｉｎｇｌｅ， ｂｊｈｊ Ａｓ Ｓｉｎｇｌｅ， ｌｘｈｊ Ａｓ Ｓｉｎｇｌｅ）

ｂｊｈｊ ＝ ０： ｌｘｈｊ ＝ ０

Ｆｏｒ ｉ ＝ １ Ｔｏ ａ

ｌｘ ＝ （貸款額 － ｂｊｈｊ）*利率 ／ １２

ｂｊ ＝ ＴｅｘｔＢｏｘ７．Ｖａｌｕｅ － ｌｘ

ｂｊｈｊ ＝ ｂｊｈｊ ＋ ｂｊ

ｌｘｈｊ ＝ ｌｘｈｊ ＋ ｌｘ

Ｎｅｘｔ ｉ

Ｅｎｄ Ｓｕｂ

等額本金的計算過程代碼：

Ｐｒｉｖａｔｅ Ｓｕｂ ｃｄｅ（ａ Ａｓ Ｉｎｔｅｇｅｒ， ｂｊ Ａｓ Ｓｉｎｇｌｅ， ｌｘ Ａｓ Ｓｉｎｇｌｅ， ｂｊｈｊ Ａｓ Ｓｉｎｇｌｅ， ｌｘｈｊ Ａｓ Ｓｉｎｇｌｅ）

ｂｊｈｊ ＝ ０： ｌｘｈｊ ＝ ０

Ｆｏｒ ｉ ＝ １ Ｔｏ ａ

ｂｊ ＝ 貸款額 ／ 期限

ｌｘ ＝ （貸款額 － ｂｊｈｊ） * 利率 ／ １２

ｂｊｈｊ ＝ ｂｊｈｊ ＋ ｂｊ

ｌｘｈｊ ＝ ｌｘｈｊ ＋ ｌｘ

Ｎｅｘｔ ｉ

Ｅｎｄ Ｓｕｂ

五、總結

利用ＶＢＡ設計的住房貸款規劃模型，在輸入貸款額、貸款期和利率的情況下，可計算出需要的某月份在兩種不同償還方式下的該月還款額、其中的本金部分與利息，以及到該月為止，已經償還的本金合計、利息合計，方便進行住房貸款決策。

主要參考文獻

［１］中國就業培訓技術指導中心．助理理財規劃師專業能力［Ｍ］．北京：中國財政經濟出版社，２００6.

［２］杜亞斌． 金融建模：使用Ｅｘｃｅｌ和ＶＢＡ［Ｍ］． 北京：機械工業出版社，２０１０.

注：本文中所涉及到的圖表、注解、公式等內容請以PDF格式閱讀原文