運用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）。

作者簡介：郭少蓉，大連理工大學管理與經濟學部，信息管理與信息系統專業；劉艷萍，大連理工大學管理與經濟學部副教授，碩士生導師，管理科學與工程博士。主要研究方向：會計信息系統，資產負債組合優化。