# How to use PMT function in Excel [step by step guide]

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.

**Contents**hide

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

## How to use PMT function

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.

## How to use the PMT function in Excel – formula examples

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.

### How to write a PMT formula in Excel

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.

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

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.

## Things to remember about the PMT function

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.

## Excel PMT function not working properly

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.

## What to do next?

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.