制作按揭贷款分析表
模拟运算表应用的范围比较广泛，除了前面所介绍的投资收益预测分析以外，与此类似的，利用模拟运算表制作按揭贷款分析表也是比较常见的一种应用。
假设购买一套住房，需要向银行贷款约15~30万元，分10到30年时间还贷，在不同的贷款利率情况下，要计算等额还款方式下每月所需按揭的费用，可依照如下步骤操作：步骤1 选中A1单元格，单击菜单“数据”→“有效性”，打开“数据有效性”对话框，选中“设置”选项卡。
在“允许”下拉列表中选择“序列”，并在“来源”编辑框中输入各种贷款额度，如“15万元,20万元,25万元,30万元”，各项之间用半角逗号间隔，如图1-1所示，单击“确定”按钮关闭对话框。
在A1单元格数据有效性的下拉列表中选择一个贷款额度，例如“15万元”。
图1-1 设置“数据有效性”
步骤2 在C3:C7单元格内输入15年~30年间贷款年限，在D2:L2单元格内输入各种贷款利率，例如本例中使用了从2002年至今历年来的公积金贷款年率，添加行列标题美化格式后显示如图1-2所示：
图1-2 设置各项计算参数
步骤3 选中C2单元格，输入公式：
=PMT(A3/12,A4*12,-LEFT(A1,2)*10000)
公式结果显示为错误值“#DIV/0!”，此结果没有实际意义，并不影响模拟运算表的继续操作。
步骤4 选中单元格区域C2:L7，单击菜单“数据”→“模拟运算表”，打开“模拟运算表”对话框，在“输入引用行的单元格”编辑框中输入“$A$3”，在“输入引用列的单元格”编辑框中输入“$A$4”，如图1-3所示：
图1-3 设置行列引用单元格
步骤5 单击“确定”按钮完成操作，调整数据显示格式后结果如图1-4所示：
图1-4 完成后的按揭贷款分析表
图1-4中详细显示了贷款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或省略时代表期末付款，在贷款模型中一般均为期末付款方式。
关于PMT函数的其他详细内容可参见Excel帮助。
如果想要了解全部按揭期结束总的还款金额，可以直接在C2单元格内将公式改为：
=PMT(A3/12,A4*12,-LEFT(A1,2)*10000)*A4*12
显示结果如图1-5所示：
图1-5 显示按揭到期付款总额
如果想要进一步了解全部还款金额中所包含的利息部分，可将公式修改为：
=PMT(A3/12,A4*12,-LEFT(A1,2)*10000)*A4*12-LEFT(A1,2)*10000
显示结果如图1-6所示：
图1-6 显示按揭到期利息总额
制作贷款还贷明细表
除了了解不同贷款年限、不同贷款利率下的还贷金额情况外，还有不少贷款人对每月按揭款中的本金和利息的分摊情况感兴趣，这同样可以通过模拟运算表来简单的实现。
以2007年9月15日起施行的五年期以下公积金贷款年率4.77%为例，要制作每月按揭还款的明细详表，操作步骤如下：
步骤1 在B1单元格内输入贷款年率4.77%。
步骤2 选中B2单元格，按<Ctrl+1>组合键打开“单元格格式”对话框，选择“数字”选项卡，在“分类”列表框内选择“自定义”，并在“类型”文本框内输入“#万元”，如图2-1所示，单击“确定”按钮关闭对话框。
图2-1 设置单元格格式
步骤3 选中B2单元格，单击菜单“数据”→“有效性”，打开“数据有效性”对话框，选中“设置”选项卡。
在“允许”下拉列表中选择“序列”，并在“来源”编辑框中输入各种贷款额度，如“15,20,25,30”，各项之间用半角逗号间隔，如图2-2所示。
单击“确定”按钮关闭对话框，然后在数据有效性下拉列表中选择一个金额，例如30。
图2-2 设置数据有效性
步骤4 选中B3单元格，参照步骤2设置单元格格式为“#年”，然后参照步骤3设置数据有效性序列为“1,2,3,4,5”。
完成后可在A1:A3单元格添加标题增强表格可读性，显示如图2-3所示：
图2-3 设置相关运算参数
注意：步骤2~步骤4之中设置数据有效性的相关操作是为了提供更多可选参数，如果用户能够确定准确的相应贷款信息，则可以省略这些步骤。
与此情况类似，这其中使用单元格格式设置的相关操作是为了增强表格数据的可读性，用户也可以有选择的省略操作。
步骤5 选中D3单元格，在单元格内输入公式：
=IF(ROW(A1)>$B$3*12,"",ROW(A1))
然后复制公式向下填充至D62单元格。
此列数据为模拟运算表的变量参数引用列，可将D3:D62区域的单元格格式设置为“第#个月”增强数据的可读性。
步骤6 在E2单元格内输入公式“=PPMT(B1/12,B4,B3*12,-B2*10000)”，在F2单元格内输入公式“=IPMT(B1/12,B4,B3*12,-B2*10000)”
步骤7 选中单元格区域D2:F62，单击菜单“数据”→“模拟运算表”，打开“模拟运算表”对话框，在“输入引用列的单元格”编辑框中输入“$B$4”，如图2-4所示：
图2-4 创建单变量模拟运算表
步骤8 单击“确定”按钮关闭对话框，完成模拟运算表的创建。
在结果区域的末尾部分会显示“#VALUE!”的错误值，影响表格的外观，可通过设置单元格格式的方式进行隐藏：选中单元格区域E3:F62，参考步骤2的操作自定义单元格格式为“[黑色]#.00”，然后设置字体颜色与单元格底纹颜色相同即可。
同理，E2和F2单元格内的错误值“#NUM!”也可以通过同样方法进行隐藏美化。
在进行格式美化并添加表头标题后，表格显示如图2-5所示：
图2-5 完善后的还贷明细表
当用户在B2单元格或B3单元格的数据有效性下拉列表中选择其他的贷款金额和贷款年限时，无需修改模拟运算表，自动显示对应的结果。
例如在B2单元格选择15万元，在B3单元格选择1年，显示如图2-6所示：
图2-6 修改初始参数后自动显示相应结果
公式解析：
D3单元格公式：=IF(ROW(A1)>$B$3*12,"",ROW(A1))
ROW函数语法为ROW(reference)，返回引用的行号，“ROW(A1)>$B$3*12”部分即是将单元格所在行的行数与B3单元格年限所对应的月份数进行对比。
此公式可以根据B3单元格所选择的贷款年限，在D列中的模拟运算表参数区域中自动生成有效的月份数，超出年限的无效月份数则自动显示空白单元格。
模拟运算表的运算公式：
=PPMT(B1/12,B4,B3*12,-B2*10000)
=IPMT(B1/12,B4,B3*12,-B2*10000)
PPMT函数和IPMT函数均为财务函数，分别求取基于固定利率及等额分期付款方式下、给定期数的本金偿还额和利息偿还额。
两个函数的语法分别为：
PPMT(rate,per,nper,pv,fv,type)
IPMT(rate,per,nper,pv,fv,type)
其中参数per表示用于计算本息的具体贷款期数，其他参数含义可参考前文，此处不再赘述。
本篇文章节选自《Excel数据处理与分析实战技巧精粹》ISBN：9787115182326人民邮电出版社。
