貸款償還分析課件

Excel2010

財務(wù)應(yīng)用案例教程Excel2010

財務(wù)應(yīng)用案例教程選擇最佳還貸方案貸款償還進度與完成比例分析第8章貸款償還分析一二逆算利率和貸款額三貸款一詞，在現(xiàn)在的社會并不陌生，如私人購房、購車，企業(yè)為擴大生產(chǎn)規(guī)模購買機器設(shè)備等，如何選擇最佳的貸款方式，如何對貸款的償還進度和完成比例進行分析操作，以及在知道貸款總額、貸款年限和每月可承受的還款額時如何求貸款利率等，本章將為您解答上述問題。。選擇最佳還貸方案貸款償還進度與完成比例分析第8章貸款償還一、選擇最佳還貸方案（一）創(chuàng)建還貸方案表并計算各方案月償還額（二）判斷還貸方案可行性并選擇最佳方案一、選擇最佳還貸方案（一）創(chuàng)建還貸方案表并計算各方案月償還額

假設(shè)銀行有兩種貸款方案供客戶選擇，一種是等額本息還貸法，另一種是等額本金還貸法。等額本息還貸法是指貸款人自還貸之日起，每個月向銀行償還的貸款數(shù)額相同。其特點是：整個還款期內(nèi)，每個月的還款額保持不變，貸款人可以準確掌握每月的還款額。但需注意的是雖然每個月還貸的數(shù)額相同，但其中包含的本金和利息的數(shù)額是不同的，這種還貸方法比較適合收入穩(wěn)定的家庭。等額本金還貸法是指貸款人每個月償還的貸款本金的數(shù)額是一定的，利息是變化的，呈現(xiàn)逐漸減少的趨勢，每月總的還款數(shù)額是遞減的，雖然每月還款額逐漸減少，但償還的速度是保持不變的。這種方式比較適合于有一定的積蓄、在還款初期能力較強、并希望在還款初期歸還較大款項來減少利息支出的借款人。假設(shè)某企業(yè)準備貸款150000元購買機器設(shè)備，貸款期限為3年，年利率為5.40%，假設(shè)企業(yè)每月可償還的貸款額最高限額為5000，如何為企業(yè)選擇最適合的貸款方案？假設(shè)銀行有兩種貸款方案供客戶選擇，一種是等額本息（一）創(chuàng)建還貸方案表并計算各方案月償還額在工作表中輸入已知貸款數(shù)據(jù)，制作兩種還貸方案表格（一）創(chuàng)建還貸方案表并計算各方案月償還額在工作表中輸入已知貸

利用PMT函數(shù)計算等額本息月還款額PMT函數(shù)即年金函數(shù)。它的功能是基于固定利率及等額分期付款方式，返回貸款的每期付款額。其語法格式是：

PMT(Rate,Nper,Pv,Fv,Type)

其中：

Rate為貸款利率。

Nper為該項貸款的付款總期數(shù)。

Pv為現(xiàn)值，或一系列未來付款的當前值的累積和，也稱為本金。

Fv為未來值，或在最后一次付款后希望得到的現(xiàn)金余額，如果省略Fv，則假設(shè)其值為零，也就是一筆貸款的未來值為零。

Type數(shù)字0或1，用以指定各期的付款時間是在期初還是期末。1代表期初，不輸入或輸入0代表期末。

利用PMT函數(shù)計算等額本息月還款額PMT函數(shù)即復(fù)制公式得到三年間每個月的還款額，由結(jié)果可知，等額本息還貸法每月的償還金額相等

復(fù)制公式得到三年間每個月的還款額，由結(jié)果可知，等額本息還貸法

