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

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

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

一、引言

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

二、PMT（）函數(shù)簡介

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

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

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

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

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

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

四、案例

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

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

五、籌資基本模型建立

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

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

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

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

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

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

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

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

首先，在D13：H14區(qū)域內(nèi)輸入對應(yīng)的借款類型和借款年限。在D15：H15區(qū)域中輸入公式，比如D15中輸入=C5，通過公式的使用，可以實現(xiàn)數(shù)據(jù)的動態(tài)鏈接，當(dāng)基本數(shù)據(jù)區(qū)的數(shù)據(jù)發(fā)生變化時，此區(qū)域的數(shù)據(jù)隨之變化。

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

再次，在此表的交叉單元格C15中輸入函數(shù)=ABS（PMT（F6/12，F(xiàn)7*12，F(xiàn)5）），表示每個月應(yīng)償還的金額，使用ABS（）函數(shù)，是為了把負數(shù)調(diào)整為正數(shù)，使整個界面看起來清晰。

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

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

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

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

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

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

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

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

參考文獻：

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

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

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

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