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

雷麗娟 許敏

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

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

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

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

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

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

圖1

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

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

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

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

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

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

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

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

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

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

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

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

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

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

注釋

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