下面計算“等額本金還貸法”中的各月利率。按照規(guī)定，月利率等于年利率除以12，日利率等于年利率除以360，這里的360指的是會計周期的360天，其他每月利率在30天的基礎(chǔ)上進行利息的加、減計算，即小于30天的減，大于30天的加。計算出月利率和日利率值下面計算“等額本金還貸法”中的各月利率。按照規(guī)定選擇正常會計周期的單元格，即每月有30天的月份所在單元格，然后輸入公式“=$G$6”，按【Ctrl+Enter】組合鍵即可得到所有正常會計周期的月利率按住【Ctrl】鍵的同時單擊表示大月的單元格，然后輸入公式“=$G$6+$G$7”，然后按【Ctrl+Enter】組合鍵即可得到所有大月的月利率選擇正常會計周期的單元格，即每月有30天的月份所在單元格，然由于2011年和2013年是平年，所以選中單元格G14和G38，然后輸入公式“=$G$6-($G$7*2)”，按【Ctrl+Enter】組合鍵即可得到這兩個年份小月的月利率在G26單元格輸入公式“=$G$6-$G$7”，按【Enter】鍵即可計算出2012年2月的月利率。至此，這三年的月利率都計算出來了在H13單元格輸入0，然后根據(jù)銀行提供的等額本金還款公式：每月還款額＝（貸款本金／還款月數(shù)）＋（貸款本金－累計已還本金）×月利率，計算等額本金第1個月償還額輸入公式計算累計已還款額，然后向下復(fù)制公式由于2011年和2013年是平年，所以選中單元格G14和G3復(fù)制I13單元格公式計算各月償還額兩種還貸方案的還款總額和最高月償還額復(fù)制I13單元格公式計算各月償還額兩種還貸方案的還款總額和（二）判斷還貸方案可行性并選擇最佳方案企業(yè)所能承受的月償還額最高限額為5000，故要據(jù)此來判斷方案的可行性根據(jù)總還款額確定最佳方案等額本金還貸法還款總額（161384.01）小于等額本息還款總額（162814.42），所以最佳還貸方案應(yīng)為等額本金還貸法

（二）判斷還貸方案可行性并選擇最佳方案企業(yè)所能承受的月償還額二、貸款償還進度與完成比例分析（一）創(chuàng)建還貸表并計算相關(guān)數(shù)據(jù)（二）創(chuàng)建貸款償還進度和計劃完成比例餅圖為擴大生產(chǎn)規(guī)模，企業(yè)所需資金有可能從銀行貸款，因此就存在貸款償還問題。企業(yè)為維護自己的信譽，會嚴格按合同約定償還貸款，在此期間，企業(yè)可以對貸款的償還進度和完成比例進行分析。假設(shè)某企業(yè)為擴大生產(chǎn)規(guī)模，于2014年1月1日申請貸款250000，貸款年利率為6.40%，貸款期限為5年，計劃在5年內(nèi)全額歸還貸款。下面，我們就根據(jù)企業(yè)在某一段時間內(nèi)實際還款的情況分析貸款償還完成進度和計劃完成情況。二、貸款償還進度與完成比例分析（一）創(chuàng)建還貸表并計算相關(guān)數(shù)據(jù)（一）創(chuàng)建還貸表并計算相關(guān)數(shù)據(jù)根據(jù)已知數(shù)據(jù)創(chuàng)建還貸表格輸入公式計算年、月償還額創(chuàng)建計劃還款表格模型輸入公式計算出2014年計劃還款總額輸入公式并向下復(fù)制計算2015年1~5月的月還款額（一）創(chuàng)建還貸表并計算相關(guān)數(shù)據(jù)根據(jù)已知數(shù)據(jù)創(chuàng)建還貸表格輸入輸入公式計算出截止2015年5月底計劃還款總額輸入實際還款額創(chuàng)建還款比例表格輸入公式計算已還本金和還款比例輸入公式計算出截止2015年5月底計劃還款總額輸入實際還款（二）創(chuàng)建貸款償還進度和計劃完成比例餅圖

下面我們創(chuàng)建貸款償還進度和計劃完成比例半圓餅圖圖表。首先創(chuàng)建圖表輔助表格，然后根據(jù)其中的數(shù)據(jù)創(chuàng)建餅圖圖表。創(chuàng)建輔助表格在單元格中輸入數(shù)據(jù)輸入公式進行計算（二）創(chuàng)建貸款償還進度和計劃完成比例餅圖下面我們

