1、會計學1貸款購房方案貸款購房方案案例說明案例說明知識點知識點 案例制作案例制作 1234知識拓展知識拓展 第1頁/共28頁15.1 15.1 案例說明案例說明 本章將通過一個實例介紹中文版本章將通過一個實例介紹中文版Excel 2003Excel 2003在個人貸在個人貸款數據處理方面的應用，其中包括款數據處理方面的應用，其中包括PMTPMT函數的使用、函數的使用、模擬運算表模擬運算表 、單變量求解等方面的知識及相關操作、單變量求解等方面的知識及相關操作。第2頁/共28頁 日常生活中，人們越來越多地同銀行的存貸日常生活中，人們越來越多地同銀行的存貸業務打交道，如住房貸款、汽車貸款、教育貸款業務

2、打交道，如住房貸款、汽車貸款、教育貸款及個人儲蓄等，但很多人對某一貸款的月償還金及個人儲蓄等，但很多人對某一貸款的月償還金額計算或利息計算往往感到束手無策。額計算或利息計算往往感到束手無策。 隨著銀行信貸業務的廣泛開展，貸款購房成隨著銀行信貸業務的廣泛開展，貸款購房成為多數家庭購房時選擇的方案。那么如何根據自為多數家庭購房時選擇的方案。那么如何根據自己的還款能力制定一個切實可行的購房貸款計劃己的還款能力制定一個切實可行的購房貸款計劃尤為重要。尤為重要。第3頁/共28頁怎樣制定一個合理的借貸方案呢？怎樣制定一個合理的借貸方案呢？我們可以利用我們可以利用Excel的的PMT函數函數及及雙變量模擬運

3、雙變量模擬運算表算表做一個購房貸款方案表，它包含在做一個購房貸款方案表，它包含在“還款期還款期數數”和和“貸款本金貸款本金”兩個參數同時變化的情況下兩個參數同時變化的情況下“貸款的每期（月）償還額貸款的每期（月）償還額”，從中我們可以很，從中我們可以很容易選擇出適合自己的一套方案來。還可使用容易選擇出適合自己的一套方案來。還可使用單單變量求解變量求解根據個人償還能力來推算出可貸款上限根據個人償還能力來推算出可貸款上限。第4頁/共28頁一、一、 PMTPMT函數函數 PMTPMT函數是基于固定利率及等額分期付款方式，返回貸函數是基于固定利率及等額分期付款方式，返回貸款的每期付款額。款的每期付款額

4、。 PMTPMT函數的格式為：函數的格式為：PMTPMT（raterate，npernper，pvpv，fvfv，typetype），返回值為，返回值為“投資投資或貸款的每期（月）償還額或貸款的每期（月）償還額”。 參數參數RateRate必選，是必選，是DoubleDouble類型，指定每一期的貸款利類型，指定每一期的貸款利率。例如，如果有一筆貸款年百分比率（率。例如，如果有一筆貸款年百分比率（APRAPR）為百分之十）為百分之十且按月付款，則每一期的利率為且按月付款，則每一期的利率為0.1/120.1/12或或0.00830.0083。 第5頁/共28頁 參數參數NperNper必選，必選

5、，IntegerInteger類型，指定一筆貸款的還款期數。類型，指定一筆貸款的還款期數。如果對一筆為期四年的汽車貸款選擇按月付款，則貸款共有如果對一筆為期四年的汽車貸款選擇按月付款，則貸款共有4 41212（或（或4848）個付款期。）個付款期。參數參數PvPv必選，必選，DoubleDouble類型，現值或一系列未來付款的當前類型，現值或一系列未來付款的當前值的累積和，也稱為本金。例如，貸款買汽車時，向貸方所借值的累積和，也稱為本金。例如，貸款買汽車時，向貸方所借貸的金額為將來每月償付給貸方的現值。貸的金額為將來每月償付給貸方的現值。參數參數FvFv可選，是可選，是VariantVaria

6、nt類型，指定在付清貸款后所希望的類型，指定在付清貸款后所希望的未來值或現金結存。例如，貸款的未來值在貸款付清后為未來值或現金結存。例如，貸款的未來值在貸款付清后為0 0元。元。但是，如果想要在但是，如果想要在8 8年間存下年間存下5000050000元作為子女教育基金，那么元作為子女教育基金，那么5000050000元為未來值。如果省略的話，缺省值為元為未來值。如果省略的話，缺省值為0 0。參數參數TypeType可選，可選，IntegerInteger類型，指定各期的付款時間是在期類型，指定各期的付款時間是在期初還是期末，如果貸款是在貸款周期結束時到期，請使用初還是期末，如果貸款是在貸款周

