PPMT function

The PPMT function in Excel calculates the principal portion of a payment for a given period in an amortizing loan or investment. It helps you determine how much of each payment goes toward the principal, as opposed to the interest, over the course of the loan or investment.

Syntax

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

Parameters

1. rate: The interest rate for each period. For example, if you have an annual interest rate of 6%, you would enter 0.06. If the payments are monthly, you would enter the monthly rate (annual rate divided by 12), i.e., 0.06/12.
2. per: The period for which you want to calculate the principal payment. This value must be between 1 and the total number of periods (nper).
3. nper: The total number of periods (payments) for the loan or investment. For a 5-year loan with monthly payments, nper would be 5*12 = 60.
4. pv: The present value, or the principal amount of the loan or investment. This is the amount of money you are borrowing or investing.
5. [fv] (optional): The future value or the cash balance you want to attain after the last payment is made. The default is 0, which means the loan is paid off entirely at the end.
6. [type] (optional): The timing of payments:
   • 0 = Payments are due at the end of the period (default).
   • 1 = Payments are due at the beginning of the period.

How It Works

The PPMT function calculates how much of a specific payment goes toward paying off the principal, rather than the interest. In an amortizing loan, early payments are mostly interest, with a small portion going toward the principal. As the loan balance decreases, more of the payment is applied to the principal.

The formula behind the PPMT function is:

$$\text{Principal Payment} = \text{Payment} – \text{Interest Payment}$$

Where:

• Payment is the total payment calculated by the PMT function.
• Interest Payment is the interest portion for the current period, calculated as loan balance * interest rate.

Example

Suppose you have a $10,000 loan with an annual interest rate of 6% and a 5-year term, with monthly payments. You want to calculate the principal portion of the payment for the 12th payment.

=PPMT(0.06/12, 12, 5*12, 10000)

In this example:

• 0.06/12 is the monthly interest rate (6% annual rate divided by 12).
• 12 is the 12th payment period.
• 5*12 is the total number of payments (60 payments for a 5-year loan with monthly payments).
• 10000 is the loan amount (principal).

This formula will return the principal portion of the 12th payment.

Important Notes

• The PPMT function returns a negative value because it represents the amount of the loan principal being paid off, which is an outflow of money.
• The rate and nper values should match the payment frequency (monthly, yearly, etc.).
• The per value must be between 1 and the total number of periods. It represents the payment period for which you want to calculate the principal.

Summary

The PPMT function is used to determine the principal portion of a payment in an amortizing loan or investment. It is a helpful tool for understanding how much of each payment is reducing the loan balance, as opposed to how much is paying interest. By providing the interest rate, the period, the total number of periods, and the loan amount, you can use the PPMT function to break down payments over the course of the loan.