运用excel建立长期借款动态还款模型

郭少蓉，刘艳萍

摘要：Excel不仅具有多种函数库，其VBA更是为用户实现高级操作提供了便捷。本文以长期借款的还款模型为例给出运用VBA编程技术建立财务管理教学中动态模型的方法。

关键词：财务管理教学；VBA；动态模型

中图分类号：G642.4 文献标识码：A 文章编号：1674-9324（2012）07-0206-02

一、引言

随着计算机技术的广泛应用，在高校中如何利用计算机技术来提高财务管理课程的教学效果成为教师的关注点。Excel的计算功能与函数辅助工具，可以使一些复杂计算简单地加以实現。教学过程中经常需要临时修改或输入某些数据，运用Excel可以随时改变单元格的内容而不影响其计算过程，这是其他教学常用软件难以实现的，所以在财务管理教学课程上应注重对excel的使用。如何构造一个方便使用、节省不必要操作过程的动态模型来提高教学效果是本文研究的重点。下面以长期借款的还款模型为例说明如何运用VBA编程技术建立一个基于年限的动态变化模型。

二、长期借款还款模型的设计实例

（一）模型建立

构造一个长期借款还款模型来分析在不同年利率、贷款额、贷款期限及还款方式下每年应付利息、欠款总额、支付金额、尚欠款额的变化。构建模型如图1所示。

图1 动态还款模型

还款方式为四种：每年只付利息，本金最后一年年末一次偿还；每年年末等额还本金及当年全部利息；每年均匀偿还全部本利和；本息最后一年年末一次偿还。

图2 还款模型结果分析

该分析结果的表格会随着贷款期限或是还款方式的不同而动态的改变，克服了传统模型中建立的表格行数、列数的限制，即假如模型建立时只考虑了期限为5年的贷款，则当要分析其他期限的贷款时就须重新设计表格，而利用VBA编程可以实现只要改变还款方式则结果分析表格中的数据也会随之改变。

（二）主要的程序代码

（1）若单元格C6或C7的值改变则执行以下代码：

Private Sub Worksheet_Change（ByVal Target As Range）

On Error GoTo L

If Target = Range（“C6”）OrTarget=Range（“C7”） Then Call hkfs 调用不同还款方式的计算过程L：Call chsh重设表格：

End If

End Sub

（2）不同还款方式的计算

Sub hkfs（）

Range（“C12：Q15”）.FormulaR1C1=“”

Call c6“由于”每年只付利息，本金最后一年年末一次偿还“和本息最后一年年末一次偿还”两种还款方式最后一年的支付金额与尚欠款额与之前年份的计算不同，需要单独计算，所以要判断C6单元格中的年限。

Select Case Range（“C7”）.FormulaR1C1

Case“每年只付利息，本金最后一年年末一次偿还”

Range（“C12”）.FormulaR1C1=“=R5C3*（R4C3/100）”

Range（“C12”）.Select

Selection.AutoFillDestination：=Range（“C12：Q12”），Type：=xlFillDefault以上两句为选中编辑完的单元格后横向自动填充Q列，后文多长用到，为了节省繁琐的代码之后用“自动填充”

字样表示：Range（“C13”）.FormulaR1C1=“=R5C3+R12C3”

自动填充：Range（“C14”）.FormulaR1C1=“=R-[2]C”

自动填充：Range（“C15”）.FormulaR1C1=“=R5C3”

自动填充：Range（h14）.FormulaR1C1=“=R12C3+R5C3”；Range（h15）.FormulaR1C1=“0”

h14、h15为C6过程中的变量，代表最后一年的支付金额与尚欠款额所对应的单元格。

Case“每年末等额还本金和当年全部利息”

Range（“C12”）.FormulaR1C1=“=R5C3*（R4C3/100）”

Range（“D12”）.FormulaR1C1=“=R15C-[1]*（R4C3/100）”

Range（“D12”）.Select

自动填充：Range（“C13”）.FormulaR1C1=“=R5C3+R12C3”；Range（“D13”）.FormulaR1C1=“=R[2]C-[1]+R-[1]C”

自动填充：Range（“C14”）.FormulaR1C1=“=R5C3/R6C3+R12C”

自动填充：Range（“C15”）.FormulaR1C1=“=R-[2]C-R-[1]C”

自动填充：Case“每年均匀偿还全部本利和”；Range（“C12”）.FormulaR1C1 =“=R5C3*（R4C3/100）”

Range（“D12”）.FormulaR1C1=“=R[3]C-[1]*（R4C3/100）”

自动填充：Range（“C13”）.FormulaR1C1=“=R5C3+R12C3”；Range（“D13”）.FormulaR1C1=“=R[2]C-[1]+R-[1]C”

自动填充：Range（“C14”）.FormulaR1C1=“=-PMT（R-[10]C/100，R-[8]C，R-[9]C）”

Range（“C14”）.Select；Range（“D14”）.FormulaR1C1=“=RC-[1]”

自动填充：Range（“C15”）.FormulaR1C1=“=R-[2]C-R-[1]C”

自动填充：Case“本息最后一年年末一次偿还”；Range（“C12：Q12，C14：Q14”）.FormulaR1C1=“0”Range（“C13”）.FormulaR1C1=“=R5C3*（1+R4C3/100）”；Range（“D13”）.FormulaR1C1=“=RC-[1]*（1+R4C3/100）”

自动填充：Range（“C15”）.FormulaR1C1=“=R-[2]C”

自动填充：Range（h14）.FormulaR1C1=“=R-[1]C”；Range（h15）.FormulaR1C1=“0”；End Select；EndSub

（3）动态表格制作：

Sub chsh（）重设表格

Call刷新

Dim a As String

Select Case Range（“C6”）.FormulaR1C1

Case“2”

a=“E11：Q15”

Call yincang（a）

Case“3”

a=“F11：Q15”

Call yincang（a）

……中间部分省略，节省篇幅。

Case“14”

a=“Q11：Q15”

Call yincang（a）

End Select

End Sub

Sub 刷新（）刷新表格

Range（“E11：Q15”）.Select

Selection.NumberFormatLocal=“G/通用格式”

Range（“C12：Q15”）.Select

对选中区域进行表格绘制，这部分代碼可以用录制宏的方式来实现，在此不展示其代码。

End Sub

隐藏不在年限范围内的单元格

Sub yincang（x As String）

Range（x）.Select

Selection.NumberFormatLocal=“；；；”

将选中区域的内外边框设为空，这部分代码可以用录制宏的方式来实现，在此不展示其代码。

End Sub

四、结束语

Excel强大的计算及图表功能为财务管理教学带来极大的方便。动态模型的建立不仅可以节省教师备课时需要建立模型与多个表格的麻烦，也方便在课堂上演示，清晰地展示分析过程，有利于学生的学习吸收。

参考文献：

[1]肖文锋，肖莉.对财务管理教学方法的几点思考[J].会计之友，2010（5）：118-119.

[2]苏庆华.函数嵌套在计算机财务管理中的应用[J].中国管理信息化，2007（11）：68-72.

基金项目：大连理工大学创新性实验计划项目（2010692）；大连理工大学研究生教改基金资助项目（JG1030）。

作者简介：郭少蓉，大连理工大学管理与经济学部，信息管理与信息系统专业；刘艳萍，大连理工大学管理与经济学部副教授，硕士生导师，管理科学与工程博士。主要研究方向：会计信息系统，资产负债组合优化。