The tutorial shows how to use PPMT function in Excel to calculate and return the payment on the principal for a given period for an investment based on periodic, constant payments and a constant interest rate.
This tutorial applies to the following Microsoft Excel versions: Excel for Microsoft 365, Excel 2021, 2019, 2016, 2013, 2010, 2007, and 2003.
I recommend downloading the Excel spreadsheet I've used to create this tutorial. You can check all the formulas and make changes if you'd like.
PPMT is a financial function that returns the principal payment for a loan. PPMT is the abbreviation for principal payment.
The PPMT function uses the following syntax:
=PPMT(rate, per, nper, pv, [fv], [type])
Note: If omitted, the optional parameters fv and type arguments have a default value of zero.
Let's look at a mortgage loan example with a period of 30 years and a constant annual interest rate of 5%. The amount borrowed from the bank is $170,000, and the payments are due at the end of each month. After the last payment is made, the residual value is $0 (i.e., the entire loan is paid).
Now, assume that we want to calculate the principal portion of the 24th payment; all we need to do is follow a few simple steps.
Adding the minus sign at the beginning of the formula is needed to convert the result to a positive number. Also, since we assume that we fully repay the loan, I omitted the future value (fv) argument.
When using financial functions in your formulas, always make sure that you set the correct payment frequency and adjust the rate and nper arguments accordingly. For easier reference, I have included a table that shows how to convert from annual payments to weekly, monthly, quarterly, and semi-annual payments:
|weekly||annual interest rate / 52||years * 52|
|monthly||annual interest rate / 12||years * 12|
|quarterly||annual interest rate / 4||years * 4|
|semi-annually||annual interest rate / 2||years * 2|
Now let's look at how the first principal payment changes when the payment frequency and interest rate are adjusted. To do this, I have prepared a simple example of a $10,000 loan with a 6.00% annual interest rate and duration of 5 years.
=-PPMT(6%/52, 1, 5*52, 10000)
=-PPMT(6%/12, 1, 5*12, 10000)
=-PPMT(6%/4, 1, 5*4, 10000)
=-PPMT(6%/2, 1, 5*2, 10000)
=-PPMT(6%, 1, 5, 10000)
Note: Principal payments are due at the end of each period. As a result, the type argument has been omitted. The future value of the loan is considered $0.
The PPMT function is relatively easy to use. However, there are a few things I would recommend to remember:
Like other Excel functions, things can go wrong, and different errors may be returned. If your PPMT function is not working correctly, you should check the following:
This tutorial includes several formulas that help you understand how to calculate the principal payment of a loan (not interest included). If you are looking to calculate the total loan payment, you should use the PMT function. In addition, the IPMT function allows you to quickly calculate the interest portion for the same loan.
I strongly encourage you to leave comments if you have difficulties understanding how the PPMT function works or if you need help writing your first formulas. I want you to get better at using Excel, and I think asking questions is the fastest way to do so.
My name is Radu Meghes, and I'm the owner of excelexplained.com. Over the past 15+ years, I have been using Microsoft Excel in my day-to-day job. I’ve worked as an investment and business analyst, and Excel has always been my most powerful weapon. Its flexibility and complexity make it a highly demanded skill for finance employees. I launched excelexplained.com back in 2017, and it has become a trusted source for Excel tutorials for hundreds of thousands of people each year.
If you'd like to get in touch, you can contact me on LinkedIn.
Thousands of people have benefited from my free Excel lessons. My only question is, will you be next?
Please log in again. The login page will open in a new tab. After logging in you can close it and return to this page.