在餅圖左側(cè)系列3上右擊鼠標在餅圖左側(cè)系列3上右擊鼠標選中圖表左側(cè)的系列1用同樣的方法“計劃完成比例圖”選中圖表左側(cè)的系列1用同樣的方法“計劃完成比例圖”三、逆算利率和貸款額（一）使用函數(shù)和單變量求解法逆算利率（二）使用函數(shù)和單變量求解法逆算貸款額逆算利率是指當知道貸款總額、貸款年限和每月可承受的還款額時，利用函數(shù)求貸款利率；逆算貸款額是指已知貸款年限、貸款利率和每月可承受的還款額時，利用函數(shù)求貸款總額。逆算利率和貸款額有兩種方法：函數(shù)法和單變量求解法。假設(shè)企業(yè)的貸款總額為200萬元，貸款期限為8年，每月可承受的最高還款額為28000元，如何逆算其年利率？或已知其貸款利率為6.55%，貸款期限為8年，每月可承受的最高還款額為28000元，如何對其逆算貸款額？三、逆算利率和貸款額（一）使用函數(shù)和單變量求解法逆算利率（二（一）使用函數(shù)和單變量求解法逆算利率制作的逆算利率模型表格利用函數(shù)計算年利率（一）使用函數(shù)和單變量求解法逆算利率制作的逆算利率模型表格RATE函數(shù)的作用返回年金的各期利率。函數(shù)RATE通過迭代法計算得出，并且可能無解或有多個解。其語法格式是：

RATE(nper,pmt,pv,fv,type,guess)Nper為總投資期，即該項投資的付款期總數(shù)。

Pmt為各期付款額，其數(shù)值在整個投資期內(nèi)保持不變。通常pmt包括本金和利息，但不包括其他費用或稅金。如果忽略了pmt，則必須包含fv參數(shù)。

Pv為現(xiàn)值，即從該項投資開始計算時已經(jīng)入賬的款項，或一系列未來付款當前值的累積和，也稱為本金。

Fv為未來值，或在最后一次付款后希望得到的現(xiàn)金余額。如果省略fv，則假設(shè)其值為零（例如，一筆貸款的未來值即為零）。

Type數(shù)字0或1，用以指定各期的付款時間是在期初還是期末。

Guess為預(yù)期利率。如果省略預(yù)期利率，則假設(shè)該值為10%。RATE函數(shù)的作用返回年金的各期利率。函數(shù)RATE通設(shè)置月償還額計算公式在“目標單元格”編輯框中單擊，然后在工作表中單擊H6單元格，在“目標值”編輯框中輸入28000，在“可變單元格”編輯框中單擊，然后在工作表中單擊H7單元格顯示“單變量求解狀態(tài)”對話框，提示求得一個解，同時單元格H7中顯示單變量求解得到的年利率為7.73%，單擊“確定”按鈕即可。由此可見，使用函數(shù)和單變量求解的結(jié)果是一致的設(shè)置月償還額計算公式在“目標單元格”編輯框中單擊，然后在工（二）使用函數(shù)和單變量求解法逆算貸款額使用函數(shù)計算貸款總額創(chuàng)建逆算貸款額模型表格（二）使用函數(shù)和單變量求解法逆算貸款額使用函數(shù)計算貸款總額設(shè)置月償還額計算公式在“目標單元格”編輯框中單擊，然后在工作表中單擊H14單元格，在“目標值”編輯框中輸入28000，在“可變單元格”編輯框中單擊，然后在工作表中單擊H12單元格顯示“單變量求解狀態(tài)”對話框，提示求得一個解，同時單元格H12中顯示單變量求解得到的貸款額為2119653.89，單擊“確定”按鈕即可。由此可見，使用函數(shù)和單變量求解的結(jié)果是一致的設(shè)置月償還額計算公式在“目標單元格”編輯框中單擊，然后在工Excel2010

財務(wù)應(yīng)用案例教程Excel2010

財務(wù)應(yīng)用案例教程選擇最佳還貸方案貸款償還進度與完成比例分析第8章貸款償還分析一二逆算利率和貸款額三貸款一詞，在現(xiàn)在的社會并不陌生，如私人購房、購車，企業(yè)為擴大生產(chǎn)規(guī)模購買機器設(shè)備等，如何選擇最佳的貸款方式，如何對貸款的償還進度和完成比例進行分析操作，以及在知道貸款總額、貸款年限和每月可承受的還款額時如何求貸款利率等，本章將為您解答上述問題。。選擇最佳還貸方案貸款償還進度與完成比例分析第8章貸款償還一、選擇最佳還貸方案（一）創(chuàng)建還貸方案表并計算各方案月償還額（二）判斷還貸方案可行性并選擇最佳方案一、選擇最佳還貸方案（一）創(chuàng)建還貸方案表并計算各方案月償還額

