Excel怎么制作每月的房贷车贷提前还贷计算器？

Excel怎么制作每⽉的房贷车贷提前还贷计算器？

我们知道银⾏贷款的按揭还贷，主要分为等额本息、等额本⾦两种还款⽅式。其中等额本息是每⽉还款固定⾦额，等额本⾦是

逐⽉递减（总利息最少）。之前，⼩编分享给⼤家如何使⽤Windows计算器简单速算等额本息每⽉还款⾦额，本⽂结合公积

⾦贷款、商业贷款，等多种复合条件，分享给⼤家如何使⽤⾝边的Excel，根据不同情况，详细计算每⽉贷款还款额度。

使⽤Excel设置贷款计算器模板标题

1、在Excel中，我们先建⼀张如下图模板的表格。

颜⾊说明：

黄⾊背景单元格，⽤来⾃⼰填写数据。

绿⾊背景的单元格，⼀般也需要⾃⼰填好。

橙⾊背景的单元格，是我们着重关注的贷款每⽉还款⾦额。

其他单元格，⼀般都是让公式⾃动计算⽣成数据。

2、第1列，在单元格A4使⽤下列公式填写年⽉。

=EDATE("2015-11-2",ROW()-3)

其中2015-11-2，需修改为贷款实际开始还款的那个⽉，当中的任意⼀天。

注意：这⾥使⽤公式，⽽不是直接填写年份⽉份，是为了⽅便往下拉公式，实现⾃动填充。

3、右击单元格，设置单元格格式。

4、在⾃定义中，修改为yyyy-m，这样的格式。

5、第1⾏，在单元格D1使⽤公式

="年限（"&E1*12&"期)"

在单元格H1使⽤公式

="打折（实际为"&TEXT(G1*I1,"0.00%")&"）"

在单元格L1使⽤公式

="总利息："&ROUD(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)&" 相当于本⾦

的"&ROUD(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⾏（复制公式），这样按揭贷款计算器的标题，即制作完毕。

⼆、使⽤公式计算公积⾦贷款每⽉还贷⾦额、本⾦、利息

1、计算公积⾦逐⽉还贷，每⽉需还款的⾦额：在单元格B4，使⽤公式

=ROUD(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，输⼊公式：

="本⾦："&ROUD(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)&" 利

息："&ROUD(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

即可⽴即得到，每⽉需要实际准备的现⾦，⽤于还公积⾦贷款。如果结果为负值，说明你的公积⾦⾜够多，根本不需要另外准

备现⾦或打款到贷款银⾏账户。

三、使⽤公式计算商业贷款每⽉还贷⾦额、本⾦、利息

1、类似地，我们在单元格H4使⽤公式

=ROUD(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，输⼊公式

="本⾦："&ROUD(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)&" 利

息："&ROUD(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⾏的相应单元格，往下拉公式即可实现快速计算每⽉贷款还款⾦

额。

四、Excel贷款计算器使⽤⽅法与⾦融数学原理

1、最后，我们总结⼀下，刚刚使⽤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

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

注意事项：不同时期贷款基准利率不同，⽽且折扣不同，需作相应设置修改，公积⾦账号因⼈⽽异，⼀般每年会调整⼀次每⽉

缴存额。