1、第10章貸款購房方案,第十章貸款購房方案,案例說明,知識點(diǎn),案例制作,1,2,3,4,知識拓展,15.1 案例說明,本章將通過一個實例介紹中文版Excel 2003在個人貸款數(shù)據(jù)處理方面的應(yīng)用，其中包括PMT函數(shù)的使用、模擬運(yùn)算表 、單變量求解等方面的知識及相關(guān)操作。,一、案例分析,日常生活中，人們越來越多地同銀行的存貸業(yè)務(wù)打交道，如住房貸款、汽車貸款、教育貸款及個人儲蓄等，但很多人對某一貸款的月償還金額計算或利息計算往往感到束手無策。 隨著銀行信貸業(yè)務(wù)的廣泛開展，貸款購房成為多數(shù)家庭購房時選擇的方案。那么如何根據(jù)自己的還款能力制定一個切實可行的購房貸款計劃尤為重要。,二、設(shè)計思路,怎樣制定一

2、個合理的借貸方案呢？ 我們可以利用Excel的PMT函數(shù)及雙變量模擬運(yùn)算表做一個購房貸款方案表，它包含在“還款期數(shù)”和“貸款本金”兩個參數(shù)同時變化的情況下“貸款的每期（月）償還額”，從中我們可以很容易選擇出適合自己的一套方案來。還可使用單變量求解根據(jù)個人償還能力來推算出可貸款上限。,10.2 知識點(diǎn),一、 PMT函數(shù) PMT函數(shù)是基于固定利率及等額分期付款方式，返回貸款的每期付款額。 PMT函數(shù)的格式為：PMT（rate，nper，pv，fv，type），返回值為“投資或貸款的每期（月）償還額”。 參數(shù)Rate必選，是Double類型，指定每一期的貸款利率。例如，如果有一筆貸款年百分比率（AP

3、R）為百分之十且按月付款，則每一期的利率為0.1/12或0.0083。,參數(shù)Nper必選，Integer類型，指定一筆貸款的還款期數(shù)。如果對一筆為期四年的汽車貸款選擇按月付款，則貸款共有412（或48）個付款期。參數(shù)Pv必選，Double類型，現(xiàn)值或一系列未來付款的當(dāng)前值的累積和，也稱為本金。例如，貸款買汽車時，向貸方所借貸的金額為將來每月償付給貸方的現(xiàn)值。參數(shù)Fv可選，是Variant類型，指定在付清貸款后所希望的未來值或現(xiàn)金結(jié)存。例如，貸款的未來值在貸款付清后為0元。但是，如果想要在8年間存下50000元作為子女教育基金，那么50000元為未來值。如果省略的話，缺省值為0。參數(shù)Type可選

4、，Integer類型，指定各期的付款時間是在期初還是期末，如果貸款是在貸款周期結(jié)束時到期，請使用0；如果貸款是在周期開始時到期，則請使用1；如果省略的話，缺省值為0。,所謂模擬運(yùn)算表是對Excel工作表中的一個單元格區(qū)域的數(shù)據(jù)進(jìn)行模擬計算，它可以顯示一個計算公式中某些參數(shù)的值的變化對計算結(jié)果的影響。它可以將所有不同的計算結(jié)果以列表方式同時顯示出來，因而便于查看、比較和分析。根據(jù)分析計算公式中參數(shù)的個數(shù)，模擬運(yùn)算表又分為單變量模擬運(yùn)算表和雙變量模擬運(yùn)算表。,二、模擬運(yùn)算表,方案是一組命令的組成部分，這些命令有時也被稱作假設(shè)分析工具。 （假設(shè)分析：該過程通過更改單元格中的值來查看這些更改對工作表中