假設(shè)銀行有兩種貸款方案供客戶選擇，一種是等額本息還貸法，另一種是等額本金還貸法。等額本息還貸法是指貸款人自還貸之日起，每個月向銀行償還的貸款數(shù)額相同。其特點是：整個還款期內(nèi)，每個月的還款額保持不變，貸款人可以準確掌握每月的還款額。但需注意的是雖然每個月還貸的數(shù)額相同，但其中包含的本金和利息的數(shù)額是不同的，這種還貸方法比較適合收入穩(wěn)定的家庭。等額本金還貸法是指貸款人每個月償還的貸款本金的數(shù)額是一定的，利息是變化的，呈現(xiàn)逐漸減少的趨勢，每月總的還款數(shù)額是遞減的，雖然每月還款額逐漸減少，但償還的速度是保持不變的。這種方式比較適合于有一定的積蓄、在還款初期能力較強、并希望在還款初期歸還較大款項來減少利息支出的借款人。假設(shè)某企業(yè)準備貸款150000元購買機器設(shè)備，貸款期限為3年，年利率為5.40%，假設(shè)企業(yè)每月可償還的貸款額最高限額為5000，如何為企業(yè)選擇最適合的貸款方案？假設(shè)銀行有兩種貸款方案供客戶選擇，一種是等額本息（一）創(chuàng)建還貸方案表并計算各方案月償還額在工作表中輸入已知貸款數(shù)據(jù)，制作兩種還貸方案表格（一）創(chuàng)建還貸方案表并計算各方案月償還額在工作表中輸入已知貸

利用PMT函數(shù)計算等額本息月還款額PMT函數(shù)即年金函數(shù)。它的功能是基于固定利率及等額分期付款方式，返回貸款的每期付款額。其語法格式是：

PMT(Rate,Nper,Pv,Fv,Type)

其中：

Rate為貸款利率。

Nper為該項貸款的付款總期數(shù)。

Pv為現(xiàn)值，或一系列未來付款的當前值的累積和，也稱為本金。

Fv為未來值，或在最后一次付款后希望得到的現(xiàn)金余額，如果省略Fv，則假設(shè)其值為零，也就是一筆貸款的未來值為零。

Type數(shù)字0或1，用以指定各期的付款時間是在期初還是期末。1代表期初，不輸入或輸入0代表期末。

利用PMT函數(shù)計算等額本息月還款額PMT函數(shù)即復(fù)制公式得到三年間每個月的還款額，由結(jié)果可知，等額本息還貸法每月的償還金額相等

復(fù)制公式得到三年間每個月的還款額，由結(jié)果可知，等額本息還貸法

下面計算“等額本金還貸法”中的各月利率。按照規(guī)定，月利率等于年利率除以12，日利率等于年利率除以360，這里的360指的是會計周期的360天，其他每月利率在30天的基礎(chǔ)上進行利息的加、減計算，即小于30天的減，大于30天的加。計算出月利率和日利率值下面計算“等額本金還貸法”中的各月利率。按照規(guī)定選擇正常會計周期的單元格，即每月有30天的月份所在單元格，然后輸入公式“=$G$6”，按【Ctrl+Enter】組合鍵即可得到所有正常會計周期的月利率按住【Ctrl】鍵的同時單擊表示大月的單元格，然后輸入公式“=$G$6+$G$7”，然后按【Ctrl+Enter】組合鍵即可得到所有大月的月利率選擇正常會計周期的單元格，即每月有30天的月份所在單元格，然由于2011年和2013年是平年，所以選中單元格G14和G38，然后輸入公式“=$G$6-($G$7*2)”，按【Ctrl+Enter】組合鍵即可得到這兩個年份小月的月利率在G26單元格輸入公式“=$G$6-$G$7”，按【Enter】鍵即可計算出2012年2月的月利率。至此，這三年的月利率都計算出來了在H13單元格輸入0，然后根據(jù)銀行提供的等額本金還款公式：每月還款額＝（貸款本金／還款月數(shù)）＋（貸款本金－累計已還本金）×月利率，計算等額本金第1個月償還額輸入公式計算累計已還款額，然后向下復(fù)制公式由于2011年和2013年是平年，所以選中單元格G14和G3復(fù)制I13單元格公式計算各月償還額兩種還貸方案的還款總額和最高月償還額復(fù)制I13單元格公式計算各月償還額兩種還貸方案的還款總額和（二）判斷還貸方案可行性并選擇最佳方案企業(yè)所能承受的月償還額最高限額為5000，故要據(jù)此來判斷方案的可行性根據(jù)總還款額確定最佳方案等額本金還貸法還款總額（161384.01）小于等額本息還款總額（162814.42），所以最佳還貸方案應(yīng)為等額本金還貸法

