The tutorial shows how to use the PPMT function in Excel to calculate the payment on the principal for a loan or investment.
When you make periodic payments on a loan or mortgage, a certain part of each payment goes towards the interest (fee charged for borrowing) and the remainder of the payment goes to paying off the loan principal (the amount you originally borrowed). While the total payment amount is constant for all periods, the principal and interest parts are different - with each succeeding payment less is applied to the interest and more to the principal.
Microsoft Excel has special functions to find both the total payment amount and its parts. In this tutorial, we will look at how to use the PPMT function to calculate the payment on the principal.
The PPMT function in Excel calculates the principal portion of a loan payment for a given period based on a constant interest rate and payment schedule.
The syntax of the PPMT function is as follows:
For example, if you make annual payments on a loan or investment with an annual interest rate of 7 percent, supply 7% or 0.07. If you make monthly payments on the same loan, then supply 7%/12.
For example, if you borrow $50,000 for 3 years with an annual interest rate of 8% and you make annual payments, the following formula will calculate the principal portion of a loan payment for period 1:
=PPMT(8%, 1, 3, 50000)
If you are going to make monthly payments on the same loan, then use this formula:
=PPMT(8%/12, 1, 3*12, 50000)
Instead of hardcoding the arguments in the formula, you can input them in the predefined cells and refer to those cells like shown in this screenshot:
If you prefer to have the result as a positive number, then put a minus sign before either the entire PPMT formula or the pv argument (loan amount). For example:
=-PPMT(8%, 1, 3, 50000)
=PPMT(8%, 1, 3, -50000)
To successfully use PPMT formulas in your worksheets, please keep in mind the following facts:
And now, let's take a couple of formula examples that show how to use the PPMT function in Excel.
Supposing, you want to calculate the payments on the principal for a loan. In this example, that will be 12 monthly payments, but the same formula will work for other payment frequencies as well such as weekly, quarterly, semi-annual or annual.
To save you the trouble of writing a different formula for each period, enter the period numbers in some cells, say A7:A18, and set up the following input cells:
Based on the input cells, define the arguments for your PPMT formula:
Now, put all the arguments together and you will get the following formula:
=PPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4)
Please pay attention, that we use absolute cell references in all the arguments except per where a relative cell reference (A7) is used. This is because the rate, nper and pv arguments refer to the input cells and should remain constant no matter where the formula is copied. The per argument should change based on the relative position of a row.
Enter the above formula in C7, then drag it down to as many cells as needed, and you will get the following result:
As you can see in the screenshot above, the total payment (calculated with the PMT function) is the same for all the periods while the principal portion increases with each successive period because initially more interest than principal is paid.
To verify the results of the PPMT function, you can add up all the principal payments by using the SUM function, and see if the sum equals the original loan amount, which is $20,000 in our case.
For this example, we will utilize the PPMT function to calculate the payments on the principal required to increase an investment from $0 to the amount you specify.
Since we are going to use the full form of the PPMT function, we define more input cells:
As with the previous example, for rate, we divide the annual interest rate by the number of payments per year ($B$1/$B$3). For nper, we multiply the number of years by the number of payments per year ($B$2*$B$3).
With the first payment period number in A10, the formula takes the following shape:
=PPMT($B$1/$B$3, A10, $B$2*$B$3, $B$4, $B$5, $B$7)
In this example, the payments are made at the end of each quarter over a period of 2 years. Please notice that the sum of all principal payments equals the future value of the investment:
If a PPMT formula is not working correctly in your worksheet, these troubleshooting tips may help:
That's how you use the PPMT function in Excel. To get some practice, you are welcome to download our PPMT Formula Examples. I thank you for reading and hope to see you on our blog next week!
Table of contents