【运用excel建立长期借款动态还款模型】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”