Ｅｘｃｅｌ“模擬運算表”工具在長期貸款分析中的應用

摘要:在信息化的時代，人們越來越意識到利用信息化工具在提高工作效率、創造更大效益等方面所發揮的重要作用。本文寫作的目的僅是拋磚引玉，希望越來越多的人們都能利用現代化的工具為其所用，更好地工作和生活。

關鍵詞:Excel的兩種“模擬運算表”;長期貸款分析;應用

目前，無論是企業還是個人都有可能需要長期貸款。進行長期貸款理性的分析是必要的。長期貸款分析主要是對貸款的金額、貸款的利率、貸款的期限和歸還期限等因素進行分析。我們經常要觀察某一個因素變動或某兩個因素的變動對其他因素的影響，Excel就提供了“模擬運算表”工具來解決這類問題。

“模擬運算表”就是將工作表中的一個單元格區域的數據進行模擬計算，測試使用一個或兩個變量對運算結果的影響。在Excel中，可以構造兩種模擬運算表：單變量模擬運算表和多變量模擬運算表。

一、單變量模擬運算表

單變量模擬運算表就是基于輸入一個變量，用它來測試對公式計算結果的影響。

創建單變量模擬運算表，所設計的運算表應當是：其輸入數值被排列在一列中或一行中。使用的公式必須引用輸入單元格（輸入單元格：在該單元格中，源于數據表的輸入值將被替換。工作表中的任何單元格都可用作輸入單元格。盡管輸入單元格不必是數據表的一部分，但是數據表中的公式必須引用輸入單元格）。如果數據表是列方向的，在第一個數值的上一行且位于數值列右邊的單元格中鍵入公式。如果數據表是行方向的，在第一個數值左邊一列且位于數值行下方的單元格中鍵入公式。

例1：某企業向銀行貸款10000元，期限5年，用“模擬運算表”工具來測試不同的利率對月還款額的影響，步驟如下：

1.設計單變量模擬運算表結構，利率設置為列變量，所以設置的公式單元格B7是位于變量值所在列的右側，并且高于第一個變量單元格A8一行。左側單元格A7無任何作用。如圖1所示。

2.在公式單元格B7中計算利率為4%時月還款額，輸入公式“B7=PMT(B5/12，B4，B1)”。

3.選取包括公式和需要進行模擬運算的單元格區域A7:B16。

4.單擊“數據”菜單，選擇“模擬運算表”項，彈出“模擬運算表”對話框，如圖2所示。

5.由于本例中引用的是列數據，故在“輸入引用列的單元格”中輸入“$B$5”。單擊“確定”按鈕，即得到單變量模擬運算結果表，如圖3所示。

二、雙變量模擬運算表

雙變量模擬運算表就是考慮兩個變量的變化對公式計算結果的影響。

用一個簡單的算式z=3x+4y+1來看：要求當x等于從1到4間的所有整數，而y為1到7間所有整數時所有z的值，用模擬運算表做：首先排好x與y的位置，然后在下面的單元格中建立一個公式，在公式所在行的右邊和下面分別輸入兩個變量的變化值，這里我們在行上為x，列上為y，然后選中這個方形的區域，選擇“數據”菜單中的“模擬運算表”命令，打開“模擬運算表”對話框，將“輸入引用行的單元格”選擇為公式中x的數值所在單元格，“輸入引用列的單元格”選擇為公式中y的數值所在的單元格。如圖4所示。

單擊“確定”按鈕，就可以看到運算的結果了。如果公式發生了改變，只要修改公式單元格中的公式就可以了，模擬運算表的排列位置及計算結果如圖5所示。

例2：某企業打算向銀行申請長期借款進行項目投資，財務人員考慮了兩種還款方式想從中選擇其一?；緱l件是：用6年時間，借款金額在100萬元—500萬元，利率在5%—12%之間，采用到期一次還本付息還款方式或采用每年等額還本付息方式。分析哪種還款方式最為有利？用“雙變量模擬運算表”工具來測試不同還款方式對還款額的影響，步驟如下：

1.設計雙變量模擬運算表結構，如圖6所示。

2.在單元格D4中計算借款金額為100萬元，借款期限為6年，借款利率為5%的條件下到期一次還本付息的金額，輸入公式“D4=FV(C4，B4，，A4)”。

3.選取包括公式和需要進行模擬運算的單元格區域D4:I12。

4.單擊【數據】菜單，選擇【模擬運算表】項，彈出【模擬運算表】對話框，在【輸入引用行的單元格】中輸入“$A$4”，在【輸入引用列的單元格】中輸入“$C$4”，如圖7所示。

5.單擊【確定】按鈕，即得到到期一次還本付息雙變量模擬運算表的結果，如圖8所示。

6.按照上述（2）到（5）的步驟進行每年等額還本付息雙變量模擬的運算，計算結果如圖9所示。

7.從圖9中可以看到，將借款200萬元，利率為7%，還款期為6年的到期一次還本付息和每年等額還本付息還款方式相比較，前者6年還款總額為300萬元，后者6年還款總額為252萬元，由此得出每年等額還本付息比到期一次還本付息還款方式要少負債很多。

例3：林某準備按揭貸款買房，他手中有資金150000元，其收入為每月3600元。現在他想選擇一套125平方米，單價為4000元/平方米的房子，假設首付款為房款的30%，貸款年利率為5%，準備20年還完?，F在對以下兩種情況進行購房分析：

第一，如果房子的價格、還款年限不變，林某只能每月支付2000元的貸款，用單變量模擬運算表確定林某能購買的合適的住房面積。

第二，如果房子的價格不變，而面積和貸款的還款年限可以變動，用雙變量模擬運算表確定合適的面積和貸款還款年限。

分析如下：

（1）建立單變量模擬運算表如圖10所示，在B7單元格中使用函數B7=PMT(B5/12，B6*12，B2*B3-B4)計算出每月應付款數額。再選中A7：B21，使用“模擬運算表”，計算出不同面積每月應付款數額。從計算結果分析：林某每月支付2000元的貸款，只能選擇面積為105平方米及以下的房子。

（2）建立雙變量模擬運算表如圖11所示，在B7單元格中使用函數B7=PMT(B5/12，B6*12，B2*B3-B4)計算出每月應付款數額。再選中B7：M21，使用“雙變量模擬運算表”，計算出不同面積、不同年限的每月應付款數額。從計算結果分析：林某每月支付2000元的貸款，可以選擇面積和年限為表中有底色顯示的房子。