PPMT Function in Excel

The PPMT function in Excel is a financial function used to calculate a principal's payment. The value returned by this function is an integer value. It helps you see how much of your monthly payment goes toward reducing the actual loan amount. You use it when you have a loan with equal payments over time.

For instance, you can utilize the PPMT function to get the principal amount of an installment for the first period, the last period, or any period between. It's useful for loan schedules or understanding how debt is paid off over time. 

For example, suppose the loan amount is $50,000 in cell B1, and the interest rate is 5% in cell B2. The period of the loan taken in cell B3 is 10 years. Then, we can calculate the principal amount for 1 month of the loan using the PPMT Excel function as follows:

=PPMT(B2/12,1,B3*12,B1)

= $321.99.

The arguments are as follows:

"Per" is the specific pay period for which one wants to compute the amount paid towards the principal. 

FV in Excel is an optional argument. If omitted, the fv takes on the default value 0.

Let us look at the steps involved in calculating the principal payment.

Step 1: Type =PPMT( into a cell to start using the function.

Step 2: Let us enter the arguments of this formula.

• Enter the interest rate divided by the number of periods per year, for example, 5% annual interest with monthly payments is 5%/12.
• Enter the specific period number you want to calculate, like 1 for the first month.
• Enter the total number of periods, such as 60 for a 5-year loan with monthly payments.
• Enter the loan amount as a negative number, for example, -10000 for a $10,000 loan.

Add a comma between each of the arguments. 

Step 3: Close the brackets and press Enter, 

Your formula will look as below: 

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

This will show how much of the first payment goes toward the principal.

Let us look at some simple examples to do the same. 

Example #1

Suppose we need to calculate the payments on the principal for months 1 and 2 on a $10,000 loan, which is to be paid off in full after 3 years with the monthly payment of $500. 

Interest is charged at a rate of 5% per year. The loan repayments are to be made at the end of each month.

To calculate this, we will use the PPMT in Excel.

Step 1: Applying the PPMT function with all input values as shown above for every month's installment, the principal amount for each month.

Step 2: Similarly, applying the PPMT function to other periods, we also have the principal amount of each period, as shown below.

As you can see above, for each period, the principal amount which totals the amount as the loan amount, which is $200,000.

Example# 2

If the loan amount is $10,000 with an interest rate of 10% and the loan period is 2 years. Then the principal amount for 1 month of the loan will be calculated using the PPMT in Excel, as shown below.

Step 1: Using the PPMT function, we compute the principal amount for the 1 month.

Step 2: Here, the fv is optional. Since there is no future value, we took it as 0, and the type is 0 as the payment is made at the end of the month. Even if we skip the last two arguments still, we will get the desired result.

Finance related topics are always interesting to learn, right? Check out some of our interesting courses like the Financial Modeling Course right here to become an expert on the subject. 

• The input rate has to be consistent. 
• If the payments are made quarterly, it will convert the annual interest rate into the quarterly rate (rate%/4), and the period number has to be converted from years to quarters (=per*4).
• By convention, the loan value (pv) is entered as negative.