雙變量模擬運算表在籌資模型中的應用及其注意事項

摘 要：籌資是一項重要的財務管理活動，充分高效率的獲取資金對個人和企業來說都是至關重要的。本文通過案例說明利用Excel雙變量模擬運算表建立個人房貸籌資決策模型，以及如何防范在建立模型的過程中容易出現的問題，以提高籌資決策效率。

關鍵詞：籌資模型；雙變量模擬運算表；等額本息還款

一、引言

隨著我國房貸款利率的不斷調整，越來越多的消費者在購買房屋時優先考慮貸款購房，但如何根據自己的實際情況來選擇合理的貸款方案是困擾大家的難題。首先，大家應該清楚目前的還款方式主要有等額本息還款和等額本金還款兩種，而對于不準備提前償還的貸款的人來說，會首先選擇等額本息還款方式，本文就是針對這種還款方式下進行的貸款決策。本文借助Excel提供的PMT函數及雙變量模擬運算表來進行房貸決策，具有一定實用價值。

二、PMT（）函數簡介

PMT（）是Excel提供的在固定利率下，計算貸款的等額分期償還額。其參數構成為PMT（rate，nper，pv，fv，type）。Rate表示期利率，當年利率為6%時，如果按年償還，則期利率為6%；如果按月償還，則期利率為6%/12。Nper表示總付款期，如一筆20年的貸款，按年付款總期數為100年；按月付款，則總基數為10*12。

Pv表示現值，即目前的貸款總額。Fv表示付清貸款后的未來值，因為我們通常計算現在貸款，每期償還金額，而很少用現在的貸款未來值來計算每期償還金額，所以此參數通常省略，缺省值為0。Type表示年金類型，先付年金為1，后會年金為0，缺省值為0。

此函數需要注意的是，現金的流入用正數表示，現金流出用負數表示；并且Pv和Fv兩個參數是必選其一，當然也可兩者都選。

三、雙變量模擬運算表簡介

模型y=f（x1，x2，x3，......，xn-1，xn）中，一共有n個自變量，模擬運算表就是假定在其余自變量固定不變，僅一個或者兩個自變量在一定范圍內非連續變動，在假設一個自變量有m個取值，另一個自變量有n個取值，則函數y有m×n個計算結果。雙變量模擬運算表可在計算公式固定的情況下，快速求出參數值的變化對計算結果的影響，并將所有計算結果同時顯示在一個表中，通過列表的方式直觀顯示，便于查看和比較。

一個自變量的變化對目標函數的影響分析，是單變量模擬運算表，兩個自變量變化對目標函數的影響分析，是雙變量模擬運算表。

四、案例

假設某人想通過向銀行借款購房，房產總價125萬元，首付20%，即需要借款100萬元。他可以選擇公積金借款或商業借款，不同的借款方式下依據年限的長短不同，利率也有所不同，2015年最新的數據是：公積金借款年限5年以下（含5年）的年利率為3.75%，5年～30年的年利率為4.25%；商業借款年限1年以內（含1年）的年利率為5.60%，1年～5年（含5年）的年利率為6.00%，5年～30年的年利率為6.15%。

由于收入的限制，此人每月還款額最高不能超過8000元，但也不想低于6000元。因為考慮到自己的收入相對穩定，不會提前償還貸款，他計劃采用等額本息還款法，按月還本付息，請給出其可選擇的貸款方案。

五、籌資基本模型建立

新建計算機財務管理工作薄，在sheet1工作表上建立借款籌資決策模型，如圖1。其中，左側的基本數據區顯示目前借款的不同類型及對應的年利率。首先，選擇“視圖”菜單下的子菜單“工具欄”，打開“窗體”對話框，單元格F4的位置插入“組合框”窗體，F7單元格的右側插入“微調項”，在“組合框”位置單擊右鍵，選擇“設置控件格式”，在微調按鈕位置，位置單擊右鍵，選擇“設置控件格式”。

通過這樣設置“組合框”，就達到了可以在下拉選項中選擇所需要的借款類型，并且在D4單元格顯示所選中的類型在數據源區域處于第幾行，比如我們選擇的是5年以下公積金借款，對應的D4單元格返回數據1。

“微調項”被這樣設置后，F7單元格中借款年限不用手工輸入，只要點擊微調按鈕就自動增減一年，借款年限限定在1～30之間，但同時要注意，年限范圍應與借款利率相匹配。

通過窗體的使用，大大提高輸入效率，同時避免了手工輸入的失誤。F7單元格需要根據用戶選擇的借款類型和借款年限自動顯示借款年利率。