（二）判斷還貸方案可行性并選擇最佳方案企業(yè)所能承受的月償還額二、貸款償還進度與完成比例分析（一）創(chuàng)建還貸表并計算相關(guān)數(shù)據(jù)（二）創(chuàng)建貸款償還進度和計劃完成比例餅圖為擴大生產(chǎn)規(guī)模，企業(yè)所需資金有可能從銀行貸款，因此就存在貸款償還問題。企業(yè)為維護自己的信譽，會嚴格按合同約定償還貸款，在此期間，企業(yè)可以對貸款的償還進度和完成比例進行分析。假設(shè)某企業(yè)為擴大生產(chǎn)規(guī)模，于2014年1月1日申請貸款250000，貸款年利率為6.40%，貸款期限為5年，計劃在5年內(nèi)全額歸還貸款。下面，我們就根據(jù)企業(yè)在某一段時間內(nèi)實際還款的情況分析貸款償還完成進度和計劃完成情況。二、貸款償還進度與完成比例分析（一）創(chuàng)建還貸表并計算相關(guān)數(shù)據(jù)（一）創(chuàng)建還貸表并計算相關(guān)數(shù)據(jù)根據(jù)已知數(shù)據(jù)創(chuàng)建還貸表格輸入公式計算年、月償還額創(chuàng)建計劃還款表格模型輸入公式計算出2014年計劃還款總額輸入公式并向下復(fù)制計算2015年1~5月的月還款額（一）創(chuàng)建還貸表并計算相關(guān)數(shù)據(jù)根據(jù)已知數(shù)據(jù)創(chuàng)建還貸表格輸入輸入公式計算出截止2015年5月底計劃還款總額輸入實際還款額創(chuàng)建還款比例表格輸入公式計算已還本金和還款比例輸入公式計算出截止2015年5月底計劃還款總額輸入實際還款（二）創(chuàng)建貸款償還進度和計劃完成比例餅圖

下面我們創(chuàng)建貸款償還進度和計劃完成比例半圓餅圖圖表。首先創(chuàng)建圖表輔助表格，然后根據(jù)其中的數(shù)據(jù)創(chuàng)建餅圖圖表。創(chuàng)建輔助表格在單元格中輸入數(shù)據(jù)輸入公式進行計算（二）創(chuàng)建貸款償還進度和計劃完成比例餅圖下面我們

在餅圖左側(cè)系列3上右擊鼠標在餅圖左側(cè)系列3上右擊鼠標選中圖表左側(cè)的系列1用同樣的方法“計劃完成比例圖”選中圖表左側(cè)的系列1用同樣的方法“計劃完成比例圖”三、逆算利率和貸款額（一）使用函數(shù)和單變量求解法逆算利率（二）使用函數(shù)和單變量求解法逆算貸款額逆算利率是指當知道貸款總額、貸款年限和每月可承受的還款額時，利用函數(shù)求貸款利率；逆算貸款額是指已知貸款年限、貸款利率和每月可承受的還款額時，利用函數(shù)求貸款總額。逆算利率和貸款額有兩種方法：函數(shù)法和單變量求解法。假設(shè)企業(yè)的貸款總額為200萬元，貸款期限為8年，每月可承受的最高還款額為28000元，如何逆算其年利率？或已知其貸款利率為6.55%，貸款期限為8年，每月可承受的最高還款額為28000元，如何對其逆算貸款額？三、逆算利率和貸款額（一）使用函數(shù)和單變量求解法逆算利率（二（一）使用函數(shù)和單變量求解法逆算利率制作的逆算利率模型表格利用函數(shù)計算年利率（一）使用函數(shù)和單變量求解法逆算利率制作的逆算利率模型表格RATE函數(shù)的作用返回年金的各期利率。函數(shù)RATE通過迭代法計算得出，并且可能無解或有多個解。其語法格式是：

RATE(nper,pmt,pv