excel制作按揭贷款分析表
假设购买一套住房，需要向银行贷款约15～30万元，分10～30年时间还贷，在不同的贷款利率情况下，要计算等额还款方式下每月所需按揭的费用，方法如下：
1）选中A1单元格，单击[数据]—［有效性］，打开“数据有效性对话框”，选中“设置”选项卡。在“允许”下拉列表中选择“序列”，在“来源”文本框中输入各种贷款额度，如“15万元，20万元，25万元，30万元”，各个选项之间用半角逗号间隔，然后然后单击“确定”按钮关闭对话框。接下来在A1单元格数据有效性的下拉列表中选择一个贷款额度，例如“15万元”。2）在C3：C7单元格内输入15～30年间贷款年限，在D2：L2单元格内输入各种贷款利率，例如本例中使用了从2002年至今历年来的公积金贷款年率，添加行列标题美化格式后的结果如下图所示；
3）选中C2单元格，输入公式：=PMT(A3/12,A4*12,-LEFT(A1,2)*10000，公式结果显示为错误值“#DIV/0!”，此结果没有实际意义，并不影响模拟运算表的继续操作；
4) 选中单元格区域C2：L7，单击菜单[数据]—［模拟运算表］打开“模拟运算表”对话框，在“输入引用行的单元格”文本框中输入“$A$3”，在“输入引用列的单元格”文本框中输入“$A$4”，如下图所示：
5）单击“确定”按钮完成操作，调整数据显示格式后的结果如下图所示：
上图中详细地显示了贷款15万元的情形下，各种贷款年率和贷款时间所对应的按月等额还款方式的具体按揭金额。在A1单元格的下拉列表中选择不同的贷款金额选项，模拟运算表中就会自动地变化显示相应的月度按揭金额。
公式解析：=PMT(A3/12,A4*12,-LEFT(A1,2)*10000)。PMT函数是一个财务函数，用于计算固定利率等额分期付款方式下的每期付款额。该函数的语法为PMT(rate,nper,pv,fv,type)，其中rate指的是固定利率，利率的时间单位必须与贷款期限的单位以及最后计算的还款时间频率单位相一致，在本例中D2：L2区域的利率单位为“年”，最后计算目标为按月还款额，所以需要将年率除以12得到月率。rate参数指向单元格A3，这个单元格没有实际意义，只作为引用位置。nper指的是付款的总期数，也就是贷款的期限，在本例中需要将C3：C7中相应的年份乘以12得到月份数，以期与rate参数的单位保持一致。Nper参数指向单元格A4，与上面的A3单元格一样，此单元格也没有实际意义，只用做模拟运算表参数的引用位置。pv指现值，在贷款模型中即指贷款的总额，本例中需要从A1单元格的字符串中取出相应的数值同时乘上单位“万”，公式中的“LEFT(A1,2)*10000”部分即是起此作用的。fv指终值，在贷款模型中终值为零，可省略。type指付款方式，为1时代表期初付款，为0或省略时代表期末付款，在贷款模型中一般均为期末付款方式。
如果想要了解全部按揭期结束总的还款金额，可以直接在C2单元格内将公式改为：
=PMT(A3/12,A4*12,-LEFT(A1,2)*10000)*A4*12
显示结果如下图所示：
如果想要进一步了解全部还款金额中所包含的利息部分，可以将公式修改为：
=PMT(A3/12,A4*12,-LEFT(A1,2)*10000)*A4*12-LEFT(A1,2)*1000
显示结果如下图所示：
