In order for this calculation to work, you require 3 items: the amount being borrowed; the interest rate that will be charged; and the term over which the loan will be re-payed.
First layout your spreadsheet with the required information, and click into the cell that you would like the answer (i.e. the monthly installment) to appear. Now switch to the Formulas tab on the ribbon, and from the Financial list scroll down to find the PMT calculation.
The Function Arguments window will appear with 3 required fields (for the information that you have ready) and 2 further optional fields that I'll explain a little further down. A clue to indicate which fields are required vs which are optional, is that the required fields have a bold title.
Place your cursor into the first required field, Rate, and click once on the cell that contains your interest rate value. Typically the interest rate that you are quoted is the APR (Annual Percentage Rate) and as such the amount is what you will be charged per year. Because we are attempting to calculate the monthly installment, we need to give Excel the monthly interest rate. You are able to do so by dividing this APR value by 12.
Now place your cursor into the second required field, Nper. This field represents the total number of installments you will be paying. So if your loan is taken out over a 10 year period, you won't be making 10 payments in total, but rather 10 years of 12 months each, so a total of 120 payments. Click once onto the cell that contains your term value, and then type *12
The final required field is PV. This part of the calculation is where you indicate the PresentValue i.e. the loan amount that needs to be re-payed. Ensure that your cursor is in the PV field, type a minus sign and then click once onto the cell on your spreadsheet that contains the loan amount. Typing a minus sign is not required, but it's a clever way to ensure that the calculated monthly repayment does not appear as a negative value.
At this point you can click OK to see installment that will be required for this loan. Please remember that many loan companies will have their own additional costs that will mean this will not be an exact amount but it will at least give you a good idea of what to expect.
There are however, two additional fields are not required in order for the calculation to work correctly, but they can have an impact on the answer.
The first of these fields is FV. This is the FutureValue - the amount that the loan needs to reach by the end of the loan period. This would typically be zero - you want to pay the loan off in full over the course of the loan period and if you leave this field blank, Excel will assume you wish to pay off the entire loan. Of course this isn't always the case, and sometimes your loan may allow for what is known as a balloon payment / residual payment. If you have such a balloon payment, you still enter the full loan amount into the PV field, and the balloon amount into the FV field.
The second optional field is Type. This is used to indicate when payment is required - if you are required to pay at the end or beginning of the period. Simply put, is your payment due at the end of the month or beginning. If you leave this field blank, it is assumed you pay at the end of the month. You can enter a 1 to indicate payment is due at the beginning of the period, whilst a 0 indicates payment at the end of the period.
I'm wondering if this post was heavy reading for you? Would you prefer to have seen this tutorial as a 2 or 3 minute video clip? Let me know you're thoughts
No comments:
Post a Comment