基于Excel的购房规划教学模型设计

开篇：润墨网以专业的文秘视角，为您筛选了一篇基于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