5、公式結(jié)果的影響。例如，更改分期支付表中的利率可以調(diào)整支付金額） 方案是一組由Excel 保存在工作表中并可進(jìn)行自動替換的值。可以使用方案來預(yù)測工作表模型的輸出結(jié)果，還可以在工作表中創(chuàng)建并保存不同的數(shù)值組，然后切換到任何新方案以查看不同的結(jié)果。可以為每個方案定義多達(dá)32個可變單元格，也就是說對一個模型可以使用多達(dá)32個變量來進(jìn)行模擬分析。,三、方案與方案管理,單變量求解是求解只有一個變量的方程的根，方程可以是線性方程，也可以是非線性方程。單變量求解工具可以解決許多財務(wù)管理中設(shè)計一個變量的求解問題。 例如，假設(shè)目前銀行的貸款年利率是6%，小豆希望可以在20年內(nèi)還完所有的貸款，而且目前的償還能力每月

6、可負(fù)擔(dān)2000元，那么可貸款的上限是多少呢？ 利用單變量求解工具進(jìn)行計算的具體步驟： 1、首先設(shè)計計算表格，如圖10-1所示。,四、單變量求解,圖10-1計算表格,2、在B1中輸入公式=PMT(B2/12,B312,B4)，計算每期償還金額。 3、在Excel的“數(shù)據(jù)工具”菜單中選擇【假設(shè)分析】，然后單擊【單變量求解】，打開“單變量求解”對話框，如圖10-2所示。,圖10-2單變量求解對話框,4、在“目標(biāo)單元格”文本框輸入“$B$1”，在“目標(biāo)值”文本框中輸入“-2000”，在“可變單元格”文本框中輸入“$B$4”。,5、單擊【確定】按鈕, 系統(tǒng)自動進(jìn)行運(yùn)算，運(yùn)算完畢后彈出“單變量求解狀態(tài)”對

7、話框，如圖10-3所示，同時計算結(jié)果顯示在工作表上。,6、按下【確定】，則最終的計算結(jié)果如圖10-4所示?？梢姡@樣的條件大約可貸27萬左右。,表格建立與數(shù)據(jù)輸入,A,模擬運(yùn)算表的計算,B,方案設(shè)計與管理器使用,C,貸款年限選擇,D,10.3 案例制作,一、表格建立與數(shù)據(jù)輸入,步驟如下： 1、新建Excel工作簿，打開一張工作表，在B2單元格輸入年利率6%，在C2單元格輸入借貸期數(shù)5年（此單元格將被設(shè)置為列變量），在D2單元格輸入房價600000（此單元格將被設(shè)置為行變量 2、在C5I5區(qū)域輸入不同房價，在B6B10區(qū)域輸入不同按揭年數(shù)的月份數(shù)。 3、在B5單元格建立公式：PMT（B2/12，

8、C2*12，D2），回車確認(rèn)，即可在B5單元格得到房價60萬元5年按揭的月供金額。結(jié)果如圖10-5所示。,圖10-5貸款購房模擬運(yùn)算表1,二、模擬運(yùn)算表的計算,步驟如下： 1、選取區(qū)域B5I10，建立模擬運(yùn)算表。選擇【數(shù)據(jù)】【模擬運(yùn)算表】命令，打開“數(shù)據(jù)表”對話框。 2、分別指定$D$2為“引用行的單元格”（即行變量），$C$2為“引用列的單元格”（即列變量），單擊【確定】按鈕，隨后，在C6I10區(qū)域便顯示不同還款期限、不同房價的房屋月供金額，如圖10-7所示。例如：F6單元格的數(shù)值表示50萬元房價、15年按揭的月供金額。,圖10-7貸款購房模擬運(yùn)算表2,三、方案設(shè)計與方案管理器的使用,1、創(chuàng)

9、建方案 當(dāng)建立了完整的數(shù)據(jù)表后，就可以使用“方案”命令對其進(jìn)行模擬分析了。其執(zhí)行步驟如下： (1) 在Excel的【數(shù)據(jù)工具】菜單中選擇【假設(shè)分析】，然后單擊【方案管理器】。 (2) 按下【添加】按鈕。出現(xiàn)一個“添加方案”對話框。 (3) 在“方案名”框中鍵入方案名。在“可變單元格”框中鍵入單元格的引用，在這里我們輸入“B7:B10”。對保護(hù)項進(jìn)行選擇。按下【確定】按鈕。就會進(jìn)入到如圖10-8所示的對話框。,圖10-8方案變量值對話框,(4) 編輯每個可變單元格的值，在輸入過程中要使用【Tab】鍵在各輸入框中進(jìn)行切換。如果我們再建立附加的方案，可以選擇【添加】按鈕重新進(jìn)入到【添加方案】對話框中

