EXCEL數據管理與分析功能在購房貸款中的應用

雷麗娟 許敏

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

關鍵詞 數據管理與分析 貸款

中圖分類號：TP31 文獻標識碼：A

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

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

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

圖1

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

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

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

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

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

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

（3）輸入引用行（列）單元格中數據：根據模擬表區域中可變條件，在輸入引用行（列）輸入單元格公式中用到的對應的條件單元格。

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

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

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

（2）輸入引用行單元格中數據：根據單元格區域中第一行中的可變條件，在輸入引用行的單元格中輸入單元格公式中用到的對應的條件單元格；

（3）輸入引用列單元格中數據：根據單元格區域中第一列中的可變條件，在輸入引用列的單元格中輸入單元格公式中用到的對應的條件單元格。

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

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

注釋

① 周洪林.EXCEL函數高級應用[J].福建電腦，2009（179）.