基于Excel的購房規劃教學模型設計

【摘 要】本文利用Excel豐富的函數與強大的計算功能，建立了購房規劃教學模型。該模型解決了購房規劃教學中計算量大、計算過程煩瑣的問題，提高了學生的學習興趣和利用Excel軟件解決復雜問題的能力。

【關鍵詞】Excel 購房規劃 教學模型

【中圖分類號】G642 【文獻標識碼】A 【文章編號】1674-4810（2015）24-0075-02

在個人理財規劃的教學中，購房規劃由于計算公式較為復雜，計算過程較為煩瑣，導致學生望而生畏，同時也不利于教師清晰闡述各計算要素之間的關系，從而影響了教學質量。因此，需要建立一個計算便捷且可擴展的計算模型來解決上述問題，利用Excel豐富的函數與強大而靈活的計算功能可以建立便捷、實用的購房規劃教學模型。

一 購房規劃內容及原理

1.購房支付能力規劃

購房支付能力規劃是指根據計劃購房時的時點、房屋面積及購房者的經濟能力等，測算個人或家庭可負擔的房價。一種應用較廣泛的核算方法是年收入概算法，計算模型如下：

可負擔首付款=目前年收入×收入中負擔首付和房貸的比率上限×年金終值系數（n=距離購房年數，r=投資報酬率或市場利率）+目前凈資產×復利終值系數（n=距離購房年數，r=投資報酬率或市場利率）。

可負擔房貸總額=目前年收入×復利終值系數（n=距離購房年數，r=預計的收入增長率）×收入中負擔首付和房貸的比率上限×年金現值系數（n=貸款年限，i=房貸利率）。

可負擔房屋總價=可負擔的首付款+可負擔房貸總額；可負擔房屋單價=可負擔房屋總價÷需求面積。

2.購房籌資規劃

對于多數人來說，購房的開支太大，不能一次性付清購房款，需要向銀行貸款，從而涉及另外一個重要問題——購房籌資規劃。購房籌資規劃的核心問題是要確定貸款期限和還款方式，從而確定每期償還金額，與購房者的經濟承受能力進行比較，以此選擇一種最合適的還款方案。現實生活中應用較多的兩種購房還款方式是：等額本息還款法和等額本金還款法。等額本息還款指在每一期期末以相等的金額歸還貸款的本金和利息，等額本金還款是在還款期限內按期等額歸還貸款的本金，同時支付當期未歸還的本金所產生的利息。

等額本息還款法下每期償還金額的計算模型為：

等額本金還款法下每期償還金額的計算模型為：

每期等額還款額=貸款本金÷貸款期數+（本金-已歸還本金累計額）×期利率。

二 購房規劃模型設計

1.購房支付能力模型設計

案例：王先生年收入為10萬元，預計的收入增長率為3%。目前資產15萬元，儲蓄首付款與負擔房貸的上限為40%，打算5年后購房，投資報酬率10%，貸款年限20年，貸款利率為6%。

第一，建立基本數據模板。如圖1所示，在Excel工作薄的sheet1工作表的A1∶D9的區域建立基本數據模板，在模板中輸入模型的基本數據。

圖1 購房支付能力模型基本數據模板

圖1中的基本數據主要根據上述案例進行錄入。為便于數據操作，在預計收入增長率、收入中負擔收付和房貸的比例上限、投資報酬率和貸款利率的位置建立滾動條控制項，在距離購房年數和貸款年限的位置建立微調按鈕控制項。以預計收入增長率滾動條控制項的制作為例，制作過程為：假定增長率范圍為0～100%，先在“C3”單元格畫出一個滾動條，然后通過右鍵點擊滾動條，出現設置控件格式對話框，將對話框中的當前值設為3、最小值設為0、最大值設為100、步長設為1、步頁長設為10，單元格鏈接設為A3，再選擇C3單元，輸入公式“=A3/100”，最后將C3單元格式變為百分比格式。

第二，建立計算與分析結果模板。如圖2所示，在Excel工作薄的sheet1工作表的A11∶B14區域建立計算與分析結果模板。

圖2 購房支付能力模型計算與分析結果模板

