PPMT Function

What is the PPMT Function?

The PPMT function is used to identify the payment of the principal for a financial instrument based on periodic, constant payments and a constant interest rate.

The function is helpful in understanding the components of the loan or an investment payment.

For example, when you make periodic payments on a loan, a certain part of each payment goes toward the interest, and the remainder goes to the principal.

While the total payment amount is usually kept constant for all periods, the principal and interest parts are different. As time goes by, the lower payment amount is applied toward the interest payment and the higher payment amount toward the principal payment.

The PPMT function helps to identify the amount applied to the principal. The other two closely related to PPMT functions that can be useful when dealing with loans are

• PMT function: calculates the total periodic payment on a loan (including interest and principal)
• IPMT function: calculates the interest payment on a loan

The PPMT function can be found under the Financial Functions category on the Formulas tab. It can also be entered as part of a formula in a worksheet cell.

How to use the PPMT Function in Excel?

As mentioned before, there are multiple ways of using the function in Excel. First, you can utilize the Formulas tab. The function can be found under the Financial functions category:

Once you select the PPMT function from the list, the formula will automatically appear. It will guide you through the arguments that must be selected to achieve the desired result. These arguments are specified below.

Alternatively, you can type the formula that follows directly into any cell in a worksheet:

= PPMT(rate; per; nper; pv; [fv]; [type])

Where:

• Rate (required) – the interest rate per period. 
• Per (required) - the instrument’s term; must be the number between 1 and the nper.
• Nper (required) – the total number of payments.
• Pv (required) – the present value of the instrument (the present value of future total payments).  
• Fv (optional) – refers to the future value of the instrument at the end of nper. If the future value is omitted, it is assumed to be 0.
• Type (optional) – the number indicating when payments are due. If the payment is expected at the end of the period, select 0. If the payment is expected at the beginning of the period, select 1. If the type is omitted, it is assumed to be 0.

Finally, you can use the Formulas tab and the manual input option.

Click on any cell in a worksheet, select the Formulas tab, press the Insert Function button, and start typing the formula.

Example of PPMT Function

Let's consider an example where you have taken out a 3-year personal loan for $20.000. The annual interest rate on the loan is 5%. The interest expenses are accrued and paid at the end of each quarter.

In addition, both parties anticipate the full repayment of the obligation at the end of the loan period. Before you can use the function, a few parameters must be amended:

1. Interest rate (rate)

Since the interest is accrued quarterly, we must identify the quarterly rate. This is done by dividing the annual rate by 4.

Quarterly interest rate = 5% / 4 = 1.25%

If the loan calls for monthly interest payments, the annual rate will have to be divided by 12 and by 2 in case of semi-annual payments.

2. The number of periods (nper)

To identify the number of periods, multiply the loan term by the frequency of interest payments.

3 years x 4 = 12 periods

At this point, one should be ready to put all of the inputs into the function:

As it is expected that the loan will be repaid in full fv equals 0. In addition, the type is also set at 0, as it was stated earlier that the interest payments are expected to be paid at the end of each quarter.

Given the assumptions above, we can now build a three-year principal payment schedule.

Aside from the per, all the arguments are fixed values that should be kept constant.

The total principal payment amount per calculation is equal to the entire principal amount specified in terms of this example, confirming that the calculation is correct.

Things to remember when using the PPMT function

When using the PPMT function, some considerations must be made, as with any other Excel function.

The things to remember are

• #NUM! Error occurs when the value of nper<per argument<0.
• If any function inputs are non-numeric values, the function returns the #VALUE! Error.
• An error can also arise if you forget to adjust the inputs to reflect the correct interest rate or the number of periods. The following table can be used as a reference when making the respective adjustments:
  

  Reference Table

  Payment Frequency	Interest rate adjustment	Number of periods adjustment
  Monthly	Annual interest rate / 12	Number of years x 12
  Quarterly	Annual interest rate / 4	Number of years x 4
  Semi-annual	Annual interest rate / 2	Number of years x 2

• The function returns a negative number because it is an outgoing payment.
• If you have a loan that is paid back weekly, the function cannot properly calculate the payments.
• The function assumes a loan or an investment has a fixed interest rate (the rate remains constant over the entire term of the loan or an investment).
• The PPMT function also assumes that the total payments are constant throughout the term of the loan or an investment.

Researched and authored by Kseniia Tokarieva | LinkedIn

Reviewed and edited by Parul Gupta | LinkedIn

Free Resources

To continue learning and advancing your career, check out these additional helpful WSO resources:

• PMT Function
• PRICE Function
• PRICEDISC Function
• RANK Function
• RATE Function