EXCEL 如何制作瀑布图,对于有些数据做图表非常适合用瀑布图，比如生活开支......
Excel：等额本金房贷、等额本息房贷
来源：互联网
2023-02-20 18:50:40 118
房贷有等额本金和等额本息两种还款方式。等额本金还款，每月还的金额不一致，第一月最多，以后逐月减少；等额本息还款，每月还的金额一样。哪种划算？今天我们将用PMT()函数、PPMT()函数、IPMT()函数、CUMIPMT()函数、CUMPRINC()函数来带大家了解贷款的那些事！赶紧来看看吧~
方法/步骤
1，等额本金法每期还款额的计算
等额本金还款的特点是每个月归还相同的本金，也就是将贷款本金平均分在每一期还款中。每月归还的本金为贷款总额÷还款期数。
等额本金每个月要归还的利息是贷款余额在上一个月所产生的利息。也就是说，随着还款的进行，所欠的贷款越来越少，那么每个月需要还给银行的利息就越来越少。
举个例子，某人以5.25%的利率贷款50万元，计划以等额本金的方式分5年还清，要计算他每月需要还款的总金额，以及其中包括的本金和利息。具体步骤如下：
①合并A6:D6单元格，输入“还款明细数据”，在A7:D7单元格分别输入“期次”“还款额”“本金”“利息”，在A8单元格输入数字“1”，单击鼠标右键，打开“设置单元格格式”对话框，在“自定义”选项卡中设置单元格格式代码为"第"00"期"。这里的“第01期”只是一个障眼法，当选择A8单元格后，在编辑栏中即可看到它的真实值，实际上是数字1，而非文本“第01期”。选中A8单元格，按住Ctrl键，向下填充公式至A67单元格。
②在C8单元格中输入公式“=$A$3/$C$3/12”，双击C8单元格右下角的自动填充柄，向下填充公式至C67单元格，可以得到各期还款额的本金。该公式用于计算单元格对应的还款期次的还款本金，每个月应该归还的本金就是50÷5÷12≈0.83万元。
③在D8单元格中输入公式“=($A$3-(A8-1)*C8)*$B$3/12”，双击D8单元格右下角的自动填充柄，向下填充公式至D67单元格，可以得到各期还款额的利息。“$A$3-(A8-1)*C8”为贷款余额，“$B$3/12”可以将给定的年利率转化为月利息。
④在B8单元格中输入公式“=C8 D8”，双击B8单元格右下角的自动填充柄，向下填充公式至B67单元格，可以得到各期总还款额。总还款额为本金与利息之和。
从表中可以看出等额本金的还款方式，每个月的还款本金是相同的，但是利息会递减。最初每月还款比较多，越到后来贷款余额越少，利息越少，每月还款额逐渐减少。
2，等额本息法中每期还款额的计算
等额本息法分期付款是指在欠款偿还期间，每期支付相等的金额，直到所有欠款还清，如果要计算等额本息法中每一期应还的金额，可以使用PMT()函数。该函数的语法格式为：
PMT(rate,nper,pv,[fv],[type])
PMT()包含3个必选参数rate、nper和pv，以及两个可选参数fv和type，各参数意义如下：
Ørate：各期利率的数字，通常以百分比形式出现，在整个贷款期间保持不变。
Ønper：贷款所需偿还的总次数，通常以月为单位。
Øpv：在此函数中表示贷款的总额。
Øfv：在此函数中表示最后一次还款后的剩余金额（通常为0，表示全部还清）。
Øtype：指定各期的付款时间是在期初（用数字1表示）还是期末（用数字0表示）。
如果要计算每期还款额中包含的本金，可以使用PPMT()函数来完成，其语法格式为：
PPMT(rate,per,nper,pv,[fv],[type])
如果要计算在相同条件下，每期还款额中包含的利息金额，则可以使用IPMT()函数来完成，其语法格式为：
IPMT(rate,per,nper,pv,[fv],[type])
从这两个函数的语法格式上可以看出，它们的语法结构完全相同。都包含4个必选参数rate、per、nper和pv，以及两个可选参数fv和type，各参数意义如下：
rate：贷款或投资期间的固定利率。
per：要计算本金（或利息）的期次，必须在1-nper之间。
nper：付款总期数。
pv：在这两个函数中可表示贷款的总额（或投资本金）。
fv：最后一次付款后剩余金额（通常为0）。
type：指定各期的付款时间是在期初（用数字1表示）还是期末（用数字0表示）。还是用上个例子，某人以5.25%的利率贷款50万元，计划以等额本息的方式分5年还清，要计算他每月需要还款的总金额，以及其中包括的本金和利息。
计算等额本息还款法中各期的还款额，可以使用PMT()函数，计算还款金额中包含的本金，可使用PPMT()函数，而在得到本金后，可以通过还款额-本金的方法得到其中包含的利息，也可以直接使用IPMT()函数来取得。具体步骤如下：
①与等额本金法的步骤①一致，搭建数据基础框架。
②在C7单元格输入公式“=PMT($C$3/12,$D$3*12,-$B$3)”，双击C7单元格右下角的自动填充柄，向下填充公式至C66单元格，可以得到各期还款总额。“$C$3/12”为各期利率数据，“$D$3*12”为总期数，“-$B$3”为贷款总额。这里为了得到正数的本金数字，所以给贷款总额添加了负号。
③在D7单元格中输入公式“=PPMT($C$3/12,B7,$D$3*12,-$B$3)”，双击D7单元格右下角的自动填充柄，向下填充公式至D66单元格，可以得到各期还款额的本金。该公式用于求得单元格对应的还款期次中的还款本金，“$C$3/12”和“$D$3*12”分别代表将给定的年利率转化为月利息，以及将还款年限转化为还款月数。这里为了得到正数的本金数字，所以给B3单元格的值添加了负号。
④在E7单元格中输入公式“=IPMT($C$3/12,B7,$D$3*12,-$B$3)”，双击E7单元格右下角的自动填充柄，向下填充公式至E66单元格，可以得到各期还款额的利息。该公式与上一个公式的参数完全相同，但求取的结果是该还款期次中的利息。
在固定利率的情况下，如果采用等额本息法还款，在每一期的还款金额中，利息在不断减少，而本金会不断增加。在表中汇总一下向银行支付的还款总额，可以看出等额本金还款支出的总额更少。如果不考虑通货膨胀的影响，等额本金还款方式更划算。
无论是等额本息还是等额本金的还款方式，每个月的还款都由本金和利息两部分构成，其中的利息是未归还的贷款金额在上一个月的时间内产生的利息。在任何时候提前还款，都会减少后续利息的产生，所以仅从利息角度看，提前还款并不会吃亏。提前还款的的确确可以减少利息，但是如果算上有些银行对提前还款收取违约金、通货膨胀等因素，提前还款不见得合算。
3，信用卡消费的猫腻
随着信用卡越来越流行，很多年轻人都养成了提前消费的习惯，当消费达到一定金额后，银行就可能会提供免息分期付款业务，但这里面却有一些猫腻。首先这里的免息确实是不需要利息，但却多出了一项手续费率，其实与利息的作用是相同的，并且普遍高于银行同期贷款利息。
在信用卡等额分期还款中，银行计算的每期还款额公式为：分期总额/还款期数 分期总额*各期手续费率。
从计算公式中可以看出每月所还的利息并不会随着还入本金的增加而减少。例如同样的50万元分5年还，手续费率为5.25%，使用PMT()函数计算的各期还款额和使用信用卡分期还款公式计算的各期还款额并不相等。
从图中可以看出，在相同的贷款金额、利率和还款期限下，信用卡分期还款的各期还款额明显高于银行贷款的还款额，这是因为在信用卡分期中，并未考虑货币的时间价值，及已经还入的本金依然在被记利息，即从开始还款到还款结束，要支付的利息始终都是贷款原始金额的利息。而正常的财务处理中，已经还款的本金是不能被计入利息的（如PMT函数计算的结果），而信用卡分期主要就是赚取客户的这一部分差额，这也是为什么信用卡可以直接贷款，而向银行贷款手续非常麻烦的原因。
付款期间的本金与利息的关系
在等额本息法分期付款的借贷行为中，如果要得到两次付款期间产生的总利息，可以使用CUMIPMT()函数来完成，其语法格式为：
CUMIPMT（rate,nper,pv,start_period,end_period,type）
同样的条件下，如果要计算两次还款期间内总共偿还的本金，则可以使用CUMPRINC()函数来完成，其语法结构为：
CUMPRINC（rate,nper,pv,start_period,end_period,type）
从两个函数的语法格式上可以看出，两个函数的参数完全相同，都包含相同的6个必选参数，各参数意义如下：
Ørate：支付期间的固定利率。
Ønper：付款总期数。
Øpv：在这两个函数中可表示贷款的总额（或投资本金）。
ØStart_period：计算利息或本金的首期，总付款基数从1开始计数。
Øend_period：计算利息或本金的末期。
Øtype：指定付款时间是在期初（用数字1表示）还是期末（用数字0表示）。
在使用这两个函数的过程中，各参数需要遵循如下几个规则：
①函数的rate和nper必须具有相同的单位，如年利率对应的期限为年，月利率对应的期限为月。
②rate，nper，pv都必须大于等于1，否则函数返回错误值#NUM!。
③start_period，end_period都必须大于等于1，且start_period不能大于end_period。
④type参数只能是0或1，若是其他值，则函数返回错误值#NUM!。举个例子，某信贷公司想快速计算用户在任意期间所需偿还的利息和本金，可以通过EXCEL函数来完成。
要计算两个指定期间的累积利息和本金，可分别是使用CUMIPMT()函数和CUMPRINC()函数求得。具体步骤如下：
①输入基础数据，选中C5、E5单元格，打开“设置单元格格式”对话框，在“自定义”选项卡中设置单元格格式代码为"第"00"期"。
②在“开发工具”选项卡下点击“插入”，将“表单控件”下的数值调节钮（窗体控件），绘制到C5单元格右侧，右击“设置控件格式”，将最小值设置为“1”，因为还款次数共60次，所以最大值设置为“60”，步长为“1”，单元格链接为“$C$5”，点击“确定”按钮。按照同样的方法在E7单元格右侧插入数值调节钮，唯一不同的是单元格链接为“$E$5”。
③选中C6单元格，输入公式“=IFERROR(ABS(CUMIPMT(D3/12,E3,B3,C5,E5,0)),"参数错误")”，按Enter键确认。首先通过函数求得该项贷款从C5单元格指定的期数至E5单元格指定的期数之间应支付的利息，再用ABS()函数对返回的结果取绝对值，最后用IFERROR()函数来检测错误，并根据情况返回具体数值或返回“参数错误”文本。
④选中E6单元格，输入公式“=IFERROR(ABS(CUMPRINC(D3/12,E3,B3,C5,E5,0)),"参数错误")”，按Enter键确认。第二个公式参数的组成和作用与第一个公式基本相同，用于求得相同时间所需偿还的本金。两个函数求得的结果都为负值，可使用ABS()函数返回其绝对值，以显示正数。由于函数的任意一个参数错误都会导致函数返回错误值，可使用IFERROR()函数来检测错误并返回一些说明文本。
个人建议
最终效果如图所示。
以上方法由办公区教程网编辑摘抄自百度经验可供大家参考！
上一篇：Excel：函数MOD的用法 下一篇：Excel：小技巧解决大麻烦—跨列居中