相關公式輸入如下：（1）可負擔首付款。在B12單元格輸入計算公式：=C2*C4*FV（C7，C6，-1）+C5*（1+C7）^C6，其中，FV（C7，C6，-1）是年金終值函數。計算結果顯示，本例中王先生可負擔的首付款最多為48.58萬。（2）可負擔房貸總額。在B13單元格輸入計算公式：=C2*（1+C3）^C6*C4*PV（C9，C8，-1），其中，PV（C9，C8，-1）為年金現值函數，其完整表達式是PV（rate，nper，pmt，type），參數意義同年金終值函數。計算結果顯示，本例中王先生可負擔的房貸總額最多為53.19萬。（3）可負擔房屋總價。可負擔房屋總價等于可負擔首付款加上可負擔房貸總額，在B14單元格輸入計算公式：=B12+B13。計算結果顯示，本例中王先生可負擔的房貸總價最多為101.77萬。

2.購房籌資模型設計

案例：接上例，王先生向銀行貸款53.19萬，貸款利率為6%，貸款年限20年，銀行提供了兩種還款方式：一種是按月等額本金還款法，一種是按月等額本息還款法。

第一，建立基本數據模板。如圖3所示，在Excel工作薄的sheet2工作表的A1∶D6的區域建立基本數據模板，在模板中輸入模型的基本數據。

圖3 購房籌資模型基本數據模板

圖3中的基本數據主要根據上述案例進行錄入。其中，滾動條控制項和微調按鈕控制項的制作方法前面已經涉及，這里不再贅述。本例中，銀行要求按月還款，因此每年還款次數為12次。總還款期數等于貸款年限乘以每年還款次數，在單元格C6輸入計算公式為：=C4 *C5，本例總還款期數為240期。

第二，建立計算與分析結果模板。如圖4所示，在Excel工作薄的sheet2工作表的A8∶K14區域建立計算與分析結果模板。

圖4 購房籌資模型計算與分析結果模板

相關公式輸入如下：

等額本金還款法：每月等額償還本金等于貸款本金除以總還款期數，在B10單元格內輸入計算公式為=C2/C6，計算結果顯示每月等額償還本金為2216.25元。

數字1～240：本例中表示第1個月、第2個月、第3個月……直至第240個月。由于篇幅所限，圖4中只顯示了部分月份。

月還款額：在B12單元格內輸入公式為= $B $10+（ $C $2- $B $10*（B11-1））* $C $3/12，然后用填充柄進行復制，將公式一直復制到第240個月。$是絕對引用符號，當因插入、復制等原因引起行、列地址的變化，公式中的絕對引用不會隨公式的地址變化而變化。結果顯示，第1個月還款額為4875.75元、第2個月還款額為4864.67元、第3個月還款額為4853.59元……第239個月還款額為2238.41元、第240個月還款額為2227.33元。

等額本息還款法：等額本息還款法利用了已知年金現值求年金的計算公式，在B14單元格輸入計算公式：=C2*（C3/C5）*（1+C3/C5）^C6/（（1+C3/C5）-1），計算結果顯示本例中每月還款額為3810.70元。

三 結論

基于Excel的購房規劃教學模型具有較強的通用性，當基本數據模板發生變化時，計算與分析結果模板的數據也會隨之發生變化，為教師分析購房規劃相關計算要素對最終結果的影響提供了非常便捷的分析方法，同時，也解決了購房規劃計算工作量大、計算過程煩瑣的難題，使得學生對購房規劃的學習更感興趣，也加深了對相關知識的理解。但該模型也存在著不足之處，如只考慮了等額本息還款法和等額本金還款法，未考慮其他還款方式；模型主要考慮了財務方面的因素，沒有考慮非財務因素的影響，如房屋的屬性、購房者的心理感受等。針對上述問題，筆者今后將繼續展開研究，進一步修正和完善本文構建的模型，使其更具有教學和實用價值。

參考文獻

[1]孫黎.個人理財實務[M].北京：中國人民大學出版社，2012

[2]廖旗平.個人理財[M].北京：高等教育出版社，2009

〔責任編輯：林勁〕