更新： 2015-11-02 17:05 我们知道银行贷款的按揭还贷，主要分为等额本息、等额本金两种还款方式。其中等额本息是每月还款固定金额，等额本金是逐月递减（总利息最少）。之前，小乐分享给大家如何使用Windows计算器简单速算等额本息每月还款金额，本文结合公积金贷款、商业贷款，等多种复合条件，分享给大家如何使用身边的Excel，根据不同情况，详细计算每月贷款还款额度。 使用Excel设置贷款计算器模板标题 1 在Excel中，我们先建一张如下图模板的表格。 颜色说明： 黄色背景单元格，用来自己填写数据。 绿色背景的单元格，一般也需要自己填好。 橙色背景的单元格，是我们着重关注的贷款每月还款金额。 其他单元格，一般都是让公式自动计算生成数据。 在Excel中，我们先建一张如下图模板的表格。 黄色背景单元格，用来自己填写数据。 绿色背景的单元格，一般也需要自己填好。 橙色背景的单元格，是我们着重关注的贷款每月还款金额。 其他单元格，一般都是让公式自动计算生成数据。 2 第1列，在单元格A4使用下列公式填写年月。 =EDATE("2015-11-2",ROW()-3) 其中2015-11-2，需修改为贷款实际开始还款的那个月，当中的任意一天。 注意： 这里使用公式，而不是直接填写年份月份，是为了方便往下拉公式，实现自动填充。 第1列，在单元格A4使用下列公式填写年月。 =EDATE("2015-11-2",ROW()-3) 其中2015-11-2，需修改为贷款实际开始还款的那个月，当中的任意一天。 这里使用公式，而不是直接填写年份月份，是为了方便往下拉公式，实现自动填充。 3 右击单元格，设置单元格格式。 4 在自定义中，修改为yyyy-m，这样的格式。 在自定义中，修改为yyyy-m，这样的格式。 5 第1行，在单元格D1使用公式 ="年限（"&E1*12&"期)" 在单元格H1使用公式 ="打折（实际为"&TEXT(G1*I1,"0.00%")&"）" 在单元格L1使用公式 ="总利息："&ROUND(C1*IF(K1=1,G1*I1/12*(E1*12+1)/2,G1*I1/12/(1-1/(1+G1*I1/12)^(E1*12))*E1*12-1),2)&" 相当于本金的"&ROUND(IF(K1=1,G1*I1/12*(E1*12+1)/2,G1*I1/12/(1-1/(1+G1*I1/12)^(E1*12))*E1*12-1),2)&"倍" ="年限（"&E1*12&"期)" ="打折（实际为"&TEXT(G1*I1,"0.00%")&"）" ="总利息："&ROUND(C1*IF(K1=1,G1*I1/12*(E1*12+1)/2,G1*I1/12/(1-1/(1+G1*I1/12)^(E1*12))*E1*12-1),2)&" 相当于本金的"&ROUND(IF(K1=1,G1*I1/12*(E1*12+1)/2,G1*I1/12/(1-1/(1+G1*I1/12)^(E1*12))*E1*12-1),2)&"倍" 6 然后将上述3个单元格，分别往下拉到第2行（复制公式） 这样按揭贷款计算器的标题，即制作完毕。 END 然后将上述3个单元格，分别往下拉到第2行（复制公式） 这样按揭贷款计算器的标题，即制作完毕。 使用公式计算公积金贷款每月还贷金额、本金、利息 1 计算公积金逐月还贷，每月需还款的金额： 在单元格B4，使用公式 =ROUND(IF($K$1=1,$C$1/$E$1/12+$C$1*(1-((ROW()-4)/$E$1/12))*$G$1/12,$C$1*$G$1*$I$1/12/(1-1/(1+$G$1*$I$1/12)^($E$1*12))),2) 计算公积金逐月还贷，每月需还款的金额： =ROUND(IF($K$1=1,$C$1/$E$1/12+$C$1*(1-((ROW()-4)/$E$1/12))*$G$1/12,$C$1*$G$1*$I$1/12/(1-1/(1+$G$1*$I$1/12)^($E$1*12))),2) 2 为了计算公积金等额本金或者等额本息，每期还款时，贷款本金和贷款利息分别还了多少，我们在单元格C4，输入公式： ="本金："&ROUND(IF($K$1=1,$C$1/$E$1/12,-($C$1*$G$1*$I$1/12-B4)*(1+$G$1*$I$1/12)^(ROW()-4)),2)&" 利息："&ROUND(IF($K$1=1,$C$1*(1-((ROW()-4)/$E$1/12))*$G$1*$I$1/12,($C$1*$G$1*$I$1/12-B4)*(1+$G$1*$I$1/12)^(ROW()-4)+B4),2) 为了计算公积金等额本金或者等额本息，每期还款时，贷款本金和贷款利息分别还了多少，我们在单元格C4，输入公式： ="本金："&ROUND(IF($K$1=1,$C$1/$E$1/12,-($C$1*$G$1*$I$1/12-B4)*(1+$G$1*$I$1/12)^(ROW()-4)),2)&" 利息："&ROUND(IF($K$1=1,$C$1*(1-((ROW()-4)/$E$1/12))*$G$1*$I$1/12,($C$1*$G$1*$I$1/12-B4)*(1+$G$1*$I$1/12)^(ROW()-4)+B4),2) 3 公积金月缴额（单位+个人），是需要自己填写的，因为因人而异， 注意，需要填写公积金月缴总金额，包含单位交的和个人交的。 然后在单元格F4，输入公式 =B4-D4 即可立即得到，每月需要实际准备的现金，用于还公积金贷款。 如果结果为负值，说明你的公积金足够多，根本不需要另外准备现金或打款到贷款银行账户。 END 公积金月缴额（单位+个人），是需要自己填写的，因为因人而异， 注意，需要填写公积金月缴总金额，包含单位交的和个人交的。 即可立即得到，每月需要实际准备的现金，用于还公积金贷款。 如果结果为负值，说明你的公积金足够多，根本不需要另外准备现金或打款到贷款银行账户。 使用公式计算商业贷款每月还贷金额、本金、利息 1 类似地，我们在单元格H4使用公式 =ROUND(IF($K$2=1,$C$2/$E$2/12+$C$2*(1-((ROW()-4)/$E$2/12))*$G$2/12,$C$2*$G$2*$I$2/12/(1-1/(1+$G$2*$I$2/12)^($E$2*12))),2) 来计算逐月按揭商业贷款，需要每月还款多少。 类似地，我们在单元格H4使用公式 =ROUND(IF($K$2=1,$C$2/$E$2/12+$C$2*(1-((ROW()-4)/$E$2/12))*$G$2/12,$C$2*$G$2*$I$2/12/(1-1/(1+$G$2*$I$2/12)^($E$2*12))),2) 来计算逐月按揭商业贷款，需要每月还款多少。 2 然后在单元格J4，输入公式 ="本金："&ROUND(IF($K$2=1,$C$2/$E$2/12,-($C$2*$G$2*$I$2/12-H4)*(1+$G$2*$I$2/12)^(ROW()-4)),2)&" 利息："&ROUND(IF($K$2=1,$C$2*(1-((ROW()-4)/$E$2/12))*$G$2*$I$2/12,($C$2*$G$2*$I$2/12-H4)*(1+$G$2*$I$2/12)^(ROW()-4)+H4),2) 来计算商贷，每月还贷本金和利息的明细。 ="本金："&ROUND(IF($K$2=1,$C$2/$E$2/12,-($C$2*$G$2*$I$2/12-H4)*(1+$G$2*$I$2/12)^(ROW()-4)),2)&" 利息："&ROUND(IF($K$2=1,$C$2*(1-((ROW()-4)/$E$2/12))*$G$2*$I$2/12,($C$2*$G$2*$I$2/12-H4)*(1+$G$2*$I$2/12)^(ROW()-4)+H4),2) 来计算商贷，每月还贷本金和利息的明细。 3 至此，我们已经完成了所有的公式制作， 直接选中第4行的相应单元格，往下拉公式 即可实现快速计算每月贷款还款金额。 END 至此，我们已经完成了所有的公式制作， 直接选中第4行的相应单元格，往下拉公式 即可实现快速计算每月贷款还款金额。

