贷款偿还分析课件

Excel2010

财务应用案例教程Excel2010

财务应用案例教程选择最佳还贷方案贷款偿还进度与完成比例分析第8章贷款偿还分析一二逆算利率和贷款额三贷款一词，在现在的社会并不陌生，如私人购房、购车，企业为扩大生产规模购买机器设备等，如何选择最佳的贷款方式，如何对贷款的偿还进度和完成比例进行分析操作，以及在知道贷款总额、贷款年限和每月可承受的还款额时如何求贷款利率等，本章将为您解答上述问题。。选择最佳还贷方案贷款偿还进度与完成比例分析第8章贷款偿还一、选择最佳还贷方案（一）创建还贷方案表并计算各方案月偿还额（二）判断还贷方案可行性并选择最佳方案一、选择最佳还贷方案（一）创建还贷方案表并计算各方案月偿还额

假设银行有两种贷款方案供客户选择，一种是等额本息还贷法，另一种是等额本金还贷法。等额本息还贷法是指贷款人自还贷之日起，每个月向银行偿还的贷款数额相同。其特点是：整个还款期内，每个月的还款额保持不变，贷款人可以准确掌握每月的还款额。但需注意的是虽然每个月还贷的数额相同，但其中包含的本金和利息的数额是不同的，这种还贷方法比较适合收入稳定的家庭。等额本金还贷法是指贷款人每个月偿还的贷款本金的数额是一定的，利息是变化的，呈现逐渐减少的趋势，每月总的还款数额是递减的，虽然每月还款额逐渐减少，但偿还的速度是保持不变的。这种方式比较适合于有一定的积蓄、在还款初期能力较强、并希望在还款初期归还较大款项来减少利息支出的借款人。假设某企业准备贷款150000元购买机器设备，贷款期限为3年，年利率为5.40%，假设企业每月可偿还的贷款额最高限额为5000，如何为企业选择最适合的贷款方案？假设银行有两种贷款方案供客户选择，一种是等额本息（一）创建还贷方案表并计算各方案月偿还额在工作表中输入已知贷款数据，制作两种还贷方案表格（一）创建还贷方案表并计算各方案月偿还额在工作表中输入已知贷

利用PMT函数计算等额本息月还款额PMT函数即年金函数。它的功能是基于固定利率及等额分期付款方式，返回贷款的每期付款额。其语法格式是：

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

其中：

Rate为贷款利率。

Nper为该项贷款的付款总期数。

Pv为现值，或一系列未来付款的当前值的累积和，也称为本金。

Fv为未来值，或在最后一次付款后希望得到的现金余额，如果省略Fv，则假设其值为零，也就是一笔贷款的未来值为零。

Type数字0或1，用以指定各期的付款时间是在期初还是期末。1代表期初，不输入或输入0代表期末。

利用PMT函数计算等额本息月还款额PMT函数即复制公式得到三年间每个月的还款额，由结果可知，等额本息还贷法每月的偿还金额相等

复制公式得到三年间每个月的还款额，由结果可知，等额本息还贷法

下面计算“等额本金还贷法”中的各月利率。按照规定，月利率等于年利率除以12，日利率等于年利率除以360，这里的360指的是会计周期的360天，其他每月利率在30天的基础上进行利息的加、减计算，即小于30天的减，大于30天的加。计算出月利率和日利率值下面计算“等额本金还贷法”中的各月利率。按照规定选择正常会计周期的单元格，即每月有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，然后根据银行提供的等额本金还款公式：每月还款额＝（贷款本金／还款月数）＋（贷款本金－累计已还本金）×月利率，计算等额本金第1个月偿还额输入公式计算累计已还款额，然后向下复制公式由于2011年和2013年是平年，所以选中单元格G14和G3复制I13单元格公式计算各月偿还额两种还贷方案的还款总额和最高月偿还额复制I13单元格公式计算各月偿还额两种还贷方案的还款总额和（二）判断还贷方案可行性并选择最佳方案企业所能承受的月偿还额最高限额为5000，故要据此来判断方案的可行性根据总还款额确定最佳方案等额本金还贷法还款总额（161384.01）小于等额本息还款总额（162814.42），所以最佳还贷方案应为等额本金还贷法

