December 1, 2021

The tutorial shows how to use PMT function in Excel to calculate the annuity for a loan or the cash flows for investments based on a fixed interest rate, number of payments, and the total loan or investment amount.

This applies to the following Microsoft Excel versions: Excel for Microsoft 365, Excel 2021, 2019, 2016, 2013, 2010, 2007, and 2003.

Before you begin, I recommend downloading the Excel spreadsheet I've used to create this tutorial. It makes it much easier to follow along.

PMT is a financial function that returns the periodic payment for a loan based on a constant interest rate, the number of periods, and the loan amount.

**PMT **is the abbreviation for payment, which makes the function name easy to remember.

The PMT function uses the following syntax:

`=PMT(rate, nper, pv, [fv], [type])`

where:

**rate**(required) - the constant interest rate for the loan or investment; if you have a loan with 12% annual interest rate and**yearly**payments, then you would use 12% or 0.12; for monthly payment periods you would use 1% (12%/12);**nper**(required) - the total number of payments for the loan; for a 30 years loan that monthly loan payments, you would insert 360 (that's 30*12);**pv**(required) - the present value of all future payments; in the case of a loan, it's simply the amount borrowed.**fv**(optional) - the future value is the cash balance that you wish to have after the last payment is made; for a loan, this is usually zero, as you want to fully repay the loan;**type**(optional) - specifies when the payments are due: 0 for the end of the period, 1 for the beginning of the period.

Note: If omitted, the **fv **and **type **arguments use the default value of 0.

Let's look at a simple PMT formula and calculate the future payments for a loan, assuming constant payments and a constant interest rate.

We'll use an example for a house that costs $200,000, with a down payment of $30,000 and a loan amount of $170,000. The mortgage loan has a period of 30 years and an annual interest rate of 5.00%, and payments are due monthly.

Using the Excel file provided for this tutorial, you can write the PMT formula in 6 steps:

- Select the cell where you want to insert the
**PMT**formula**.**Using your mouse or keyboard navigate to the cell where you want to insert your formula. In my file, this is**cell F4**. - Type
**=-PMT(** - Select the
**rate**argument. In my case, this is**C3/12**because we are using monthly payments. - Enter the number of periods or the
**nper**argument. In this example, this is**C4*12**which is equal to 360 months. - Select the present value of the loan or the
**pv**argument. I've used cell C6. - Type
**)**to close the function and**press ENTER**.

Since the **fv **and **type **arguments were omitted, Excel used their default value of zero. The minus sign at the beginning of the formula is used to convert the amount to a positive figure.

The same function can be used to write formulas that calculate periodic payments for a mortgage loan, a car loan, or a student loan. As long as the interest rate is constant, the PMT function can be used to determine the loan payment.

Depending on the payment frequency, you will have to convert the **rate **and **nper **arguments. The following table shows how to easily convert from annual interest rate and 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 |

To calculate the amount of a periodic payment for a $10,000 loan with a 6.00% annual interest rate and duration of 5 years, you can use one of the following PMT formulas:

**Weekly **payments:

`=-PMT(6%/52, 5*52, 10000)`

**Monthly **payments:

`=-PMT(6%/12, 5*12, 10000)`

**Quarterly **payments:

`=-PMT(6%/4, 5*4, 10000)`

**Semi-annual **payments:

`=-PMT(6%/2, 5*2, 10000)`

**Annual **payments:

`=-PMT(6%, 5, 10000)`

Note: The balance after the last payment (i.e., the **fv** argument) is assumed to be $0. Payments are due at the end of each period, so the **type **argument is omitted.

If you want your PMT formula to work properly you need to take into account the following:

- The returned PMT value is a
**negative number**since these are periodic payments; you can add the minus sign in front of your formula to convert the result to a positive number; - The value returned by PMT includes principal and interest amounts but does not include any fees, taxes, or reserve payments associated with a loan;
- The PMT function can compute a loan or investment cash flows for different frequencies (yearly, quarterly, monthly, weekly); all you need to do is adjust the total number of periods and convert the annual rate to match your payment frequency.

If your Excel PMT formula is not working or returns an error message, there are a few things to investigate:

- If the result of your PMT formula is significantly higher or lower than expected, check to see if your
**rate**and**nper**arguments are consistent. Using an annual interest rate with monthly payments will result in a much higher monthly payment. - If your PMT function returns the #VALUE! error, then one of the arguments is text. Check your data and make sure you don't have numbers stored as text.
- If your PMT function returns the #NUM! error check if the
**rate**argument is negative or if the**nper**argument is equal to 0.

The formulas from this Excel tutorial return the total value of a payment (includes principal and interest). If you are looking for a way to split the interest and principal, two financial functions can help you achieve this: the PPMT function and the IPMT function.

As always, if you are having difficulties using the PMT function, please leave a comment, and I'll reply as soon as possible.

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