使用EXCLE函数等额本息法和等额本金法计算每月还款金额

用pmt函数就可以，Pmt(贷款利率，期数，本金)可以计算。

本金＝PPMT(各期利率,第几期,总期数,本金）

利息＝IPMT(各期利率,第几期,总期数,本金）

等额(本息)还款法，是指借款人在贷款期间内每月等额归还贷款本息；计算公式如下:

每月支付利息＝剩余本金 X 贷款月利率；

每月归还本金＝每月还本付息额-每月支付利息。

等额本金：

利息计算＝（本金 — 已归还本金累计额）×每月利率

每月还款＝（贷款本金 / 还款月数）+（本金 — 已归还本金累计额）×每月利率

扩展资料：

举例说明等额本息还款法，

假定借款人从银行获得一笔20万元的个人住房贷款，贷款期限20年，贷款年利率42%，每月还本付息。按照上述公式计算，每月应偿还本息和为123314元。

上述结果只给出了每月应付的本息和，因此需要对这个本息和进行分解。仍以上例为基础，一个月为一期，第一期贷款余额20万元，应支付利息700元（200000×42%/12），支付本金53314元，仍欠银行贷款19946686元；第二期应支付利息（19946686×42%/12)元。

-等额本息

计算每月等额还款额 EXCEL中提供的PMT函数是基于固定利率及等额分期付款方式，返回投资或贷款的每期付款额。利用PMT函数可以计算出每月偿还的款额。 11 PMT函数的使用格式 PMT（rate，nper，pv，fv，type） Rate 为各期利率，是一固定值。 Nper 为总投资（或贷款）期，即该项投资（或贷款）的付款期总数。 Pv 为现值，即从该项投资（或贷款）开始计算时已经入账的款项，或一系列未来付款当前值的累积和，也称为本金。 Fv 为未来值，或在最后一次付款后希望得到的现金余额，如果省略 fv，则假设其值为零（例如，一笔贷款的未来值即为零）。　Type 只能用数字0 或1，用以指定各期的付款时间是在期初还是期末。如果省略 type，则假设其值为零。 12 说明 PMT 函数返回的支付款项包括本金和利息，但不包括税款、保留支付或某些与贷款有关的费用。 应确认所指定的 rate 和 nper 单位的一致性。例如，同样是四年期年利率为 12% 的贷款，如果按月支付，rate 应为 12%/12，nper 应为 412；如果按年支付，rate 应为 12%，nper 为 4 13 银行还贷计算示例 假如你购一套200，000元的房子，首付30%后，你的贷款本金是140，000元，10年按揭付清（至0），按目前银行商业住房贷款利率，10年是月利率是42‰。 在EXCEL电子表格的某单元中，输入： =PMT（00042，1012，140000，0，0）　回车确认后，所得的值148766元，就是你每月等额还款的数字，它包括了本金与利息。 输入公式中的00042是月利率，如果是年利率，要将年利率除以12转换为月利率；1012是还款的总月数；140000是贷款的本金；第一个0，指还完所有本息后其最后值就是0；第二个0表示期末付，银行多设置为期末付。 除了用于贷款之外，函数 PMT 还可以计算出别的以年金方式付款的支付额。例如，如果需要以按月定额存款方式在 10 年中存款 ￥50，000，假设存款年利率为 6%，则函数 PMT 可以用来计算月存款额： PMT（6%/12， 1012， 0， 50000） 等于 -￥30510 即向 6% 的存款帐户中每月存入 ￥30510，10 年后可获得 ￥50，000 2 计算各月偿还的利息EXCEL中的IPMT函数是一个基于固定利率及等额分期付款方式，返回投资或贷款在某一给定期次内的利息偿还额的函数，利用IPMT 函数可以计算各月偿还的利息额。　2、IPMT函数的使用格式 IPMT（rate，per，nper，pv，fv，type） Rate 为各期利率，是一固定值。 Per 用于计算其利息数额的期次，必须在 1 至 nper 之间。 Nper 为总投资（或贷款）期，即该项投资（或贷款）的付款期总数。 Pv 为现值，即从该项投资（或贷款）开始计算时已经入账的款项，或一系列未来付款当前值的累积和，也称为本金。 Fv 为未来值，或在最后一次付款后希望得到的现金余额。如果省略 fv，则假设其值为零（例如，一笔贷款的未来值即为零）。　Type 只能是数字 0 或 1，用以指定各期的付款时间是在期初还是期末。如果省略 type，则假设其值为零。 22 说明 应确认所指定的 rate 和 nper 单位的一致性。例如，同样是四年期年利率为 12% 的贷款，如果按月支付，rate 应为 12%/12，nper 应为 412；如果按年支付，rate 应为 12%，nper 为 4。　在所有参数中，支出的款项，如银行存款，表示为负数；收入的款项，如股息收入，表示为正数。 23 各月利息计算 按以上例子（即贷款140000元，10年还清，月利率42‰）来计算各月的利息。在EXCEL的A1填上“第N月”，B1填上“第N月偿还利息”，然后在A2到A121分别填上1～120，表示第1个月到第120个月。在B2单元中输入： =IPMT（00042，A2，1012，140000，0，0） 回车确认即可得出-58800，即第1个月的利息为58800元。

这实际上就是“等额本息法”的公式，如下：

月还款额=本金月利率(1+月利率)^n/[(1+月利率)^n-1]

式中：月利率=年利率/12，n表示贷款月数，^n表示n次方，如^180，表示180次方（贷款15年，180个月）。

注意：计算(1+月利率)^n-1时，要先将小括号内的算出来，乘方后再减1。

例:现假设贷款20万元，期限15年（180个月），年利率9%。

将数据代入公式：200000（9%/12(1+9%/12)^180/[(1+9%/12)^180-1] =2028533168=202853元。

扩展资料:

一、PMT还贷函数简介:

PMT函数即年金函数，PMT基于固定利率及等额分期付款方式，返回贷款的每期付款额。EXCEL函数一共有11类，分别是数据库函数、日期与时间函数、工程函数、财务函数、信息函数、逻辑函数、查询和引用函数、数学和三角函数、统计函数、文本函数以及用户自定义函数。

二、PMT还贷函数语法参数:

1Rate贷款利率（期利率）。

2Nper该项贷款的付款总期数(总年数或还租期数）。

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

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

5Type数字0或1，用以指定各期的付款时间是在期初还是期末。1代表期初（先付：每期的第一天付），不输入或输入0代表期末（后付：每期的最后一天付）。

三、PMT还贷函数的种类:

PMT函数一共有11类，分别是数据库函数、日期与时间函数、工程函数、财务函数、信息函数、逻辑函数、查询和引用函数、数学和三角函数、统计函数、文本函数以及用户自定义函数。

1数据库函数:

当需要分析数据清单中的数值是否符合特定条件时，可以使用数据库工作表函数。

2日期与时间函数:

通过日期与时间函数，可以在公式中分析和处理日期值和时间值。

3工程函数:

工程工作表函数用于工程分析。这类函数中的大多数可分为三种类型：对复数进行处理的函数、在不同的数字系统间进行数值转换的函数、在不同的度量系统中进行数值转换的函数。

4逻辑函数:

使用逻辑函数可以进行真假值判断，或者进行复合检验。例如，可以使用 IF 函数确定条件为真还是假，并由此返回不同的数值。

5数学和三角函数:

通过数学和三角函数，可以处理简单的计算，例如对数字取整、计算单元格区域中的数值总和或复杂计算。

6用户自定义函数:

如果要在公式或计算中使用特别复杂的计算，而工作表函数又无法满足需要，则需要创建用户自定义函数。这些函数，称为用户自定义函数，可以通过使用 Visual Basic for Applications 来创建。

7查询和引用函数:

当需要在数据清单或表格中查找特定数值，或者需要查找某一单元格的引用时，可以使用查询和引用工作表函数。

8统计函数:

统计工作表函数用于对数据区域进行统计分析。例如，统计工作表函数可以提供由一组给定值绘制出的直线的相关信息，如直线的斜率和 y 轴截距，或构成直线的实际点数值。

9文本函数:

通过文本函数，可以在公式中处理文字串。例如，可以改变大小写或确定文字串的长度。可以将日期插入文字串或连接在文字串上。

参考资料:PMT还贷函数_

假设贷款总金额为A，月利率为β，贷款期数为k，

每期需还款总金额（本金+利息）为x，

则：

    第一期还款后，欠款总金额 Q1 = A (1 + β) - x

    第二期还款后，欠款总金额 Q2 = Q1 (1 + β) - x = [A (1 + β) - x] (1 + β) - x

                                                      = A (1 + β) ^ 2 - [1 + (1 + β)] x

    第三期还款后，欠款总金额 Q3 = Q2 (1 + β) - x

                                                       = {A (1 + β) ^ 2 - [1 + (1 + β)] x} (1 + β) - x

                                                       = A (1 + β) ^ 3 - [(1 + β) ^ 2 + (1 + β) + 1] x

由此可得出    第k期还款后，

    欠款总金额 Qk = Qk-1 (1 + β) - x =

                             = A (1 + β) ^ k - [(1 + β) ^ (k-1) + (1 + β) ^ (k-2) + + 1] x。

   我们发现[ ]内是等比数列，等比数列求和公式是不是又忘记了？

   我们一起来推导下。设y=1 + β，

    则Sk = 1 + y + y ^2 + + y ^ (k-1)，y Sk = y + y ^2 + + y ^ (k-1) + y ^ k，

   两公式相差得 y Sk - Sk = y ^ k - 1，从而得出Sk = (y ^ k - 1) / (y -1)。

   由此继续 Qk = A (1 + β) ^ k - {[(1 + β) ^ k - 1] / β} x，

   第k期还款后贷款结束，因此Qk = 0，即 A (1 + β) ^ k - {[(1 + β) ^ k - 1] / β} x = 0，

  得出等额本息每期还款本息总额 x = A β (1 + β) ^ k / [(1 + β) ^ k - 1]，这便是每期需要还款的总金额。

等额本息每期还款总金额x公式已经有了，那么每期还款的本金是多少呢？

假设第n期还款本金为Pn，

则：

        第一期需还本金 P1 = x - A β

        第二期需还本金 P2 = x - (A - P1) β

                                       = x - {A - [x - A β]} β

                                       = x - A β + (x - A β) β

                                       = P1 + P1 β = P1 (1 + β)

        第三期需还本金 P3 = x - (A - P1 - P2) β

                                        = x - {A - P1 - P1 (1 + β)} β

                                        = x - A β + P1 β + P1 (1 + β) β

                                        = P1 (1 + β) ^ 2

 则可以猜测第n期需还本金 Pn = P1 (1 + β) ^ (n - 1)

 下面我们来论证这个公式，假设公式成立，

 则 P(n + 1) = x - [A - P1 - P2 - -Pn] β

                    = x - {A - P1 [1 + (1 + β) + + (1 + β) ^ (n - 1)]} β

                    = x - {A - P1 [(1 + β) ^ n - 1] / β} β

                    = x - A β + P1 [(1 + β) ^ n - 1] = p1 (1 + β) ^ n

由此可以得出，等额本息还款中每期还款本金 Pn = P1 (1 + β) ^ (n - 1)

1、首期利息　等额本息中，首期还款可能存在不足月的情况，这时候本金可以严格按照上述公式得出，

     但利息肯定不能按满月算了（每期还款利息是按期数-月为单位的），     这时候首期利息得需要按实际使用天数进行特殊计算。

     假设第一期还款时实际使用天数为 t，则首期利息 L1 = A β t / 30

     如何计算首期实际使用天数？

     首期实际使用天数计算实性的是“对月对日”，首先找到首期还款日t1对应上一期的还款日t0（若当月t0不存在，则往下延一天，即下月的首日），再比较起息日y和t0的天数差，综合，首期实际使用天数 t = 30 - (y - t0)。

范例：

     1) 起息日2018-02-15，首期还款日2018-03-10，则t0为2018-02-10，

          得出首期实际使用天数 t = 30- (2018-02-15 - 2018-02-10) = 25

      2) 起息日2018-03-02，首期还款日2018-03-31，则t0为2018-03-01

         （对应2018-02-31不存在，则顺延一天）

           得出首期实际使用天数 t = 30- (2018-03-02 - 2018-03-01) = 29