（二）判断还贷方案可行性并选择最佳方案企业所能承受的月偿还额二、贷款偿还进度与完成比例分析（一）创建还贷表并计算相关数据（二）创建贷款偿还进度和计划完成比例饼图为扩大生产规模，企业所需资金有可能从银行贷款，因此就存在贷款偿还问题。企业为维护自己的信誉，会严格按合同约定偿还贷款，在此期间，企业可以对贷款的偿还进度和完成比例进行分析。假设某企业为扩大生产规模，于2014年1月1日申请贷款250000，贷款年利率为6.40%，贷款期限为5年，计划在5年内全额归还贷款。下面，我们就根据企业在某一段时间内实际还款的情况分析贷款偿还完成进度和计划完成情况。二、贷款偿还进度与完成比例分析（一）创建还贷表并计算相关数据（一）创建还贷表并计算相关数据根据已知数据创建还贷表格输入公式计算年、月偿还额创建计划还款表格模型输入公式计算出2014年计划还款总额输入公式并向下复制计算2015年1~5月的月还款额（一）创建还贷表并计算相关数据根据已知数据创建还贷表格输入输入公式计算出截止2015年5月底计划还款总额输入实际还款额创建还款比例表格输入公式计算已还本金和还款比例输入公式计算出截止2015年5月底计划还款总额输入实际还款（二）创建贷款偿还进度和计划完成比例饼图

下面我们创建贷款偿还进度和计划完成比例半圆饼图图表。首先创建图表辅助表格，然后根据其中的数据创建饼图图表。创建辅助表格在单元格中输入数据输入公式进行计算（二）创建贷款偿还进度和计划完成比例饼图下面我们

在饼图左侧系列3上右击鼠标在饼图左侧系列3上右击鼠标选中图表左侧的系列1用同样的方法“计划完成比例图”选中图表左侧的系列1用同样的方法“计划完成比例图”三、逆算利率和贷款额（一）使用函数和单变量求解法逆算利率（二）使用函数和单变量求解法逆算贷款额逆算利率是指当知道贷款总额、贷款年限和每月可承受的还款额时，利用函数求贷款利率；逆算贷款额是指已知贷款年限、贷款利率和每月可承受的还款额时，利用函数求贷款总额。逆算利率和贷款额有两种方法：函数法和单变量求解法。假设企业的贷款总额为200万元，贷款期限为8年，每月可承受的最高还款额为28000元，如何逆算其年利率？或已知其贷款利率为6.55%，贷款期限为8年，每月可承受的最高还款额为28000元，如何对其逆算贷款额？三、逆算利率和贷款额（一）使用函数和单变量求解法逆算利率（二（一）使用函数和单变量求解法逆算利率制作的逆算利率模型表格利用函数计算年利率（一）使用函数和单变量求解法逆算利率制作的逆算利率模型表格RATE函数的作用返回年金的各期利率。函数RATE通过迭代法计算得出，并且可能无解或有多个解。其语法格式是：

RATE(nper,pmt,pv,fv,type,guess)Nper为总投资期，即该项投资的付款期总数。

Pmt为各期付款额，其数值在整个投资期内保持不变。通常pmt包括本金和利息，但不包括其他费用或税金。如果忽略了pmt，则必须包含fv参数。

Pv为现值，即从该项投资开始计算时已经入账的款项，或一系列未来付款当前值的累积和，也称为本金。

Fv为未来值，或在最后一次付款后希望得到的现金余额。如果省略fv，则假设其值为零（例如，一笔贷款的未来值即为零）。

Type数字0或1，用以指定各期的付款时间是在期初还是期末。

