Search Not Just Numbers

Tuesday 25 November 2014

Excel Tip: The PMT function - calculating loan payments in Excel

Excel has a baffling array of functions under the heading of "Financial", many of which are only likely to be used by financial analysts. However, over the next few weeks, I will tell you about a few that can be very useful to the rest of us.

This week, I want to introduce you to the PMT function, which allows you to calculate payments on a loan.

Before we get into how the PMT function works, it is useful to note what types of loan it will work for (and what types it won't!).

The function works for loans with a constant interest rate and regular payments of the same amount. The only exception to this is that it does allow for a 'balloon payment' at the end of the loan.

If this is the nature of the loan payment you want to calculate, then the PMT function will work for you.

Essentially, you feed the function the loan details, and it returns the regular payment amount.

The syntax of the PMT function is:

=PMT(Interest Rate,No of Periods,Present Value,[Future Value],[Type])

The last two arguments are optional and we'll come to those later in this post.

The three mandatory arguments are explained below:

Interest Rate: This is the interest rate per payment period, so if you have an annual interest rate of 6% with monthly payments, the Interest Rate entered shound be 0.5% (i.e. 6% divided by 12).

No of Periods: This is the number of periods or payments of the loan. A 5 year loan with monthly payments would be 60, for example.

Present Value: This is the value, today, of the loan and for a new loan will be the loan advance amount.

So, say we have a £10,000 loan over 5 years with monthly payments and an annual interest rate of 12%:

Interest Rate = 12%/12 = 1% or .01
No of Periods = 5 x 12 = 60
Present Value = £10,000

So our function becomes:

=PMT(.01,60,10000)

and returns a monthly payment of £222.44. Note that the function actually returns -222.44 as we entered Present Value as  positive figure. If the receipt of £10,000 is positive, then for consistency, the payment returned is negative. We could enter the £10,000 as a negative number (i.e. a negative payment), which would return a positive payment.

In reality, we wouldn't normally enter these figures into the function directly, more likely we would hold them in cells so that we could experiment with changing their values. and the function would be more like =PMT(B1,B2,B3), where Interest Rate, No of Periods and Present Value were held in cells B1, B2 and B3 respectively.

The final two (optional) arguments work as follows:

[Future Value]: This is where you would enter a 'balloon' payment, or any balance that you wish to remain outstanding at the end of the period you are looking at. You might have a final 'balloon' payment of £5,000, and therefore the payment calculated would need to leave exactly £5,000 outstanding when No of Periods comes to an end. Alternatively, you might recalculate payments annually to hit a specific target balance at the end of each year. This should be entered with the same sign as the payments (i.e. the opposite sign to Present Value). This arguments is treated as zero if omitted, i.e. the loan will be completely paid off after all payments are made.

[Type]: This is a 1 or a 0 and indicates when in each period the payments are made. If this is 0, or omitted, the calculation assumes that payments are made at the end of each of the periods in No of Periods. Enter 1 for this argument if payments are made at the start of each period.


If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

4 comments:

  1. Worth noting that the "period" is the capitalisation period... so if you have a mortgage where the interest is calculated and added annually then you would need to use the nominal APR interest rate, the term in years as the number of periods and then divide the result by 12 for the monthly payment...

    ReplyDelete
  2. In the US -
    I don't know about the UK - interest is often calculated based on a 360 day year. My Excel calculations will only match the bank's calculations if I take account of this by multiplying interest by 365/360

    ReplyDelete
    Replies
    1. This function doesn't really take any of that into account as it assumes that all periods are equal. It does not distinguish between, say, 12 monthly payments with a monthly interest rate, 12 weekly payments with a weekly interest rate or 12 annual payments with an annual interest rate. For this function, these are all the same calculation.

      Delete
  3. I never knew that Excel has a baffling array of functions under the heading of "Financial", many of which are only likely to be used by financial analysts. I think matrix calculator wont be needed anymore.

    ReplyDelete