7、期結束時到期，請使用0 0；如；如果貸款是在周期開始時到期，則請使用果貸款是在周期開始時到期，則請使用1 1；如果省略的話，缺省；如果省略的話，缺省值為值為0 0。第6頁/共28頁 所謂模擬運算表是對所謂模擬運算表是對ExcelExcel工作表中的一個單工作表中的一個單元格區域的數據進行模擬計算，它可以顯示一個元格區域的數據進行模擬計算，它可以顯示一個計算公式中計算公式中某些參數的值的變化對計算結果的影某些參數的值的變化對計算結果的影響響。它可以將所有不同的計算結果以列表方式同。它可以將所有不同的計算結果以列表方式同時顯示出來，因而便于查看、比較和分析。根據時顯示出來，因而便于查看、比較和分析

8、。根據分析計算公式中參數的個數，模擬運算表又分為分析計算公式中參數的個數，模擬運算表又分為單變量模擬運算表和雙變量模擬運算表。單變量模擬運算表和雙變量模擬運算表。二、模擬運算表二、模擬運算表第7頁/共28頁 方案是一組命令的組成部分，這些命令有時也被方案是一組命令的組成部分，這些命令有時也被稱作假設分析工具。稱作假設分析工具。 （假設分析：該過程通過更改單（假設分析：該過程通過更改單元格中的值來查看這些更改對工作表中公式結果的影響元格中的值來查看這些更改對工作表中公式結果的影響。例如，更改分期支付表中的利率可以調整支付金額）。例如，更改分期支付表中的利率可以調整支付金額） 方案是一組由方案是一

9、組由Excel Excel 保存在工作表中并可進行自保存在工作表中并可進行自動替換的值。可以使用方案來預測工作表模型的輸出結動替換的值。可以使用方案來預測工作表模型的輸出結果，還可以在工作表中創建并保存不同的數值組，然后果，還可以在工作表中創建并保存不同的數值組，然后切換到任何新方案以查看不同的結果。可以為每個方案切換到任何新方案以查看不同的結果。可以為每個方案定義多達定義多達3232個可變單元格，也就是說對一個模型可以使個可變單元格，也就是說對一個模型可以使用多達用多達3232個變量來進行模擬分析。個變量來進行模擬分析。 三、方案與方案管理三、方案與方案管理第8頁/共28頁 單變量求解是求解

10、只有一個變量的方程的根，方程單變量求解是求解只有一個變量的方程的根，方程可以是線性方程，也可以是非線性方程。單變量求解工可以是線性方程，也可以是非線性方程。單變量求解工具可以解決許多財務管理中設計一個變量的求解問題。具可以解決許多財務管理中設計一個變量的求解問題。 例如，假設目前銀行的貸款年利率是例如，假設目前銀行的貸款年利率是6%6%，小豆希望，小豆希望可以在可以在2020年內還完所有的貸款，而且目前的償還能力每年內還完所有的貸款，而且目前的償還能力每月可負擔月可負擔20002000元，那么可貸款的上限是多少呢？元，那么可貸款的上限是多少呢？ 利用單變量求解工具進行計算的具體步驟：利用單變量

11、求解工具進行計算的具體步驟：1 1、首先設計計算表格，如圖、首先設計計算表格，如圖10-110-1所示。所示。 四、單變量求解四、單變量求解第9頁/共28頁圖圖10-110-1計算表格計算表格 2 2、在、在B1B1中輸入公式中輸入公式=PMT(B2/12,B3=PMT(B2/12,B312,B4)12,B4)，計算，計算每期償還金額。每期償還金額。3 3、在、在ExcelExcel的的“數據工具數據工具”菜單中選擇菜單中選擇【假設分析假設分析】，然后單擊，然后單擊【單變量求解單變量求解】，打開，打開“單變量求解單變量求解”對對話框，如圖話框，如圖10-210-2所示。所示。第10頁/共28頁