Guess为预期利率。如果省略预期利率，则假设该值为10%。RATE函数的作用返回年金的各期利率。函数RATE通设置月偿还额计算公式在“目标单元格”编辑框中单击，然后在工作表中单击H6单元格，在“目标值”编辑框中输入28000，在“可变单元格”编辑框中单击，然后在工作表中单击H7单元格显示“单变量求解状态”对话框，提示求得一个解，同时单元格H7中显示单变量求解得到的年利率为7.73%，单击“确定”按钮即可。由此可见，使用函数和单变量求解的结果是一致的设置月偿还额计算公式在“目标单元格”编辑框中单击，然后在工（二）使用函数和单变量求解法逆算贷款额使用函数计算贷款总额创建逆算贷款额模型表格（二）使用函数和单变量求解法逆算贷款额使用函数计算贷款总额设置月偿还额计算公式在“目标单元格”编辑框中单击，然后在工作表中单击H14单元格，在“目标值”编辑框中输入28000，在“可变单元格”编辑框中单击，然后在工作表中单击H12单元格显示“单变量求解状态”对话框，提示求得一个解，同时单元格H12中显示单变量求解得到的贷款额为2119653.89，单击“确定”按钮即可。由此可见，使用函数和单变量求解的结果是一致的设置月偿还额计算公式在“目标单元格”编辑框中单击，然后在工Excel2010

财务应用案例教程Excel2010

财务应用案例教程选择最佳还贷方案贷款偿还进度与完成比例分析第8章贷款偿还分析一二逆算利率和贷款额三贷款一词，在现在的社会并不陌生，如私人购房、购车，企业为扩大生产规模购买机器设备等，如何选择最佳的贷款方式，如何对贷款的偿还进度和完成比例进行分析操作，以及在知道贷款总额、贷款年限和每月可承受的还款额时如何求贷款利率等，本章将为您解答上述问题。。选择最佳还贷方案贷款偿还进度与完成比例分析第8章贷款偿还一、选择最佳还贷方案（一）创建还贷方案表并计算各方案月偿还额（二）判断还贷方案可行性并选择最佳方案一、选择最佳还贷方案（一）创建还贷方案表并计算各方案月偿还额

假设银行有两种贷款方案供客户选择，一种是等额本息还贷法，另一种是等额本金还贷法。等额本息还贷法是指贷款人自还贷之日起，每个月向银行偿还的贷款数额相同。其特点是：整个还款期内，每个月的还款额保持不变，贷款人可以准确掌握每月的还款额。但需注意的是虽然每个月还贷的数额相同，但其中包含的本金和利息的数额是不同的，这种还贷方法比较适合收入稳定的家庭。等额本金还贷法是指贷款人每个月偿还的贷款本金的数额是一定的，利息是变化的，呈现逐渐减少的趋势，每月总的还款数额是递减的，虽然每月还款额逐渐减少，但偿还的速度是保持不变的。这种方式比较适合于有一定的积蓄、在还款初期能力较强、并希望在还款初期归还较大款项来减少利息支出的借款人。假设某企业准备贷款150000元购买机器设备，贷款期限为3年，年利率为5.40%，假设企业每月可偿还的贷款额最高限额为5000，如何为企业选择最适合的贷款方案？假设银行有两种贷款方案供客户选择，一种是等额本息（一）创建还贷方案表并计算各方案月偿还额在工作表中输入已知贷款数据，制作两种还贷方案表格（一）创建还贷方案表并计算各方案月偿还额在工作表中输入已知贷

利用PMT函数计算等额本息月还款额PMT函数即年金函数。它的功能是基于固定利率及等额分期付款方式，返回贷款的每期付款额。其语法格式是：

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

其中：

Rate为贷款利率。

Nper为该项贷款的付款总期数。

Pv为现值，或一系列未来付款的当前值的累积和，也称为本金。

Fv为未来值，或在最后一次付款后希望得到的现金余额，如果省略Fv，则假设其值为零，也就是一笔贷款的未来值为零。

Type数字0或1，用以指定各期的付款时间是在期初还是期末。1代表期初，不输入或输入0代表期末。

利用PMT函数计算等额本息月还款额PMT函数即复制公式得到三年间每个月的还款额，由结果可知，等额本息还贷法每月的偿还金额相等

复制公式得到三年间每个月的还款额，由结果可知，等额本息还贷法

