使用PMT和IPMT在Excel中显示贷款付款

With the PMT function, you can enter a few numbers, and calculate the monthly payments for a loan amount. But when is each payment due, and how much goes to interest and principal? This PMT and IPMT functions example shows how to calculate that too.

使用PMT功能，您可以输入一些数字，并计算贷款金额的每月还款额。 但是每笔付款何时到期，利息和本金有多少？ 这个PMT和IPMT功能示例也显示了如何计算该值。

PMT和IPMT示例 (PMT and IPMT Example)

This example shows how to calculate monthly payments and interest amounts with the Excel PMT and IPMT functions.

本示例说明如何使用Excel PMT和IPMT函数计算每月付款和利息金额。

Your loan company might use different calculations, so check with them for accurate numbers. This example is for entertainment purposes only!

您的贷款公司可能使用不同的计算方法，因此请与他们核对准确的数字 。 此示例仅用于娱乐目的！

贷款明细 (Loan Details)

In this example, the loan details are entered at the top of the worksheet, and the PMT function will calculate the monthly payment amount.

在此示例中，贷款详细信息输入工作表的顶部， PMT功能将计算每月还款额。

There are 3 required numbers for the PMT function:

PMT功能需要3个数字：

• Rate: The annual rate is entered in cell D2 (named LoanRate)

  费率 ：年费率在单元格D2中输入(名为LoanRate )

• Periods: The number of months for the loan term is entered in cell C2 (named LoanMths)

  期间 ：在单元格C2中输入贷款期限的月数(名为LoanMths )

• Value: The loan amount is entered in cell B2 (named LoanAmt)

  价值 ：在单元格B2中输入贷款金额(名为LoanAmt )

The other green cell (A2, named LoanStart) is for the date of the first payment. It's not required for the PMT function, but will be used in the payment table.

另一个绿色单元格(A2，名为LoanStart )用于第一次付款的日期。 PMT功能不是必需的，但它将在付款表中使用。

计算付款金额 (Calculate Payment Amount)

The PMT function is used in cell E2 (named LoanPmt), to calculate the monthly payment amount.

E2单元格(称为LoanPmt)中使用PMT函数来计算每月还款额。

• =-PMT(LoanRate/12,LoanMths,LoanAmt)

  = -PMT(LoanRate / 12，LoanMths，LoanAmt)

• There's a minus sign at the start of the formula, to show the result as a positive number.

  公式开头有一个负号 ，以将结果显示为正数。

• The annual rate is divided by 12, to calculate the monthly rate

  将年利率除以12 ，以计算月利率

The PMT function calculates that if we borrow $5000, over 36 months, at an annual rate of 5%, the monthly payment will be $149.85

PMT函数计算得出，如果我们在36个月内以5％的年利率借入$ 5,000，则每月还款$ 149.85

There are more PMT function examples on my Contextures website.

我的Contextures网站上有更多的PMT功能示例 。

付款明细表 (Payment Details Table)

Below the PMT calculation, there is a named Excel table – tblPay – which is filled with formulas. It will calculate the details for each payment.

在PMT计算下方，有一个名为Excel的表格 – tblPay –填充了公式。 它将计算每次付款的详细信息。

The table has 48 rows for monthly data. If you're paying over a longer period, add more rows to the table. The formulas should fill down to the new rows automatically

该表有48行用于每月数据。 如果您要较长时间付款，请在表格中添加更多行。 公式应自动填入新行

付款表列 (Payment Table Columns)

There are 7 columns in the payment details table. Here are the column names and purpose:

付款明细表中有7列。 这是列名称和用途：

• Pay Date: Payment due date

  付款日期 ：付款到期日

• Outstanding: Outstanding loan amount, before payment is made

  未偿还 ：未偿还的未偿还贷款金额

• Mth Pmt: Payment amount

  Mth Pmt ：付款金额

• Interest: Interest amount in the payment

  利息 ：付款利息金额

• Principal: Principal amount in the payment

  本金 ：付款本金

• Total Princ Paid: Running total of principal amount paid

  已付Princ总额： 已付本金的总计

• Pmt Num: Payment number

  Pmt Num ：付款号码

付款号码 (Payment Number)

In column G, this formula calculates the payment number, using the MAX function and SUM function:

在G列中，此公式使用MAX函数和SUM函数计算支付数：

=IF(MAX(G$6:G6)< LoanMths, SUM(G6,1),"")

= IF(MAX(G $ 6：G6)<LoanMths，SUM(G6,1)，“”)

In this example, the loan term is 36 months, so the payment numbers stop at 36, in row 42.

在此示例中，贷款期限为36个月，因此付款编号在第42行的36处停止。

• In row 43, the MAX in G$6:G42 is 36.
  在第43行中，G $ 6：G42的MAX为36。
• That is NOT less than the number in the LoanMths cell, so the result is an empty string
  这不少于LoanMths单元格中的数字，因此结果是一个空字符串

其他公式 (Other Formulas)

All of the other formulas check the Pmt Num column ( G ), to see if it's empty. If it is, the other formulas show an empty string too.

所有其他公式都检查Pmt Num列(G)，以查看其是否为空。 如果是这样，其他公式也将显示一个空字符串。

Here's the start of all the other formulas:

这是所有其他公式的开始：

