EXCEL數(shù)據(jù)管理與分析功能在購(gòu)房貸款中的應(yīng)用

雷麗娟 許敏

摘 要 本文利用模擬運(yùn)算表，方案管理器分別計(jì)算貸款額度、貸款年限及月利率分別改變時(shí)的月還款額，為用戶購(gòu)買商品房辦理銀行按揭提供參考。

關(guān)鍵詞 數(shù)據(jù)管理與分析 貸款

中圖分類號(hào)：TP31 文獻(xiàn)標(biāo)識(shí)碼：A

Excel是微軟公司開(kāi)發(fā)的辦公軟件系列中的一款數(shù)據(jù)處理軟件，它不僅可以用于輸入和操縱數(shù)據(jù)，同時(shí)也為我們提供了許多數(shù)據(jù)管理與分析工具，有時(shí)我們?cè)谶M(jìn)行計(jì)算時(shí)常常需要假設(shè)某種情況，比如改變其中的一個(gè)條件，結(jié)果也要發(fā)生相應(yīng)變化，同時(shí)我們又想對(duì)比這些情況，這時(shí)我們就可以使用excel提供的數(shù)據(jù)管理與分析工具，幫助我們模擬運(yùn)行的可能結(jié)果，①如模擬運(yùn)算表，方案管理器等。

下面我們就以購(gòu)買商品房辦理銀行按揭為背景，利用“模擬運(yùn)算表”和“方案管理器”來(lái)分別計(jì)算貸款額度、貸款年限及月利率分別改變時(shí)對(duì)月還款額的影響，為用戶選擇一種適合自己的貸款方案提供參考。

第一步：建立購(gòu)房貸款計(jì)劃表如圖1所示，將所有月還款額對(duì)應(yīng)的單元格設(shè)置為數(shù)值類型。

圖1

第二步：利用PMT函數(shù)計(jì)算固定貸款額度，貸款年限，月利率下月還款額。

在E8單元格中輸入公式：=PMT（C8，B8*12，A8），這里利用PMT函數(shù)來(lái)計(jì)算月還款額，PMT（rate，nper，pv，fv，type）是基于固定利率及等額分期付款方式下返回貸款的每期付款額。其中Rate代表貸款利率，Nper代表該項(xiàng)貸款的付款總次數(shù)，rate與nper要相匹配，譬如果rate為月利率，那么nper應(yīng)為月數(shù)，Pv代表本金，F(xiàn)v代表在最后一次付款后希望得到的現(xiàn)金余額，忽略表示0，Type值為0或省略表示付款時(shí)間是在期末，為1時(shí)表示在期初。

第三步：利用單變量模擬運(yùn)算表計(jì)算固定貸款額度，貸款年限，不同月利率下月還款額的變化。

在B10單元格輸入PMT公式計(jì)算月還款額，選擇B9：F10區(qū)域，點(diǎn)擊數(shù)據(jù)菜單下的模擬運(yùn)算表，在輸入引用行單元格輸入$C$8，點(diǎn)擊確定。使用單變量模擬運(yùn)算表時(shí)有幾個(gè)需要注意的地方。

（1）單元格公式位置：模擬表區(qū)域中可變條件在同一行，那么單元格公式在模擬表的左下方（如果可變條件在同一列，那么單元格公式在模擬表的右上方）；

（2）輸入引用行（列）單元格選擇：模擬表區(qū)域中可變條件在同一行，那么選擇輸入引用行單元格（如果可變條件在同一列，那么選擇輸入引用列單元格）；

（3）輸入引用行（列）單元格中數(shù)據(jù)：根據(jù)模擬表區(qū)域中可變條件，在輸入引用行（列）輸入單元格公式中用到的對(duì)應(yīng)的條件單元格。

第四步：利用雙變量模擬運(yùn)算表計(jì)算固定貸款額度，不同貸款年限，不同月利率下月還款額的變化情況

首先在A14單元格中輸入公式=PMT（C8，B8*12，A8），計(jì)算某一固定貸款額度，貸款年限及貸款利率下月還款額，然后選中A14到F18這片連續(xù)的單元格，點(diǎn)擊數(shù)據(jù)菜單下的模擬運(yùn)算表，在輸入引用行的單元格中填寫(xiě)$B$8，在輸入引用列的單元格中輸入$C$8，點(diǎn)擊確定。使用雙變量模擬運(yùn)算表時(shí)有幾個(gè)需要注意的地方。

（1）單元格公式位置：模擬表區(qū)域中左上角頂角的位置；

（2）輸入引用行單元格中數(shù)據(jù)：根據(jù)單元格區(qū)域中第一行中的可變條件，在輸入引用行的單元格中輸入單元格公式中用到的對(duì)應(yīng)的條件單元格；

（3）輸入引用列單元格中數(shù)據(jù)：根據(jù)單元格區(qū)域中第一列中的可變條件，在輸入引用列的單元格中輸入單元格公式中用到的對(duì)應(yīng)的條件單元格。

第五步：當(dāng)貸款額度，貸款年限，月利率都發(fā)生改變的情況下可以使用方案管理器進(jìn)行比較，方案是excel保存在工作表中并可以自動(dòng)替換的一組值，使用方案管理器可以預(yù)測(cè)工作表模型的輸出結(jié)果，在工作表中創(chuàng)建并保存了不同的數(shù)值組，通過(guò)切換到任意的新方案可以查看不同的結(jié)果。

首先選擇任意選一種方案，計(jì)算其月還款額，在F21單元格中輸入公式=PMT（E21，D21*12，C21），然后選擇可變條件區(qū)域C21：E21，點(diǎn)擊工具菜單欄下的方案，點(diǎn)擊添加方案，彈出添加方案對(duì)話框，在對(duì)話框的方案名文本框中輸入組合貸款，可變條件單元格自動(dòng)選擇C21：E21，點(diǎn)擊確定，彈出方案變量值對(duì)話框，在對(duì)話框中的貸款金額文本框中填入數(shù)字500000，在貸款年限文本框中填入數(shù)字25，在月利率文本框中填入數(shù)字0.0038點(diǎn)擊確定，組合貸款方案就完成了，按此方法添加公積金貸款方案，商業(yè)性貸款方案。公積金貸款方案貸款金額設(shè)置為600000，貸款年限設(shè)置為30，月利率設(shè)置為0.0034，商業(yè)性貸款方案貸款金額設(shè)置為400000，貸款年限設(shè)置為20，月利率設(shè)置為0.0042。最后選擇不同方案，單擊顯示，可在當(dāng)前可變單元格區(qū)域顯示不同方案結(jié)果。單擊摘要，可生成方案總結(jié)報(bào)告，其具體步驟是點(diǎn)擊摘要，彈出方案摘要對(duì)話框，在對(duì)話框的結(jié)果單元格中填寫(xiě)=$F$21，結(jié)果單元格中填寫(xiě)的是使用了可變條件單元格中數(shù)據(jù)進(jìn)行公式計(jì)算的結(jié)果單元格，點(diǎn)擊確定可生成方案摘要。

注釋

① 周洪林.EXCEL函數(shù)高級(jí)應(yīng)用[J].福建電腦，2009（179）.