How to use PPMT function in Excel [step by step guide]

July 11, 2024July 11, 2024

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.

How to use PPMT function

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])

where

• rate (required) – the constant interest rate per period (i.e., the rate corresponding to your payment frequency). For instance, if you have a mortgage with a 6% annual interest rate, but your payments are made monthly, you need to convert that interest to a rate that matches the monthly payment (6%/12 = 0.5%).
• per (required) – the period for which you want to calculate the principal payment; it must be an integer in the range of 1 to nper;
• nper (required) – the number of payments during the lifetime of the loan;
• pv (required) – the present value of the loan; in case of a loan, this is the borrowed amount.
• fv (optional) – the future value of the balance after the last payment is made;
• type (optional) – indicates when the principal amount is due: 0 for the end of the period and 1 for the beginning of the period.

Note: If omitted, the optional parameters fv and type arguments have a default value of zero.

How to use the PPMT function in Excel – formula examples

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.

How to write a PPMT formula in Excel

1. Navigate to the cell where you want to insert your formula. I’ve used cell G5 in my example.
2. Type =-PPMT(
3. Insert the rate argument. Remember that we use monthly payments, so we need to divide the annual rate by 12. This corresponds to E3/12.
4. Enter the period for which you want to return the principal paid. As I said, we want the 24th payment (cell E7 from in my file)
5. Fill in the nper argument (i.e., the total number of payments). In our case, this is 360 months (cell E4 multiplied by 12).
6. Select the present value of the loan or the pv argument. I’ve used cell E6.
7. Type ) to close the function and press ENTER.

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.

Calculate weekly, monthly, quarterly, and semi-annual principal payments

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:

payment frequency	rate	nper
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.

Weekly payments:

=-PPMT(6%/52, 1, 5*52, 10000)

Monthly payments:

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

Quarterly payments:

=-PPMT(6%/4, 1, 5*4, 10000)

Semi-annual payments:

=-PPMT(6%/2, 1, 5*2, 10000)

Annual payments:

=-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.

Things to remember about the PPMT function

The PPMT function is relatively easy to use. However, there are a few things I would recommend to remember:

• Since Excel follows the sign convention, any values returned by the PPMT function are considered outgoing payments and shown as a negative number. You can convert the result to a positive number by adding the minus sign in front of your formula.
• The value returned by the PPMT function corresponds to a given period, and this is controlled by the per argument.
• The PPMT function can only calculate the cash-flows for a loan with a fixed interest rate.
• The PPMT function can be used to calculate principal payments using different frequencies (yearly, quarterly, monthly, weekly); remember to adjust the number of periods and to convert the annual rate to match your payment frequency.
• By default, PPTM assumes zero balance at the end of the periodic payments.

Excel PPMT function not working properly

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:

• If the result of your PPMT formula looks higher or lower than expected, make sure that your rate and nper arguments are consistent. Using mismatching interest rates or payment frequency will result in significantly higher or lower principal payments.
• If the PPMT formula returns the #VALUE! error, the first thing to check is if all the arguments are numeric. Numbers stored as text can mess up your formulas.
• If the IPMT formula returns the #NUM! error, it means that the per argument is outside the range of 1 to nper.

What to do next?

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.