【作　　者】
陈国栋（博士）

【作者单位】
（华北水利水电大学管理与经济学院 郑州 450046）

【摘　　要】

【摘要】 本文介绍了分期付款的还款明细的计算方法，然后详细介绍了计算分期付款还款明细的Excel模板的步骤。该模板将等额本息和等额本金两种还款方式都涵盖了，同时非常简洁和美观。
【关键词】 分期付款   等额本息   等额本金

分期付款已经成为人们的日常消费方式，但当他们想核对自己的分期付款还款明细是否正确时，因为不明白每月还款多少是怎么计算出来的，所以也无法判断自己的还款明细是否有误。对消费者来说，他们不仅想明白分期付款的原理，更需要一个简单实用的计算分期付款还款明细的工具。因此，笔者认为有必要将分期付款还款明细的计算方法，特别是如何设计分期付款还款明细的Excel模板介绍给广大消费者。
一、分期付款的计算方法
分期付款的还款方式有等额本息和等额本金两种。等额本息是在还款期内，每月偿还同等数额的贷款（包括本金和利息）。等额本金是在还款期内把贷款数总额等分，每月偿还同等数额的本金和剩余贷款在该月所产生的利息。采用等额本金时，每个月的还款额是不一样的，这是因为每月的还款本金额固定，而利息越来越少，所以随时间的推移每月还款数也越来越少。
1. 等额本息还款计算方法。假设某人贷款总额为D元，贷款分n个月偿还，每个月的月末还款P元，月利率为R。则第一个月的月末本金和利息总计为：D（1+R）。第一个月月末还款后剩余款项为：b1=D（1+R）-P，b1也就是第二月月初的贷款总额。所以第二个月月末还款是在［D（1+R）-P］（1+R）的基础上还P元，即第二个月月末还款后剩余款项为：b2=［D（1+R）-P］（1+R）-P。如此类推，到第n个月月末还P元时全部还完贷款。第n个月月末还P元时剩余款项为bn，
bn的值为0。bn的数学表达式为：bn=D（1+R）n-P［（1+R）n-1+（1+R）n-2+…+1］=0。即D（1+R）n=P［（1+R）n-1］/R，从而得到等额本息还款，并且每月月末还款每月偿还额为：［DR（1+R）n］/［（1+R）n-1］，所支付的利息总额为：［nDR（1+R）n］/［（1+R）n-1］-D。所以，等额本息还款每月还款额计算公式为：［贷款本金×月利率×（1+月利率）还款月数］÷［（1+月利率）还款月数-1］。
2. 等额本金还款计算方法。等额本金贷款每月还款额由两部分构成，一是每月偿还同等数额的本金，二是剩余贷款在该月所产生的利息。所以等额本金还款每月还款额计算公式为：（贷款本金÷还款月数）+（本金-已归还本金累计额）×每月利率。
二、分期付款还款明细的Excel模板设计
无论是采用等额本息还是等额本金还款方式，计算量都很大。为了方便计算分期付款还款明细，笔者设计了Excel模板来计算分期付款还款明细。具体步骤如下：
1. 在区域C4∶C8中输入分期付款的已知数据，C8单元格使用了Excel的数据有效性，可以让用户选择贷款方式是等额本息还是等额本金。详见表1。

2. 在区域C13∶C15中显示分期付款的月付金额（等额本息时）、利息总额和本息合计数据，详见表2。

3. 设计分期付款还款明细输出区域的表头标题，同时给区域A19∶E20每个单元格添加黑色边框，详见表3。