在F6單元格輸入函數如下：=INDEX（C5：C9，D4），可以實現借款利率隨著借款類型的變化而發生相應的變化，比如：5年以下公積金借款其利率為3.75%。

在單元格F8中輸入函數如下：=ABS（PMT（F6/12，F7*12，F5，）），參數中利率為月利率，總期數為對應的月份數，可以計算等額本息還款方式下，每個月的還款額，用ABS（）函數求出正值使整個頁面看起來整齊。

基本模型設計結束，每月償還金額與上面的因素形成了動態鏈接，可以更改模型中的任意一個數據或多個數據，包括通過下拉框選擇借款類型，隨之而變動的借款年率，可以通過微調按鈕更改借款年限，也可以根據所需要借款的金額來輸入相應的借款本金，這些數據的變化都會使每個月的還款額發生變化，使決策快速化，用戶可以根據自己的實際能力選擇籌資方式。

六、利用雙變量模擬運算表設計籌資決策模型

首先，在D13：H14區域內輸入對應的借款類型和借款年限。在D15：H15區域中輸入公式，比如D15中輸入=C5，通過公式的使用，可以實現數據的動態鏈接，當基本數據區的數據發生變化時，此區域的數據隨之變化。

其次，在C16：C45區域中通過輸入序列的方式輸入借款年限1-30年。

再次，在此表的交叉單元格C15中輸入函數=ABS（PMT（F6/12，F7*12，F5）），表示每個月應償還的金額，使用ABS（）函數，是為了把負數調整為正數，使整個界面看起來清晰。

最后，選擇整張要放入模擬運算表內容的單元格區域C15：H45，從“數據”菜單下選擇“模擬運算表”，出現如圖5所示的模擬運算表對話框，在“輸入引用行的單元格”中選擇$F$6，在“輸入引用列的單元格”中選擇$F$7。表示是用表中的第15行中各種可能的利率數據來替換F6的值，用表中C列各種可能的年限數據來替換F7的值，點確定，此時雙變量模擬運算表操作就已完成。此時要注意輸入引用的行和輸入引用的列所要替換的單元格不要選反。

因為在表中表示利率的第15行中的利率是與基本數據形成動態鏈接的，所以當政策變更使利率發生變化時，只要更改基本數據區的對應利率即可。模擬運算表完成之后，當交叉單元格中公式中引用的數據發生變化時，模擬運算表中的數據也會隨之變化，但有些數據只影響交叉單元格的數據變化（比如：基本模型中借款利率和借款年限的變化），而有些數據的變化則影響整張模擬表中的數據（比如基本數據區的借款利率、基本模型區的借款本金的變化）。

決策者可以根據已經做好的雙變量模擬運算表來進行籌資決策，依據個人能否進行公積金借款，進行借款方式的選擇，再依據個人每個月的償還能力選擇合適的償還金額范圍，選擇償還金額所對應的借款年限。

七、使用雙變量模擬運算表要注意的幾個問題

（1）運算表中的所有結果都是一樣的數據。因為你在使用模擬運算表中輸入引用的行或輸入引用的列時，沒有使用交叉單元格式公式所使用的單元格地址。

（2）運算表中的年限不用使用年來顯示，而是折算成月來顯示。這是因為在基本模型區設置了總付款期數，在輸入引用的列時用的是總付款期這一單元格，所以，只能用此列中的數據替換總付款期數。

（3）基本數據區的內容很多，把模擬運算表放入不同的工作表中來顯示。在這種情況下，如果更改基本數據區中的其他因素，模擬運算表并不會相應發生變化。這是因為模擬運算表中輸入引用的行或輸入引用的列的單元格是不可以在不同的工作表中的，只能引用同一個工作表中的單元格。

（4）在修改模擬運算表時，經常出現“不能更改模擬運算表的某一部分”的警告語，而且讓你無法繼續操作，這時應該是處于編輯狀態，只需要點中公式編輯左側的×號，取消修改即可。

參考文獻：

[1]蔣秀蓮，宋言東等.利用excel雙變量模擬運算表進行購房貸款決策[J].會計之友，2007（6）上.

[2]張瑞君.計算機財務管理[M].北京：中國人民大學出版社，2007年.

[3]谷增軍.excel模擬運算表在財務分析中的應用[J].財會月刊，2010（1）.

作者簡介：陳艷杰（1975.07- ），女，江蘇徐州人，作者單位：徐州工程學院，講師，研究方向：財務管理