=IF(G7="","",

= IF(G7 =“”，“”，

支付日期公式 (Pay Date Formula)

In column A, this formula calculates each payment date, based on the start date entered in cell A1 (LoanStart):

在A列中，此公式根据在单元格A1(LoanStart)中输入的开始日期计算每个付款日期：

=IF(G7="","", DATE(YEAR(LoanStart), MONTH(LoanStart)+G7-1, DAY(LoanStart)))

= IF(G7 =“”，“”，DATE(YEAR(LoanStart)，MONTH(LoanStart)+ G7-1，DAY(LoanStart))))

The DATE function gets the year, month and day from the LoanStart cell.

DATE函数从LoanStart单元格获取年，月和日。

For the month, it also adds the Payment number, and then subtracts 1.

对于该月，它还会添加付款号，然后减去1。

So, if the first payment is December 1, 2019, the second payment is January 1, 2020.

因此，如果第一次付款是2019年12月1日，则第二次付款是2020年1月1日。

DATE(2019, 12+2-1,1)

日期(2019，12 + 2-1，1 )

Excel is smart enough to show that as a real date, instead of creating an error because we told it to show month 13 (12+2-1)

Excel足够聪明，可以将其显示为真实日期，而不会产生错误，因为我们告诉它显示第13个月(12 + 2-1)

pm Pmt公式 (Mth Pmt Formula)

In column C, there's a simple formula to show the monthly payment amount, based on the PMT function result at the top of the worksheet:

在C列中，有一个简单的公式根据工作表顶部的PMT函数结果显示每月付款金额：

=IF(G7="","",LoanPmt)

= IF(G7 =“”，“”，LoanPmt)

利息公式 (Interest Formula)

In column D, the IPMT function calculates the interest amount in each payment:

在D列中，IPMT功能计算每次付款的利息金额：

=IF(G7="","",-IPMT(LoanRate/12, G7, LoanMths, LoanAmt))

= IF(G7 =“”，“”，-IPMT(LoanRate / 12，G7，LoanMths，LoanAmt))

The IPMT function is similar to the PMT function, but uses the payment number as its second argument (G7)

IPMT功能类似于PMT功能，但是使用支付号作为第二个参数(G7)

Again, I put a minus sign in front of the function, to return a positive number.

再次，我在函数前面放置一个减号，以返回一个正数。

主要和优秀公式 (Principal  and Outstanding Formulas)

After the interest is calculated, it's easy to find the principal amount – just subtract the interest amount from the payment amount.

计算利息后，很容易找到本金–只需从付款金额中减去利息即可。

=IF(G7="","",C7-D7)

= IF(G7 =“”，“”，C7-D7)

From the principal amounts, we can calculate the Outstanding amount. In each row, it's the original loan amount, minus the total principal amounts in the rows above.

根据本金，我们可以计算出未清额。 在每一行中，它是原始贷款金额减去上一行中的本金总额。

=IF(G7="","",LoanAmt-SUM(E$6:E6))

= IF(G7 =“”，“”，LoanAmt-SUM(E $ 6：E6))

And for the Total Princ Paid column, the formula is a total of all the principal amounts, including the current row.

对于Total Princ Paid列，该公式是所有本金( 包括当前行 )的总和。

=IF(G7="","",SUM(E$6:E7))

= IF(G7 =“”，“”，SUM(E $ 6：E7))

This screen shot shows the formula in row 9, with the sum starting in the header cell (E$6), and ending in E9

此屏幕截图显示了第9行中的公式，总和从标题单元格(E $ 6)开始，并以E9结尾

条件格式 (Conditional Formatting)

There's a conditional formatting rule on the table, so it highlights the latest payment, based on the current date.

表格上有一个条件格式设置规则，因此它会根据当前日期突出显示最新的付款。

To create the conditional formatting rule, I followed these steps:

要创建条件格式设置规则，我遵循以下步骤：

Select all the data cells in the table, starting from cell A7.

从单元格A7中选择表中的所有数据单元格。

On the Excel Ribbon's Home tab, click Conditional Formatting, then click New Rule

在Excel功能区的“主页”选项卡上，单击“条件格式”，然后单击“新建规则”

For Rule Type, select "Use a formula to determine which cells to format"

对于规则类型，选择“使用公式来确定要格式化的单元格”

In the formula box, type this INDEX/MATCH formula, which refers to cell A7 (the active cell):

在公式框中，键入此INDEX / MATCH公式 ，它引用单元格A7(活动单元格)：

• =$A7=INDEX($A$7:$A$54, MATCH(TODAY(), $A$7:$A$54,1))

  = $ A7 = INDEX($ A $ 7：$ A $ 54，MATCH(TODAY()，$ A $ 7：$ A $ 54,1))

规则如何运作 (How the Rule Works)

In the Conditional Formatting rule:

在条件格式规则中：

• MATCH looks for the current date (TODAY function) in the payment date column
  MATCH在付款日期列中查找当前日期(TODAY函数)
• If the current date is not found, MATCH returns the location of the latest date before the current date, because the 3rd argument is 1 (Less than)
  如果找不到当前日期，则MATCH返回当前日期之前的最新日期的位置，因为第3个参数为1(小于)
• Then, the INDEX function returns the date from that location in the list of payment dates
  然后，INDEX函数返回付款日期列表中该位置的日期
• If the date in the current row matches that date, the table row is highlighted with light orange
  如果当前行中的日期与该日期匹配，则表行以浅橙色突出显示

获取样本文件 (Get the Sample File)

To see how the payment table works, you can download the completed sample file from my Contextures website.

若要查看付款表的工作原理，可以从Contextures网站下载完整的示例文件。

On the PMT Function page, go to the download section, and get the Example #4 file.

在PMT功能页面上 ，转到下载部分，并获取Example＃4文件。

The zipped file is in xlsx format, and does not contain any macros.

压缩文件为xlsx格式，不包含任何宏。

翻译自: https://contexturesblog.com/archives/2019/10/31/show-loan-payments-in-excel-with-pmt-and-ipmt/