12、圖圖10-210-2單變量求解對話框單變量求解對話框 4 4、在、在“目標單元格目標單元格”文本框輸入文本框輸入“$B$1$B$1”，在，在“目標值目標值”文本框中輸入文本框中輸入“-2000-2000”，在，在“可變單元格可變單元格”文本框中文本框中輸入輸入“$B$4$B$4”。第11頁/共28頁5 5、單擊、單擊【確定確定】按鈕按鈕, , 系統自動進行運算，運算完畢后彈系統自動進行運算，運算完畢后彈出出“單變量求解狀態單變量求解狀態”對話框，如圖對話框，如圖10-310-3所示，同時計算所示，同時計算結果顯示在工作表上。結果顯示在工作表上。6 6、按下、按下【確定確定】，則最終的計算結果如

13、圖，則最終的計算結果如圖10-410-4所示。可所示。可見，這樣的條件大約可貸見，這樣的條件大約可貸2727萬左右。萬左右。第12頁/共28頁表格建表格建立與數立與數據輸入據輸入 A模擬模擬運算運算表的表的計算計算 B方案方案設計設計與管與管理器理器使用使用 C貸款貸款年限年限選擇選擇 D10.3 10.3 案例制作案例制作第13頁/共28頁步驟如下：步驟如下：1 1、新建、新建ExcelExcel工作簿，打開一張工作表，在工作簿，打開一張工作表，在B2B2單元格輸入單元格輸入年利率年利率6%6%，在，在C2C2單元格輸入借貸期數單元格輸入借貸期數5 5年（此單元格將被年（此單元格將被設置為列

14、變量），在設置為列變量），在D2D2單元格輸入房價單元格輸入房價600000600000（此單元格（此單元格將被設置為行變量將被設置為行變量2 2、在、在C5I5C5I5區域輸入不同房價，在區域輸入不同房價，在B6B10B6B10區域輸入不同區域輸入不同按揭年數的月份數。按揭年數的月份數。3 3、在、在B5B5單元格建立公式：單元格建立公式：PMTPMT（B2/12B2/12，C2C2* *1212，D2D2），），回車確認，即可在回車確認，即可在B5B5單元格得到房價單元格得到房價6060萬元萬元5 5年按揭的月年按揭的月供金額。結果如圖供金額。結果如圖10-510-5所示。所示。 第14頁

15、/共28頁圖10-5貸款購房模擬運算表1第15頁/共28頁二、模擬運算表的計算二、模擬運算表的計算 步驟如下：步驟如下：1 1、選取區域、選取區域B5I10B5I10，建立模擬運算表。選擇，建立模擬運算表。選擇【數據數據】【模擬運算表模擬運算表】命令，打開命令，打開“數據表數據表”對話框。對話框。2 2、分別指定分別指定$D$2$D$2為為“引用行的單元格引用行的單元格”（即行變量），（即行變量），$C$2$C$2為為“引用列的單元格引用列的單元格”（即列變量），單擊（即列變量），單擊【確定確定】按鈕，隨后，在按鈕，隨后，在C6I10C6I10區域便顯示不同還款期限、不同區域便顯示不同還款期限

16、、不同房價的房屋月供金額，如圖房價的房屋月供金額，如圖10-710-7所示。例如：所示。例如：F6F6單元格的單元格的數值表示數值表示5050萬元房價、萬元房價、1515年按揭的月供金額。年按揭的月供金額。第16頁/共28頁圖10-7貸款購房模擬運算表2第17頁/共28頁三、方案設計與方案管理器的使用三、方案設計與方案管理器的使用 1 1、創建方案、創建方案當建立了完整的數據表后，就可以使用當建立了完整的數據表后，就可以使用“方案方案”命令對其命令對其進行模擬分析了。其執行步驟如下：進行模擬分析了。其執行步驟如下： (1) (1) 在在ExcelExcel的的【數據工具數據工具】菜單中選擇菜單

17、中選擇【假設分析假設分析】，然后單擊然后單擊【方案管理器方案管理器】。 (2) (2) 按下按下【添加添加】按鈕。出現一個按鈕。出現一個“添加方案添加方案”對話框。對話框。 (3) (3) 在在“方案名方案名”框中鍵入方案名。在框中鍵入方案名。在“可變單元格可變單元格”框框中鍵入單元格的引用，在這里我們輸入中鍵入單元格的引用，在這里我們輸入“B7:B10B7:B10”。對保。對保護項進行選擇。按下護項進行選擇。按下【確定確定】按鈕。就會進入到如圖按鈕。就會進入到如圖10-810-8所示的對話框。所示的對話框。 第18頁/共28頁圖10-8方案變量值對話框(4) (4) 編輯每個可變單元格的值，

