December 3, 2021

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

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.

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.

- Navigate to the cell where you want to insert your formula. I've used
**cell G5 in my example.** - Type
**=-PPMT(** - 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**. - 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) - Fill in the
**nper**argument (i.e., the total number of payments). In our case, this is 360 months (cell**E4**multiplied by**12**). - Select the present value of the loan or the
**pv**argument. I've used cell**E6**. - 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.

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.

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.

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

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.

Thousands of people have benefited from my free Excel lessons. My only question is, will you be next?

I have compiled a list of the best Excel online courses. It covers basic and advanced topics, VBA, and Power BI.

View coursesExcel is a very complex application, and understanding it on your own may prove difficult and time-consuming. I have created ExcelExplained.com to help you better understand each function and provide useful examples for different tasks. If you can’t find the answer to your question in one of the published articles, please leave a comment or send me an email, and I will do my best to help you out.

Affiliate Disclosure: When you purchase a product through a link on our website, I may earn an affiliate commission. There are no additional costs to you.

Copyright © 2017-2022 ExcelExplained.com