Excel贷款计算器使用方法与金融数学原理 1 最后，我们总结一下，刚刚使用Excel制作的贷款计算器的具体使用方法与原理。 使用方法很简单，直接按照图示7个步骤，填好数据和公式，最后往下拉即可。 最后，我们总结一下，刚刚使用Excel制作的贷款计算器的具体使用方法与原理。 使用方法很简单，直接按照图示7个步骤，填好数据和公式，最后往下拉即可。 2 为了兼顾需要深究钻研的朋友，本文最后补充一下等额本息、等额本金，每月还款本金、利息的计算原理与方法。 等额本金，顾名思义，就是每月还款中，包含的还款本金都一样。 只不过，由于欠银行的贷款本金逐月减少，那么相应的利息也逐月减少。 因此，会出现逐月还款后，所还的金额越来越少的现象（逐月递减）。 假设贷款总金额a，年利率P，年限n（共n*12个月，即分为n*12期按揭）， 月利率p=P/12 具体等额本金的计算公式（第i个月）： 每月还款含本金（都相等）：a/(12n) 每月还款含利息（逐月递减，等差数列，首项为ap，公差-ap/(12n)）： ap(1-(i-1)/(12n)) 每月还款总额：a/(12n)+ap(1-(i-1)/(12n)) 总利息（等差数列求和，），为（ap-（n*12-1）*ap/(n*12)/2）*n*12 =ap(n*12+1)/2 为了兼顾需要深究钻研的朋友，本文最后补充一下等额本息、等额本金，每月还款本金、利息的计算原理与方法。 等额本金，顾名思义，就是每月还款中，包含的还款本金都一样。 只不过，由于欠银行的贷款本金逐月减少，那么相应的利息也逐月减少。 因此，会出现逐月还款后，所还的金额越来越少的现象（逐月递减）。 假设贷款总金额a，年利率P，年限n（共n*12个月，即分为n*12期按揭）， 具体等额本金的计算公式（第i个月）： 每月还款含本金（都相等）：a/(12n) 每月还款含利息（逐月递减，等差数列，首项为ap，公差-ap/(12n)）： ap(1-(i-1)/(12n)) 每月还款总额：a/(12n)+ap(1-(i-1)/(12n)) 总利息（等差数列求和，），为（ap-（n*12-1）*ap/(n*12)/2）*n*12 3 等额本息，顾名思义，就是每月还的本金加利息，总和固定。 假设贷款总金额a，年利率P，年限n（共n*12个月，即分为12n期按揭）， 月利率p=P/12 每月还款总额都为x 具体等额本息的计算公式： 第1个月： 还款利息：ap 还款本金：x-ap 第2个月： 还款利息：(a-(x-ap))p = (ap-x)(1+p) +x 还款本金：x-( (ap-x)(1+p) +x ) = -(ap-x)(1+p) 第3个月： 还款利息：(a-(x-ap)+(ap-x)(1+p))p = (ap-x)(1+p)² +x 还款本金：x-( (ap-x)(1+p)² +x ) = -(ap-x)(1+p)² 以此类推， 第i个月： 还款利息：(ap-x)(1+p)^(i-1) +x 还款本金： -(ap-x)(1+p)^(i-1) 一直到最后一个月（第n*12个月）： 还款利息：(ap-x)(1+p)^(12n-1) +x 还款本金： -(ap-x)(1+p)^(12n-1) 将每个月的还款本金（是等比数列，首项为x-ap，公比为1+p）， 相加之和应该等于总本金a，即 （x-ap）(1-(1+p)¹²ⁿ) / (1-(1+p)) = a 则 x=ap(1+1/((1+p)¹²ⁿ - 1)) 将x再代入上面的各月的式子，得到： 第1个月： 还款利息：ap 还款本金：ap/((1+p)¹²ⁿ - 1) 第2个月： 还款利息：ap((1+p)¹²ⁿ-(1+p))/((1+p)¹²ⁿ - 1) 还款本金：ap(1+p)/((1+p)¹²ⁿ - 1) 第3个月： 还款利息：ap((1+p)¹²ⁿ-(1+p)²)/((1+p)¹²ⁿ - 1) 还款本金：ap(1+p)²/((1+p)¹²ⁿ - 1) 以此类推， 第i个月： 还款利息：ap((1+p)¹²ⁿ-(1+p)^(i-1))/((1+p)¹²ⁿ - 1) 还款本金： ap(1+p)^(i-1)/((1+p)¹²ⁿ - 1) 总利息，ap(1+p)¹²ⁿ(12n)/((1+p)¹²ⁿ - 1) - [(1-(1+p)¹²ⁿ)/(1-(1+p))] *ap/((1+p)¹²ⁿ - 1) =ap(1+p)¹²ⁿ(12n)/((1+p)¹²ⁿ - 1)-a END 等额本息，顾名思义，就是每月还的本金加利息，总和固定。 假设贷款总金额a，年利率P，年限n（共n*12个月，即分为12n期按揭）， 还款利息：(a-(x-ap))p = (ap-x)(1+p) +x 还款本金：x-( (ap-x)(1+p) +x ) = -(ap-x)(1+p) 还款利息：(a-(x-ap)+(ap-x)(1+p))p = (ap-x)(1+p)² +x 还款本金：x-( (ap-x)(1+p)² +x ) = -(ap-x)(1+p)² 还款利息：(ap-x)(1+p)^(i-1) +x 还款本金： -(ap-x)(1+p)^(i-1) 一直到最后一个月（第n*12个月）： 还款利息：(ap-x)(1+p)^(12n-1) +x 还款本金： -(ap-x)(1+p)^(12n-1) 将每个月的还款本金（是等比数列，首项为x-ap，公比为1+p）， （x-ap）(1-(1+p)¹²ⁿ) / (1-(1+p)) = a x=ap(1+1/((1+p)¹²ⁿ - 1)) 将x再代入上面的各月的式子，得到： 还款本金：ap/((1+p)¹²ⁿ - 1) 还款利息：ap((1+p)¹²ⁿ-(1+p))/((1+p)¹²ⁿ - 1) 还款本金：ap(1+p)/((1+p)¹²ⁿ - 1) 还款利息：ap((1+p)¹²ⁿ-(1+p)²)/((1+p)¹²ⁿ - 1) 还款本金：ap(1+p)²/((1+p)¹²ⁿ - 1) 还款利息：ap((1+p)¹²ⁿ-(1+p)^(i-1))/((1+p)¹²ⁿ - 1) 还款本金： ap(1+p)^(i-1)/((1+p)¹²ⁿ - 1) 总利息，ap(1+p)¹²ⁿ(12n)/((1+p)¹²ⁿ - 1) - [(1-(1+p)¹²ⁿ)/(1-(1+p))] *ap/((1+p)¹²ⁿ - 1) =ap(1+p)¹²ⁿ(12n)/((1+p)¹²ⁿ - 1)-a 不同时期贷款基准利率不同，而且折扣不同，需作相应设置修改 公积金账号因人而异，一般每年会调整一次每月缴存额 怎样使用函数DB计算折旧值 2020.03.18 如何利用IPMT函数计算第一个月的应付利息 2021.04.03 EXCEL运用RATE计算投资年增长率 2020.08.14 Excel如何计算分期贷款的偿还的本和利息 2019.04.05 excel如何自动求指定年限的本金额 2018.12.02