下面计算“等额本金还贷法”中的各月利率。按照规定，月利率等于年利率除以12，日利率等于年利率除以360，这里的360指的是会计周期的360天，其他每月利率在30天的基础上进行利息的加、减计算，即小于30天的减，大于30天的加。计算出月利率和日利率值下面计算“等额本金还贷法”中的各月利率。按照规定选择正常会计周期的单元格，即每月有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，然后根据银行提供的等额本金还款公式：每月还款额＝（贷款本金／还款月数）＋（贷款本金－累计已还本金）×月利率，计算等额本金第1个月偿还额输入公式计算累计已还款额，然后向下复制公式由于2011年和2013年是平年，所以选中单元格G14和G3复制I13单元格公式计算各月偿还额两种还贷方案的还款总额和最高月偿还额复制I13单元格公式计算各月偿还额两种还贷方案的还款总额和（二）判断还贷方案可行性并选择最佳方案企业所能承受的月偿还额最高限额为5000，故要据此来判断方案的可行性根据总还款额确定最佳方案等额本金还贷法还款总额（161384.01）小于等额本息还款总额（162814.42），所以最佳还贷方案应为等额本金还贷法

（二）判断还贷方案可行性并选择最佳方案企业所能承受的月偿还额二、贷款偿还进度与完成比例分析（一）创建还贷表并计算相关数据（二）创建贷款偿还进度和计划完成比例饼图为扩大生产规模，企业所需资金有可能从银行贷款，因此就存在贷款偿还问题。企业为维护自己的信誉，会严格按合同约定偿还贷款，在此期间，企业可以对贷款的偿还进度和完成比例进行分析。假设某企业为扩大生产规模，于2014年1月1日申请贷款250000，贷款年利率为6.40%，贷款期限为5年，计划在5年内全额归还贷款。下面，我们就根据企业在某一段时间内实际还款的情况分析贷款偿还完成进度和计划完成情况。二、贷款偿还进度与完成比例分析（一）创建还贷表并计算相关数据（一）创建还贷表并计算相关数据根据已知数据创建还贷表格输入公式计算年、月偿还额创建计划还款表格模型输入公式计算出2014年计划还款总额输入公式并向下复制计算2015年1~5月的月还款额（一）创建还贷表并计算相关数据根据已知数据创建还贷表格输入输入公式计算出截止2015年5月底计划还款总额输入实际还款额创建还款比例表格输入公式计算已还本金和还款比例输入公式计算出截止2015年5月底计划还款总额输入实际还款（二）创建贷款偿还进度和计划完成比例饼图

下面我们创建贷款偿还进度和计划完成比例半圆饼图图表。首先创建图表辅助表格，然后根据其中的数据创建饼图图表。创建辅助表格在单元格中输入数据输入公式进行计算（二）创建贷款偿还进度和计划完成比例饼图下面我们

在饼图左侧系列3上右击鼠标在饼图左侧系列3上右击鼠标选中图表左侧的系列1用同样的方法“计划完成比例图”选中图表左侧的系列1用同样的方法“计划完成比例图”三、逆算利率和贷款额（一）使用函数和单变量求解法逆算利率（二）使用函数和单变量求解法逆算贷款额逆算利率是指当知道贷款总额、贷款年限和每月可承受的还款额时，利用函数求贷款利率；逆算贷款额是指已知贷款年限、贷款利率和每月可承受的还款额时，利用函数求贷款总额。逆算利率和贷款额有两种方法：函数法和单变量求解法。假设企业的贷款总额为200万元，贷款期限为8年，每月可承受的最高还款额为28000元，如何逆算其年利率？或已知其贷款利率为6.55%，贷款期限为8年，每月可承受的最高还款额为28000元，如何对其逆算贷款额？三、逆算利率和贷款额（一）使用函数和单变量求解法逆算利率（二（一）使用函数和单变量求解法逆算利率制作的逆算利率模型表格利用函数计算年利率（一）使用函数和单变量求解法逆算利率制作的逆算利率模型表格RATE函数的作用返回年金的各期利率。函数RATE通过迭代法计算得出，并且可能无解或有多个解。其语法格式是：

RATE(nper,pmt,pv