# A to Z of Excel Functions: The PMT Function

23 October 2023

*Welcome
back to our regular A to Z of Excel Functions blog. Today we look at the PMT function. *

This function is often referred to as the *mortgage calculator*. Here, the aim is to calculate what the
regular repayment is per period to service and pay off a debt over a given
amount of time – just like a common mortgage, but with a fixed (rather than a
variable) interest rate.

For example, if I borrow $300,000 over 25 years at an interest rate of 6% p.a. what will my regular monthly payments be (assuming no change of rate)?

The answer to this question is given by the formula:

**P = Ai / (1 – (1 + i) ^{-N}) **

where:

**P**= regular periodic payment**A**= amount borrowed**i**= periodic interest rate**N**= total number of repayment periods

(Interesting that the acronym for
remembering the mortgage variables is **PAiN**!)

In our example, crunching the numbers
(using a periodic interest rate of 0.50%, being 6% / 12 (since the interest
does not compound as it is paid monthly) and total number of periods being 25 x
12 = 300) gives a monthly repayment of $1,932.90, *viz.*

It should be noted that using **PMT** will give the same solution, but be
negative instead. This is because
Excel’s financial functions distinguish between cash inflows (positive) and
outflows (negative).

**PMT** calculates
the payment for a loan based on constant payments and a constant interest rate. It employs the following syntax to operate:

**PMT(rate, nper, pv, [fv], [type])**

The **PMT**function has the following arguments:

**rate:**this is required and represents the constant interest rate for the loan**nper:**this is also required and denotes the total number of payments for the loan**pv:**also necessary, this is the present value, or the total amount that a series of future payments is worth now, also known as the principal (*i.e.*what you are borrowing)**fv:**this is the first of two optional arguments. This is the future value, or a cash balance you want to attain, after the last payment is made. If**fv**is omitted, it is assumed to be zero (0),*i.e.*the future value of a loan is nil**type:**this final argument is also optional. This the number zero (0) or one (1) and indicates when payments are due:

It should be further noted that:

- the payment returned by
**PMT**includes principal and interest but considers no taxes, reserve payments or other fees sometimes associated with loans - make sure that you are
consistent about the units you use for specifying
**rate**and**nper**. If you make monthly payments on a four-year loan at an annual interest rate of 12%, use 12%/12 for**rate**and 4*12 for**nper**. If you make annual payments on the same loan, use 12% for the**rate**and 4 for**nper** - to find the total amount
paid over the duration of the loan, multiply the returned
**PMT**value by**nper**.

* *

*We’ll
continue our A to Z of Excel Functions soon.
Keep checking back – there’s a new blog post every other business day.*