18、在輸入過程中要使用編輯每個可變單元格的值，在輸入過程中要使用【TabTab】鍵在各輸入框中進行切換。如果我們再建立附加的方案鍵在各輸入框中進行切換。如果我們再建立附加的方案，可以選擇，可以選擇【添加添加】按鈕重新進入到按鈕重新進入到【添加方案添加方案】對話框對話框中。中。 重復輸入全部的方案。當輸入完所有的方案后，按下重復輸入全部的方案。當輸入完所有的方案后，按下【確定確定】按鈕。按鈕。 (5) (5) 選擇選擇【關閉關閉】按鈕，完成該項工作。按鈕，完成該項工作。第19頁/共28頁2 2、顯示方案、顯示方案 設定了各種模擬方案后，就可以隨時察看模擬的結果。設定了各種模擬方案后，就可以隨時察看模

19、擬的結果。操作步驟如下：操作步驟如下： (1) (1) 在在【數據工具數據工具】菜單中選擇菜單中選擇【假設分析假設分析】，然后單，然后單擊擊【方案管理器方案管理器】，出現，出現“方案管理器方案管理器”對話框。對話框。 (2) (2) 在在“方案管理器方案管理器”列表框中，選定要顯示的方案。列表框中，選定要顯示的方案。 (3) (3) 按下按下【顯示顯示】按鈕。選定方案中可變單元格的值出按鈕。選定方案中可變單元格的值出現在工作表的可變單元格中，同時工作表重新計算，以現在工作表的可變單元格中，同時工作表重新計算，以反映模擬的結果。反映模擬的結果。 (4) (4) 重復顯示其它方案，最后按下重復顯示

20、其它方案，最后按下【關閉關閉】按鈕。按鈕。 第20頁/共28頁四、四、 貸款年限選擇貸款年限選擇 Excel Excel中可用單變量求解方式計算最長借貸年限，以中可用單變量求解方式計算最長借貸年限，以方便借貸者選擇合適的借貸年限。方便借貸者選擇合適的借貸年限。 例如，假設小豆以例如，假設小豆以6%6%的年利率向銀行借貸的年利率向銀行借貸600000600000元，元，而且他目前的償還能力每月可負擔而且他目前的償還能力每月可負擔40004000元，那么他最多能元，那么他最多能借款多少年呢？借款多少年呢？ 第21頁/共28頁先設計計算表，在先設計計算表，在B1B1中輸入借款額中輸入借款額60000

21、0600000，B2B2中輸入年利中輸入年利率率6%6%，B4B4中任意輸入一個正數（比如中任意輸入一個正數（比如1 1），再在），再在B3B3中輸入中輸入公式公式“=PMT(B2/12,B4=PMT(B2/12,B4* *12,-B1)12,-B1)”，計算每月的等額償還額，計算每月的等額償還額。打開。打開“單變量求解單變量求解”對話框，在對話框，在“目標單元格目標單元格”文本框文本框輸入輸入“$B$3$B$3”，在，在“目標值目標值”文本框輸入文本框輸入“40004000”，在，在“可可變單元格變單元格”輸入輸入“$B$4$B$4”。單擊。單擊【確定確定】。則系統自動進行。則系統自動進行運

22、算，最后結果如圖運算，最后結果如圖10-910-9所示。可見，小豆最多可借款所示。可見，小豆最多可借款2323年多。年多。第22頁/共28頁單變量單變量與雙變與雙變量模擬量模擬運算比運算比較較方案總方案總結與方結與方案保護案保護AB第23頁/共28頁 雙變量模擬運算表與單變量模擬運算表最大區別是前雙變量模擬運算表與單變量模擬運算表最大區別是前者受兩個變量的同時影響，而后者僅受一個變量的影響。者受兩個變量的同時影響，而后者僅受一個變量的影響。 創建雙變量模擬運算表的步驟與創建單變量模擬運算創建雙變量模擬運算表的步驟與創建單變量模擬運算表的步驟相差不大，如果已經掌握創建單變量模擬運算表表的步驟相差不大，如果已經掌握創建單變量模擬運算表的方法，則創建雙變量模擬運算表就很簡單了。的方法，則創建雙變量模擬運算表就很簡單了。 創建雙變量模擬運算表時只是在創建雙變量模擬運算表時只是在“模擬運算表模擬運算表”對話對話框中在框中在“輸入引用列的單元格輸入引用列的單元格”編