4. 按ALT+F11组合键，打开Visual Baisc编辑器，点击插入菜单下的模块，插入一个新模块。然后将下面的VBA代码复制到插入的模块中。
Sub 计算分期付款明细（）
    Dim LastRow
    Application.ScreenUpdating=False
    ActiveSheet.Unprotect
    Range（"C4：C8"）.Locked=False
    Dim temp As String
    Range（"C13"）.Formula="=IFERROR（IF（C8=""等额本息""，PMT（C5/12，C6，-C4），""""），""""）"
    Range（"C14"）.Formula="=IFERROR（SUM（每月还的利息），""""）"
    Range（"C15"）.Formula="=IFERROR（C14+C4，""""）"
    Select Case Range（"C8"）.Value
        Case "等额本息"
            Range（"A20"）.Formula=1
            Range（"B20"）.Formula="=$C$7"
            Range（"C20"）.Formula="=$C$4∗C5/12"
            Range（"D20"）.Formula="=E20-C20"
            Range（"E20"）.Formula="=$C$13"
            Range（"F20"）.Formula="=$C$4-D20"
            Range（"A21"）.Formula="=A20+1"
            Range（"B21"）.Formula="=DATE（YEAR（B20），MONTH（B20）+1，DAY（B20））"
            Range（"C21"）.Formula="=F20∗$C$5/12"
            Range（"D21"）.Formula="=E21-C21"
            Range（"E21"）.Formula="=$C$13"
            Range（"F21"）.Formula="=F20-D21"
        Case "等额本金"
            Range（"A20"）.Formula=1
            Range（"B20"）.Formula="=$C$7"
            Range（"C20"）.Formula="=C4∗C5/12"
            Range（"D20"）.Formula="=$C$4/$C$6"
            Range（"E20"）.Formula="=C20+D20"
            Range（"F20"）.Formula="=C4-D20"
            Range（"A21"）.Formula="=A20+1"
            Range（"B21"）.Formula="=DATE（YEAR（B20），MONTH（B20）+1，DAY（B20））"
            Range（"C21"）.Formula="=F20∗$C$5/12"
            Range（"D21"）.Formula="=$D$20"
            Range（"E21"）.Formula="=C21+D21"
            Range（"F21"）.Formula="=F20-D21"
    End Select
    If Not Application.WorksheetFunction.Or（Range（"C8"）="等额本息"， Range（"C8"）="等额本金"） Then
        MsgBox "请重新检查输入的数据！"        Exit Sub
    End If
    If Range（"C4"） > 0 And Range（"C5"） > 0 And Range（"C6"） > 0 And Range（"C7"） > 0 Then
        LastRow=19 + Cells（6， 3）
        Rows（"22：1048576"）.Delete
        Rows（"18：1048576"）.EntireRow.Hidden=False
        Range（"A21：F21"）.AutoFill Destination：=Range（"A21：F" & LastRow）， Type：=xlFillDefault
        Application.ScreenUpdating=True
        Range（"C4：C8"）.Locked=True
        Range（"C4：C8"）.Locked=True
        Application.ActiveSheet.Columns（"A：F"）.AutoFit
        ActiveSheet.Protect DrawingObjects：=True， Contents：=True， Scenarios：=True
    Else
        MsgBox "请重新检查输入的数据！"
    End If
End Sub
Sub 输入数据（）
    Application.ScreenUpdating=False
    ActiveSheet.Unprotect
    Range（"C4：C8"）.Locked=False
    Range（"C4：C8"）.ClearContents
    Range（"C13：C15"）.ClearContents
    Rows（"18：1048576"）.EntireRow.Hidden=True
    ActiveSheet. Protect DrawingObjects：=True， Contents：=True， Scenarios：=True
    Application.ScreenUpdating=True
End Sub
5. 选中B10单元格，插入开发工具选项卡控件组中的按钮（窗体控件），然后选中该按钮将其文字改为输入数据，指定宏为输入数据。类似的，选中B11单元格，插入一个按钮（窗体控件），然后选中该按钮将其文字改为开始计算，指定宏为计算分期付款明细，详见图1。
6. 点击输入数据按钮，输入表1中的数据，然后点击“开始计算”按钮，详见图2。
7. 点击输入数据按钮，清除基础数据框中的实例数据，然后另存为启用宏的模板即可。下次需要使用该模板时，在“文件—新建—我的模板”中找到这个模板，然后新建一个工作簿即可。
三、小结
本文详细介绍了分期付款还款明细的Excel模板设计过程，将分期付款还款明细的计算过程简化为只需两次点击鼠标左键。第一次点击“输入数据”按钮，输入分期付款参数，第一次点击“开始计算”按钮，计算分期付款还款明细。该模板的创新在于，一个模板将等额本息和等额本金两种还款方式都涵盖了，具有很强的通用性和实用性，可供大家参考。
【注】 本文研究受华北水利水电大学“管理科学与工程”省级重点学科建设经费资助。
主要参考文献
1. 胡耀胜，杜汉玲.分期付款的数学原理及其应用.统计与决策，2004；11
2. John Walkenbanch. Excel 2003高级VBA编程宝典.北京：电子工业出版社，2006