基于Excel的購(gòu)房規(guī)劃教學(xué)模型設(shè)計(jì)

【摘 要】本文利用Excel豐富的函數(shù)與強(qiáng)大的計(jì)算功能，建立了購(gòu)房規(guī)劃教學(xué)模型。該模型解決了購(gòu)房規(guī)劃教學(xué)中計(jì)算量大、計(jì)算過(guò)程煩瑣的問(wèn)題，提高了學(xué)生的學(xué)習(xí)興趣和利用Excel軟件解決復(fù)雜問(wèn)題的能力。

【關(guān)鍵詞】Excel 購(gòu)房規(guī)劃 教學(xué)模型

【中圖分類號(hào)】G642 【文獻(xiàn)標(biāo)識(shí)碼】A 【文章編號(hào)】1674-4810（2015）24-0075-02

在個(gè)人理財(cái)規(guī)劃的教學(xué)中，購(gòu)房規(guī)劃由于計(jì)算公式較為復(fù)雜，計(jì)算過(guò)程較為煩瑣，導(dǎo)致學(xué)生望而生畏，同時(shí)也不利于教師清晰闡述各計(jì)算要素之間的關(guān)系，從而影響了教學(xué)質(zhì)量。因此，需要建立一個(gè)計(jì)算便捷且可擴(kuò)展的計(jì)算模型來(lái)解決上述問(wèn)題，利用Excel豐富的函數(shù)與強(qiáng)大而靈活的計(jì)算功能可以建立便捷、實(shí)用的購(gòu)房規(guī)劃教學(xué)模型。

一 購(gòu)房規(guī)劃內(nèi)容及原理

1.購(gòu)房支付能力規(guī)劃

購(gòu)房支付能力規(guī)劃是指根據(jù)計(jì)劃購(gòu)房時(shí)的時(shí)點(diǎn)、房屋面積及購(gòu)房者的經(jīng)濟(jì)能力等，測(cè)算個(gè)人或家庭可負(fù)擔(dān)的房?jī)r(jià)。一種應(yīng)用較廣泛的核算方法是年收入概算法，計(jì)算模型如下：

可負(fù)擔(dān)首付款=目前年收入×收入中負(fù)擔(dān)首付和房貸的比率上限×年金終值系數(shù)（n=距離購(gòu)房年數(shù)，r=投資報(bào)酬率或市場(chǎng)利率）+目前凈資產(chǎn)×復(fù)利終值系數(shù)（n=距離購(gòu)房年數(shù)，r=投資報(bào)酬率或市場(chǎng)利率）。

可負(fù)擔(dān)房貸總額=目前年收入×復(fù)利終值系數(shù)（n=距離購(gòu)房年數(shù)，r=預(yù)計(jì)的收入增長(zhǎng)率）×收入中負(fù)擔(dān)首付和房貸的比率上限×年金現(xiàn)值系數(shù)（n=貸款年限，i=房貸利率）。

可負(fù)擔(dān)房屋總價(jià)=可負(fù)擔(dān)的首付款+可負(fù)擔(dān)房貸總額；可負(fù)擔(dān)房屋單價(jià)=可負(fù)擔(dān)房屋總價(jià)÷需求面積。

2.購(gòu)房籌資規(guī)劃

對(duì)于多數(shù)人來(lái)說(shuō)，購(gòu)房的開(kāi)支太大，不能一次性付清購(gòu)房款，需要向銀行貸款，從而涉及另外一個(gè)重要問(wèn)題——購(gòu)房籌資規(guī)劃。購(gòu)房籌資規(guī)劃的核心問(wèn)題是要確定貸款期限和還款方式，從而確定每期償還金額，與購(gòu)房者的經(jīng)濟(jì)承受能力進(jìn)行比較，以此選擇一種最合適的還款方案?，F(xiàn)實(shí)生活中應(yīng)用較多的兩種購(gòu)房還款方式是：等額本息還款法和等額本金還款法。等額本息還款指在每一期期末以相等的金額歸還貸款的本金和利息，等額本金還款是在還款期限內(nèi)按期等額歸還貸款的本金，同時(shí)支付當(dāng)期未歸還的本金所產(chǎn)生的利息。

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

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

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

二 購(gòu)房規(guī)劃模型設(shè)計(jì)

1.購(gòu)房支付能力模型設(shè)計(jì)

案例：王先生年收入為10萬(wàn)元，預(yù)計(jì)的收入增長(zhǎng)率為3%。目前資產(chǎn)15萬(wàn)元，儲(chǔ)蓄首付款與負(fù)擔(dān)房貸的上限為40%，打算5年后購(gòu)房，投資報(bào)酬率10%，貸款年限20年，貸款利率為6%。

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

圖1 購(gòu)房支付能力模型基本數(shù)據(jù)模板

圖1中的基本數(shù)據(jù)主要根據(jù)上述案例進(jìn)行錄入。為便于數(shù)據(jù)操作，在預(yù)計(jì)收入增長(zhǎng)率、收入中負(fù)擔(dān)收付和房貸的比例上限、投資報(bào)酬率和貸款利率的位置建立滾動(dòng)條控制項(xiàng)，在距離購(gòu)房年數(shù)和貸款年限的位置建立微調(diào)按鈕控制項(xiàng)。以預(yù)計(jì)收入增長(zhǎng)率滾動(dòng)條控制項(xiàng)的制作為例，制作過(guò)程為：假定增長(zhǎng)率范圍為0～100%，先在“C3”單元格畫(huà)出一個(gè)滾動(dòng)條，然后通過(guò)右鍵點(diǎn)擊滾動(dòng)條，出現(xiàn)設(shè)置控件格式對(duì)話框，將對(duì)話框中的當(dāng)前值設(shè)為3、最小值設(shè)為0、最大值設(shè)為100、步長(zhǎng)設(shè)為1、步頁(yè)長(zhǎng)設(shè)為10，單元格鏈接設(shè)為A3，再選擇C3單元，輸入公式“=A3/100”，最后將C3單元格式變?yōu)榘俜直雀袷健?/p>

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

圖2 購(gòu)房支付能力模型計(jì)算與分析結(jié)果模板

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

2.購(gòu)房籌資模型設(shè)計(jì)

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

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

圖3 購(gòu)房籌資模型基本數(shù)據(jù)模板

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

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

圖4 購(gòu)房籌資模型計(jì)算與分析結(jié)果模板

相關(guān)公式輸入如下：

等額本金還款法：每月等額償還本金等于貸款本金除以總還款期數(shù)，在B10單元格內(nèi)輸入計(jì)算公式為=C2/C6，計(jì)算結(jié)果顯示每月等額償還本金為2216.25元。

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

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

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

三 結(jié)論

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

參考文獻(xiàn)

[1]孫黎.個(gè)人理財(cái)實(shí)務(wù)[M].北京：中國(guó)人民大學(xué)出版社，2012

[2]廖旗平.個(gè)人理財(cái)[M].北京：高等教育出版社，2009

〔責(zé)任編輯：林勁〕