10、。 重復(fù)輸入全部的方案。當(dāng)輸入完所有的方案后，按下【確定】按鈕。 (5) 選擇【關(guān)閉】按鈕，完成該項工作。,2、顯示方案 設(shè)定了各種模擬方案后，就可以隨時察看模擬的結(jié)果。操作步驟如下： (1) 在【數(shù)據(jù)工具】菜單中選擇【假設(shè)分析】，然后單擊【方案管理器】，出現(xiàn)“方案管理器”對話框。 (2) 在“方案管理器”列表框中，選定要顯示的方案。 (3) 按下【顯示】按鈕。選定方案中可變單元格的值出現(xiàn)在工作表的可變單元格中，同時工作表重新計算，以反映模擬的結(jié)果。 (4) 重復(fù)顯示其它方案，最后按下【關(guān)閉】按鈕。,四、 貸款年限選擇,Excel中可用單變量求解方式計算最長借貸年限，以方便借貸者選擇合適的借貸

11、年限。 例如，假設(shè)小豆以6%的年利率向銀行借貸600000元，而且他目前的償還能力每月可負(fù)擔(dān)4000元，那么他最多能借款多少年呢？,先設(shè)計計算表，在B1中輸入借款額600000，B2中輸入年利率6%，B4中任意輸入一個正數(shù)（比如1），再在B3中輸入公式“=PMT(B2/12,B4*12,-B1)”，計算每月的等額償還額。打開“單變量求解”對話框，在“目標(biāo)單元格”文本框輸入“$B$3”，在“目標(biāo)值”文本框輸入“4000”，在“可變單元格”輸入“$B$4”。單擊【確定】。則系統(tǒng)自動進(jìn)行運(yùn)算，最后結(jié)果如圖10-9所示?？梢?，小豆最多可借款23年多。,15.4 知識拓展,單變量與雙變量模擬運(yùn)算比較,方

12、案總結(jié)與方案保護(hù),A,B,一、單變量與雙變量模擬運(yùn)算的比較,雙變量模擬運(yùn)算表與單變量模擬運(yùn)算表最大區(qū)別是前者受兩個變量的同時影響，而后者僅受一個變量的影響。 創(chuàng)建雙變量模擬運(yùn)算表的步驟與創(chuàng)建單變量模擬運(yùn)算表的步驟相差不大，如果已經(jīng)掌握創(chuàng)建單變量模擬運(yùn)算表的方法，則創(chuàng)建雙變量模擬運(yùn)算表就很簡單了。 創(chuàng)建雙變量模擬運(yùn)算表時只是在“模擬運(yùn)算表”對話框中在“輸入引用列的單元格”編輯框中和“輸入引用行的單元格”編輯框中均需為單元格鍵入引用，其他與創(chuàng)建單變量模擬運(yùn)算表的步驟基本相同。,二、方案總結(jié)與方案保護(hù),1、方案總結(jié) （1）在【數(shù)據(jù)】選項卡上的【數(shù)據(jù)工具】組中，單擊【假設(shè)分析】，然后單擊【方案管理器】。 （2）單擊【摘要】。 （3）單擊【方案摘要】或【方案數(shù)據(jù)透視表】。 （4）在【結(jié)果單元格】框中，輸入單元格的引用，該單元格為方案用于更改結(jié)果的單元格。多個引用需用逗號分隔開,注意：在生成方案總結(jié)報告時不一定需要結(jié)果單元格，而在生成方案數(shù)據(jù)透視表時則一定需要！,2、方案保護(hù) 在創(chuàng)建方案時在“添加方案”對話框中有一個“保護(hù)項”，如圖所示。在“保護(hù)項