EXCEL数据管理与分析功能在购房贷款中的应用

摘 要 本文利用模拟运算表，方案管理器分别计算贷款额度、贷款年限及月利率分别改变时的月还款额，为用户购买商品房办理银行按揭提供参考。

关键词 数据管理与分析 贷款

中图分类号：TP31 文献标识码：A

Excel是微软公司开发的办公软件系列中的一款数据处理软件，它不仅可以用于输入和操纵数据，同时也为我们提供了许多数据管理与分析工具，有时我们在进行计算时常常需要假设某种情况，比如改变其中的一个条件，结果也要发生相应变化，同时我们又想对比这些情况，这时我们就可以使用excel提供的数据管理与分析工具，帮助我们模拟运行的可能结果，①如模拟运算表，方案管理器等。

下面我们就以购买商品房办理银行按揭为背景，利用“模拟运算表”和“方案管理器”来分别计算贷款额度、贷款年限及月利率分别改变时对月还款额的影响，为用户选择一种适合自己的贷款方案提供参考。

第一步：建立购房贷款计划表如图1所示，将所有月还款额对应的单元格设置为数值类型。

图1

第二步：利用PMT函数计算固定贷款额度，贷款年限，月利率下月还款额。

在E8单元格中输入公式：=PMT（C8，B8*12，A8），这里利用PMT函数来计算月还款额，PMT（rate，nper，pv，fv，type）是基于固定利率及等额分期付款方式下返回贷款的每期付款额。其中Rate代表贷款利率，Nper代表该项贷款的付款总次数，rate与nper要相匹配，譬如果rate为月利率，那么nper应为月数，Pv代表本金，Fv代表在最后一次付款后希望得到的现金余额，忽略表示0，Type值为0或省略表示付款时间是在期末，为1时表示在期初。

第三步：利用单变量模拟运算表计算固定贷款额度，贷款年限，不同月利率下月还款额的变化。

在B10单元格输入PMT公式计算月还款额，选择B9：F10区域，点击数据菜单下的模拟运算表，在输入引用行单元格输入$C$8，点击确定。使用单变量模拟运算表时有几个需要注意的地方。

（1）单元格公式位置：模拟表区域中可变条件在同一行，那么单元格公式在模拟表的左下方（如果可变条件在同一列，那么单元格公式在模拟表的右上方）；

（2）输入引用行（列）单元格选择：模拟表区域中可变条件在同一行，那么选择输入引用行单元格（如果可变条件在同一列，那么选择输入引用列单元格）；

（3）输入引用行（列）单元格中数据：根据模拟表区域中可变条件，在输入引用行（列）输入单元格公式中用到的对应的条件单元格。

第四步：利用双变量模拟运算表计算固定贷款额度，不同贷款年限，不同月利率下月还款额的变化情况

首先在A14单元格中输入公式=PMT（C8，B8*12，A8），计算某一固定贷款额度，贷款年限及贷款利率下月还款额，然后选中A14到F18这片连续的单元格，点击数据菜单下的模拟运算表，在输入引用行的单元格中填写$B$8，在输入引用列的单元格中输入$C$8，点击确定。使用双变量模拟运算表时有几个需要注意的地方。

（1）单元格公式位置：模拟表区域中左上角顶角的位置；

（2）输入引用行单元格中数据：根据单元格区域中第一行中的可变条件，在输入引用行的单元格中输入单元格公式中用到的对应的条件单元格；

（3）输入引用列单元格中数据：根据单元格区域中第一列中的可变条件，在输入引用列的单元格中输入单元格公式中用到的对应的条件单元格。

第五步：当贷款额度，贷款年限，月利率都发生改变的情况下可以使用方案管理器进行比较，方案是excel保存在工作表中并可以自动替换的一组值，使用方案管理器可以预测工作表模型的输出结果，在工作表中创建并保存了不同的数值组，通过切换到任意的新方案可以查看不同的结果。

首先选择任意选一种方案，计算其月还款额，在F21单元格中输入公式=PMT（E21，D21*12，C21），然后选择可变条件区域C21：E21，点击工具菜单栏下的方案，点击添加方案，弹出添加方案对话框，在对话框的方案名文本框中输入组合贷款，可变条件单元格自动选择C21：E21，点击确定，弹出方案变量值对话框，在对话框中的贷款金额文本框中填入数字500000，在贷款年限文本框中填入数字25，在月利率文本框中填入数字0.0038点击确定，组合贷款方案就完成了，按此方法添加公积金贷款方案，商业性贷款方案。公积金贷款方案贷款金额设置为600000，贷款年限设置为30，月利率设置为0.0034，商业性贷款方案贷款金额设置为400000，贷款年限设置为20，月利率设置为0.0042。最后选择不同方案，单击显示，可在当前可变单元格区域显示不同方案结果。单击摘要，可生成方案总结报告，其具体步骤是点击摘要，弹出方案摘要对话框，在对话框的结果单元格中填写=$F$21，结果单元格中填写的是使用了可变条件单元格中数据进行公式计算的结果单元格，点击确定可生成方案摘要。

注释

① 周洪林.EXCEL函数高级应用[J].福建电脑，2009（179）.