利用excel單變量模擬計(jì)算月還款額

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

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

1、打開(kāi)一個(gè)excel工作表，切換到“單變量模擬銷(xiāo)售”工作表。

圖1

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

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

圖2

4、單擊【確定】按鈕。

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

Rate 表示貸款利率。

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

Pv 表示現(xiàn)值，或一系列未來(lái)付款的當(dāng)前值的累積和，也稱(chēng)為本金。

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

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

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

圖3

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

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

圖4

8、單擊【確定】按鈕。

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

圖5

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

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

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

圖6

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

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

圖7

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

圖8

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

圖9

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