2、末期本金

             由于每期还款本金是公式计算后取四舍五入的值，存在精度丢失问题，

              因此末期还款本金金额为 Pk = A - P1 - P2 - - P(k-1)

      假设贷款总金额为A，月利率为β，贷款期数为k，

      每期需还款总金额（本金+利息）为x，

      第n期需还款本金为Pn，第n期需还利息为Ln，

则：

      第1至k-1期每期还款本金 Pn (1 <= n < k) = P1 (1 + β) ^ (n - 1)　    

      第k期还款本金 Pk = A - P1 - P2 - - P(k-1)

      第1期还款利息 L1 = A β t / 30　第2期到k期还款利息 Ln = x - Pn

      第1期还款本息总额 w1 = P1 + L1   第2期至k期还款本息总额 wn = x

 利用函数PPMT(rate,per,nper,pv,fv,type)计算本金，IPMT函数计算利息

 本金＝PPMT(各期利率,第几期,总期数,本金）

 利息＝IPMT(各期利率,第几期,总期数,本金）

  Excel中的PMT函数，通过单、双变量的模拟运算来实现贷款的利息计算。

  PMT函数可基于利率及等额分期付款方式，

  根据贷款利率、定期付款和贷款金额，来求出每期(一般为每月)应偿还的贷款金额。

  PMT函数的格式和应用方式：　PMT(Rate，Nper，Pv，Fv，Type)

  其中各参数的含义如下：

  Rate：各期利率，

             例如，如果按84%的年利率借入一笔贷款来购买住房，并按月偿还贷款，

             则月利率为84%/12(即07%)。

             用户可以在公式中输入84%/12、07%或0007作为Rate的值。

 Nper：贷款期数，即该项贷款的付款期总数。

             例如，对于一笔10年期按月偿还的住房贷款，共有10×12(即120)个偿款期数。

                         可以在公式中输入120作为Nper的值。

 Pv：现值，或一系列未来付款的当前值的累积和，也就是贷款金额。

 Fv：指未来终值，或在最后一次付款后希望得到的现金余额。

         如果省略Fv，则假设其值为零，

         也就是一笔贷款的未来值为零，一般银行贷款此值为0。