利用excel單變量模擬計算月還款額

現(xiàn)在買房、買車、裝修等都是采用的分期付款的方式，我們?nèi)绾斡嬎氵@些分期付款的方式呢，今天我們利用excel單變量模擬計算月還款額的操作方法。

單變量模擬運算主要用來分析當其他因素不變時， 一個參數(shù)的變化對目標值的影響。假設(shè)某企業(yè)推出 10 萬元汽車，顧客可以使用分期付款方式支付，要求首付為 20000，年限為 10 年，目前的年利率為 4.52％，分月償還。 則利用函數(shù)可以計算出每月的償還額， 然后建立單變量模擬運算表，快速求出不同年利率情況下客戶需要支付的金額和公司的回款總額。

1、打開一個excel工作表，切換到“單變量模擬銷售”工作表。

圖1

2、選擇單元格 D3，切換到【公式】選項卡，在【函數(shù)庫】組中單擊【財務(wù)】下拉按鈕，選擇PMT函數(shù)。

3、彈出 【函數(shù)參數(shù)】 對話框，在 Rate（比率） 、Nper （期數(shù)） 和 Pv （現(xiàn)值）中分別輸入 C3/2、B6*12和 B5。

圖2

4、單擊【確定】按鈕。

函數(shù) PMT 是用于計算在固定利率下，貸款的等額分期償還額。格式為 PMT（Rate,Nper,Pv,Fv,Type），其中各參數(shù)含義如下。

Rate 表示貸款利率。

Nper 表示該項貸款的付款總數(shù)。

Pv 表示現(xiàn)值，或一系列未來付款的當前值的累積和，也稱為本金。

Fv 表示未來值，或在最后一次付款后希望得到的現(xiàn)金余額，如果省略 Fv，則假設(shè)其值為零，也就是一筆貸款的未來值為零。

Type 的值為數(shù)字 0 或 1，用以指定各期的付款時間是在期初還是期末。

5、單元格 D3 中得到年利率為 4.52%時顧客每月還款額為 1941 元，也可以直接在單元格 D3 中輸入公式=PMT（C3/2,B6*12,B5），按下 Enter 鍵后得到結(jié)果。

圖3

6、選擇單元格 E3，切換到【公式】選項卡，在【函數(shù)庫】組中單擊【財務(wù)】下拉按鈕，選擇 FV 函數(shù)。

7、彈出 【函數(shù)參數(shù)】 對話框，在 Rate（比率） 、Nper（期數(shù)） 、Pmt（每期還款）和 Pv（現(xiàn)值）文本框中分別輸入 C3/12、 B7、D3 和——B4。

圖4

8、單擊【確定】按鈕。

9、在單元格 E3 中得到公司回款總額的計算結(jié)果為 26071，也可以直接向單元格 E3 中輸入公式=FV（C3/12,B7,D3,-B4），按下 Enter 鍵得到結(jié)果。

圖5

結(jié)果表示年限為 10 年，首付為 20000，年利率為 4.52%，公司在 3 個月后可以得到 26071 的回款額。 這個數(shù)額是把首付和前 3 個月公司得到的回款轉(zhuǎn)換為 3 個月后的未來值，并且進行加總得到的。

10、選擇單元格區(qū)域 C3:E7，切換到【數(shù)據(jù)】選項卡，在【數(shù)據(jù)工具】組中單擊【假設(shè)分析】下拉按鈕，執(zhí)行【數(shù)據(jù)表】命令。

11、彈出 【數(shù)據(jù)表】 對話框，單擊【輸入引用列的單元格】文本框右側(cè)的折疊按鈕。

圖6

所謂“引用列的單元格” ，即模擬運算表的模擬數(shù)據(jù)（最左列數(shù)據(jù)）要代替公式中的單元格地址。本例的模擬運算表是關(guān)于利率的模擬數(shù)據(jù)，所以指定為$C$3，即年利率所在的單元格為引用列的單元格。為了方便，通常稱其為模擬運算表的列變量。

12、在“單變量模擬銷售”工作表中選擇單元格 C3，單擊【數(shù)據(jù)表——輸入引用列的單元格】對話框右側(cè)的折疊按鈕，展開對話框。

圖7

13、返回【數(shù)據(jù)表】對話框，單擊【確定】按鈕。

圖8

14、在excel單元格區(qū)域D4:D7中得到不同年利率對應(yīng)的顧客每個月應(yīng)該償還的金額，在單元格區(qū)域 E4:E7 中得到企業(yè)在對應(yīng)利率的回款額。

圖9

選擇單元格區(qū)域 D8:E7，編輯欄中顯示公式為{=表（，C3）}，表示其是一個以 C3 為列變量的模擬運算表。與一般的計算公式相似，當改變模擬數(shù)據(jù)時，模擬運算表的數(shù)據(jù)會自動重新計算。