A to Z of Excel Functions: The PMT Function

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:

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:

The PMTfunction 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.