Yes gentlemen! you too can have PMT. Especially if you are in finance and need to do calculations for loans and the such. This is a function found in the Financial section of functions that calculates payments for loans. I use this function to demonstrate the fact that you need to know what you’re using before you use it in earnest. As there are several catches within this function. I shall explain.
This is the explanation you get if you ask for PMT when you go to the Insert Function either on the Formula bar
or from the Formula tab.
- Select either one.
Sounds great and sensible and straight forward.
- Now select it and open the dialog box.
You are presented with four areas to fill. But wait! Notice please, that the words to the left of each field are either bold or not bold. The words in bold are mandatory, whereas, the non bold fields are optional as Excel will fill them in as appropriately. Or so you think.
We shall go field by field.
Rate: – ( interest rate). This is the interest rate at the time of calculation. Please read the whole of the explanation at the bottom of the dialog box. It helps you to understand what you need to do here.
We tend to repay our loans on a monthly basis. Therefore, we need to divide the interest rate by 12 NOT by 4, to give us a monthly interest rate.
- You can either type in the interest rate, don’t forget the %, or select it from a cell in your spreadsheet.
Nper: – (number of payments). This is quite straight forward. Type in the number of times you will repay the loan or type in 12x the number of years you intend this to apply to. Again you can select a cell in your spreadsheet if you already have this calculation there. For example a 5 year loan would have 60 repayment periods.
Pv: – (Present Value). I just love the English in this sentence. It just really does NOT explain what is required. When my students read this out to me they hesitate and stammer a lot and look up at me with questioning eyes baffled by the confusing language. In my layman’s view all it should say is “what is the size of your loan?”. An accountant is probably more familiar with the term.
Fv: – (Future Value). (not in bold, therefore not necessary to fill in) this is where you can either say 0. Which makes the calculation of regular payments throughout the life span of the loan with the last being equal to all the rest. If on the other hand you have a Bond that is to mature just before the final payment of this loan and you wish to use it then. You can place this amount in the Fv field and the calculation changes, in some cases dramatically, to making your monthly payments smaller as the final payment is large. If you leave this field blank then Excel will assume that the entry is 0.
See example bellow.
Type:- (again NOT in bold and therefore assumed not to have to be entered). Please read the following carefully. Again the English here is appalling. “ is a logical value: payment at the beginning of the period = 1; payment at the end of the period = 0 or omitted.”
This means that if you pay at the beginning of the month place a 1 in this field. If you wish to pay at the end of the month either type in 0 or leave it blank and Excel will assume that and use 0.
It would be wise to note that in leaving it blank or typing in 0 you have just made your payment a bit larger as you have attracted a whole months interest on the bit of capital you could have paid off at the beginning of the month and over say a 25 year period this could be significant. It’s interesting that this is the default for leaving it blank!
For example:- you wish to have a loan of £100,000 for a 25 year period and the interest rate we shall set at 6.5%. here is how you would fill in the fields.
Simply changing the Type to a 1 this is the result.
In the first case the repayment would be £675.21, in the second case they would be £671.57.
675.21-671.57=3.64 per month.
It may not seam much per month but over the 25 years it accumulates to something worth thinking about.
Now how about the Future Value
For example :- you have a bond that will mature and give you £5000. We shall add this to the function and the result is this.
When do you use this function
This may seem like a complex function, but actually like many other financial function it can have simple applications.
If you loan a friend £1000 and decide not to charge them interest PMT(0%,12,-1000). I use the negative amount as this money is coming out of your funds. The repayment calculates out at £83.33 per month. Increasing interest rate to 1% :pmt(1%,12,-1000) means that the repayment is £88.85. Alternatively you might decide that at the end of the year your friend should have repaid you back £100 in addition to the loan amount: pmt(0%,12,-1000,-100) = 91.67. Note that it is worth using simple numbers just to understand how the calculation works out.
The future value (Fv) is sometimes confusing. In the example above if you loan your friend £1000 that is a debit to your account. The extra £100 is an amount that while doesn’t effect your current account balance, it will affect it in the future once the loan is repaid, it therefore increases the loan value and is entered as a negative number.
So there you have it your answer staring at you from the sheet. Hope it makes you smile, because you now know you can afford that item you need the loan for.