Summary

The Excel PPMT function can be used to calculate the principal portion of a given loan payment. For example, you can use PPMT to calculate the principal amount of a payment for the first period, the last period, or any period in between.

Purpose 

Get principal payment in given period

Return value 

The principal payment

Syntax

=PPMT(rate,per,nper,pv,[fv],[type])
  • rate - The interest rate per period.
  • per - The given payment period.
  • nper - The total number of payments for the loan.
  • pv - The present value, or total value of all payments now.
  • fv - [optional] The cash balance desired after last payment is made. Defaults to 0.
  • type - [optional] When payments are due. 0 = end of period. 1 = beginning of period. Default is 0.

How to use 

The PPMT function returns the principal portion of a payment for a given period of an investment or a loan, based on constant periodic payments and a constant interest rate. PPMT takes six arguments, four of which are required:

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

Each argument has the following meaning:

  • rate - the interest rate per period. Typically, this is the annual interest rate divided by the compounding periods per year.
  • per - the given payment period as a number (i.e. 1, 2, 3, etc.)
  • nper - The total number of payment periods for the loan or investment.
  • pv - The present value, or the principal amount of the loan or investment.
  • fv - Optional. The desired future value, or a cash balance after the last payment (defaults to 0)
  • type - Optional. The timing of the payment: 0 = end of period (default), 1 = beginning of the period.

Example #1 - hardcoded values

Suppose you have a 5-year loan of $10,000 with an annual interest rate of 5% and 12 compounding periods per year. You want to figure out the principal portion of the payment for period 1. You can calculate this amount with the PPMT function like this:

=PPMT(5%/12,1,60,-10000)

The inputs to PPMT are as follows:

  • rate - 5%/12 = 0.00416 (annual interest rate with monthly compounding)
  • per - 1 (period 1)
  • nper - 60 (a 5-year loan has 60 periods)
  • pv - -10,000 (the loan amount is negative to yield a positive result)
  • fv - Omitted. Defaults to 0.
  • type - Omitted. Defaults to 0.

The result is 147.05. This is the principal portion of the payment for period 1 of the loan. Notice we have provided the loan balance as a negative value to get a positive result from PPMT. If we provide 10,000 as a positive number, PPMT will return -147.05. In practice, the decision to use a positive or negative value for pv depends on the specific scenario.

Example #2 - worksheet formula

In the example above, all inputs to PPMT are hardcoded to make the formula easier to read. More typically, the inputs will come from cell references. The screen below shows how the same example can be transferred to a worksheet:

Example of the PPMT function to calculate the principal amount in period 1

The formula in cell C10 is evaluated like this:

=PPMT(C5/C7,1,C6*C7,-C4)
=PPMT(0.05/12,1,5*12,-10000)
=PPMT(0.004167,1,60,-10000)
147.05

Notice that we provide (years * periods per year) for nper instead of hardcoding the number 60. We do this so that the formula will automatically adapt to a loan with a different term in years, or a loan with a different number of periods per year.

Also, notice that the PPMT function does not need the monthly payment. This is because PPMT calculates the principal portion of a payment based on the original principal (or present value), the interest rate, and the number of periods. To calculate the payment for a loan you can use the PMT function.

Notes

  1. Use the IPMT function to get the interest portion of a loan payment for a given period.
  2. The interest rate can be provided as a percentage like 5% or a decimal number like 0.05.
  3. Be careful to provide the periodic interest rate for rate. For example, 5%/12 or 0.05/12.
  4. The loan value (pv) can be entered as a positive value or a negative value. 
  5. The value for period (per) must be in the range 1 to nper.
Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.