【作 者】
韩建丽
【作者单位】
（枣庄学院经济与管理学院，山东枣庄 277160）
【摘 要】
【摘要】 本文构建了一个分期还款的Excel模型，该模型设计方法简单，通用性强，界面简洁美观，易于理解，能够根据还款期数的多少自动调整表格，使用非常简便，而且结合方案管理器工具可以轻松实现多方案对比。
【关键词】 分期还款；等额本息；等额本金；方案管理器一、建立分期还款模型要使用的Excel函数
本文建立分期还款模型的方法比较简单，只需要用到以下几个Excel函数：
1. 条件函数IF（）。语法：IF（logical_test，value_if_true，value_if_false）。IF（）函数首先执行真假值判断，当第一个参数逻辑条件（logical_test）的值为真时，返回第二个参数，为假时返回第三个参数。使用该函数是为了确保模型的通用性，以便当还款期数发生改变时，分期还款明细表能够自动调整期数的多少，需要几期，显示几期。
2. 年金函数PMT（）、年金中的本金函数PPMT（）、年金中的利息函数IPMT（）。语法：PMT（rate，nper，pv，fv，type）；PPMT（rate，per，nper，pv，fv，type）；IPMT（rate，per，nper，pv，fv，type）。
PMT（）函数、PPMT（）函数、IPMT（）函数能够分别计算出相应参数给定条件下的年金值、年金中的本金值和利息值。这几个函数中，参数rate为每期利率、nper为总期数、pv为现值、fv为终值、type为年金类型、per为要计算的本金或利息所对应的期数。在分期还款模型中，pv为贷款总额或分期付款原值；没有终值，所以fv省略；假设每期期末还款（即为普通年金），所以type也省略。3. 日期与时间函数DATE（）、YEAR（）、MONTH（）、DAY（）。函数DATE（year，month，day），返回这三个参数对应的日期；函数YEAR（serial_number），返回参数的年份值；函数MONTH（serial_number），返回参数的月份值；函数DAY（serial_number），返回一个月中的第几天的数值。
日期与时间函数的使用，是为了自动生成分期还款明细表中的还款日期。
二、分期还款模型设计
1. 原始数据输入区域设计。首先，合并B2：D2单元格区域，输入文本“原始数据输入区域”，并添加底色；然后，在C3：C7单元格区域依次输入文本“贷款总额（元）”、“贷款年利率”、“贷款期限（年）”、“还款间隔（月）”、“贷款日期”；其次，根据实际情况在D3：D7区域输入已知数据。这里假设（例1）李先生要贷款6万元购车，已知贷款年利率为8%，期限2年，每月还款一次，贷款日期为2015年2月22日。则在该区域依次输入60000，8%，2，1，2015-2-22。最后，设计边框，并取消整张工作表的网格线。
2. 计算结果输出区域设计。计算结果输出区域包括分期还款汇总表和分期还款明细表（见图2，图中隐藏了部分单元格）。常见的分期还款方式有等额本金还款法和等额本息还款法两种，这两张表对这两种方法分别予以列示。具体设计步骤如下：
（1）按照图2所示表格样式输入标题和表体固定项目（即文字部分），添加分期还款汇总表边框和分期还款明细表表头部分边框。
（2）计算并显示还款期数在汇总表中计算还款总期数：在D12和F12单元格中分别输入公式：=D5∗12/D6。
在明细表中显示还款期数：在B21单元格中输入1，在B22单元格中输入公式：=IF（B21<$D$12，B21+1，""）。该公式的含义是：先测试B21单元格中的数值是否小于还款总期数，如果是则返回B21+1的计算结果，如果否则返回空值。由于B21单元格中的数值1小于$D$12单元格中的数值24，逻辑测试结果为是，因此B22单元格中显示的结果为2。后文中很多类似的公式，其具体含义不再赘述。
将B22单元格中的公式向下复制直到足够的行数。具体的复制行数读者可根据需要自行选择。本模型中向下复制了1 200行，直至单元格B1220，这能够满足总还款期数多达1 200期的还款明细的显示。
（3）分期还款明细表中还款日期的显示。还款日期的显示需要使用日期与时间函数。在C21单元格中输入公式：=IF（B21<=$D$12，DATE（YEAR（D7），MONTH（D7）+$D$6，DAY（D7）），""）。在C22单元格中输入公式：=IF（B22<=$D$12，DATE（YEAR（C21），MONTH（C21）+$D$6，DAY（C21）），""），并向下复制直至C1220单元格。
（4）分期还款明细表中等额本息还款法下相关项目的计算。等额本息还款法，即每期偿还的金额（本息和）相同，构成一个年金。因此，每期还款额的计算，需要使用年金函数。在F21单元格中输入公式：=IF（B21<=$D$12，PMT（$D$4/12∗$D$6，$D$12，-$D$3），""），并向下复制直至F1220单元格。
每期支付利息的计算，需要使用年金中的利息函数。在D21单元格中输入公式：=IF（B21<=$D$12，IPMT（$D$4/12∗$D$6，B21，$D$12，-$D$3），""），并向下复制直至D1220单元格。
每期偿还本金的计算，需要使用年金中的本金函数。在E21单元格中输入公式：=IF（B21<=$D$12，PPMT（$D$4/12∗$D$6，B21，$D$12，-$D$3），""），并向下复制直至E1220单元格。
每期末剩余本金的计算，用期初剩余本金（等于上期末剩余本金）减去本期偿还本金即可。在G21单元格中输入公式：=IF（B21<=$D$12，D3-E21，""），在G22单元格中输入公式：=IF（B22<=$D$12，G21-E22，""），然后向下复制直至G1220单元格。
（5）分期还款明细表中等额本金还款法下相关项目的计算。等额本金还款法，每期偿还的本金相同，可先行计算，用贷款总额或分期付款原始金额除以还款总期数即可。在I21单元格中输入公式：=IF（B21<=$D$12，$D$3/$D$12，""），然后向下复制直至I1220单元格。每期末剩余本金的计算，同样用期初剩余本金（等于上期末剩余本金）减去本期偿还本金即可。在K21单元格中输入公式：=IF（B21<=$D$12，D3-I21，""），在K22单元格中输入公式：=IF（B22<=$D$12，K21-I22，""），然后向下复制直至K1220单元格。
每期支付利息的计算，需要用期初剩余本金（等于上期末剩余本金）乘以每期利率。在H21单元格中输入公式：=IF（B21<=$D$12，D3∗$D$4/12∗$D$6，""），在H22单元格中输入公式：=IF（B22<=$D$12，K21∗$D$4/12∗$D$6，""），然后向下复制直至H1220单元格。
每期还款额的计算，用每期支付的利息加上每期偿还的本金即可。在J21单元格中输入公式：=IF（B21<=$D$12，H21+I21，""），然后向下复制直至J1220单元格。
（6）分期还款汇总表中其他项目的计算。等额本息还款法下每期还款额的计算，在D13单元格中输入公式：=F21或者=PMT（$D$4/12∗$D$6，$D$12，-$D$3）；利息总额的计算，在D14单元格中输入公式：=SUM（D21:D1220）；本息总额的计算，在D15单元格中输入公式：=D3+D14。
等额本金还款法下每期偿还本金的计算，在F13单元格中输入公式：=D3/D12；利息总额的计算，在F14单元格中输入公式：=SUM（H21:H1220）；本息总额的计算，在F15单元格中输入公式：=D3+F14；第1期还款额的计算，在F16单元格中输入公式：=J21。
（7）分期还款明细表表体部分边框的显示。为了分期还款明细表显示界面的美观，表体部分的边框应能根据还款期数的多少自动调整。以Excel2003为例，设置方法如下：选中B21：K1220单元格区域，选择【格式】菜单中的【条件格式】命令，打开【条件格式】对话框，【条件1（1）】依次设置为：单元格数值、不等于、=""，然后单击右下角的【格式】按钮，打开【单元格格式】对话框，在【边框】选项卡下设置所需要的边框，单击【确定】按钮，返回如图3所示的界面，再单击【确定】按钮完成设置。
至此，分期还款模型设计完毕。该模型通用性极强，几乎可以解决任何涉及等额本息还款法或等额本金还款法的分期还款问题，而且该模型的使用非常简单，只需要根据实际情况将D3：D7区域的数据进行替换即可自动生成计算结果。当多个方案需要进行对比分析时，则不必逐次替换，可以结合Excel的方案管理器工具。
三、多方案对比时分期还款模型的使用
例2：张先生拟贷款购房，有3个方案可供选择：①贷款30万元，期限5年，利率5.75%，每月还款一次；②贷款30万元，期限10年，利率5.90%，每月还款一次；③贷款40万元，期限10年，利率5.90%，每月还款一次。试对这三个方案进行对比。仍以Excel 2003为例说明操作步骤。
1. 打开方案管理器。选择【工具】菜单中的【方案】命令，打开【方案管理器】对话框。
2. 添加方案。单击右侧的【添加】按钮，打开【添加方案】对话框，“方案名”设置为“方案一”，“可变单元格”设置为$D$3：$D$6，单击【确定】按钮，在打开的【方案变量值】对话框中依次输入300 000、5.75%、5、1，然后单击【确定】按钮，返回【方案管理器】对话框。用同样的方法添加方案二、方案三。
3. 建立方案总结报告。在【方案管理器】对话框中，单击【摘要】按钮，打开【方案摘要】对话框，在“结果单元格”中选择$D$12:$D$16，$F$13:$F$16区域，然后单击【确定】按钮，Excel会自动在本工作簿中生成一张名为“方案摘要”的工作表（如图4所示）。为了使显示结果易于理解，已事先使用【插入】菜单下的【名称】命令下的【定义】子命令给本部分所引用单元格插入了名字。
“方案摘要”工作表中，三个方案的信息一目了然，通过对比这三个方案的还款期数、每期还款额、利息总额等信息，张先生可以找到最适合自己的贷款方案。
主要参考文献
陈国栋.分期付款还款明细的Excel模板设计［J］.财会月刊，2014（12）.
张瑞君.计算机财务管理——财务建模方法与技术（第三版）［M］.北京：中国人民大学出版社，2011.
