Question: I’m looking at taking out a loan from the bank. I know how much for, and what the interest rate would be, but how do I figure out what the payments would be per month?
Answer: The PMT function can help
Process (Excel 2003 and 2007):
When it comes to finance, personal or business, knowing what you’re paying or how much you’re spending is paramount. Knowing how you can save money by adjusting your payments is a very handy thing, and we can use the PMT function to not only work this out, but see what we would have to pay before we enter into a loan agreement.
The PMT function has the following syntax:
=PMT(rate,nper,pv,[fv],[type])
Rate: this is the interest rate to be paid per period
Nper: this is the number of periods in the life of the loan (i.e. the number of payments to be made)
Pv: the present value of the loan amount
[Fv]: the future value (optional)
[Type]: shows whether payment is to be made on the first day or the last day of the month
In this example, we will use the following information to see how much we would need to pay back in monthly instalments on a $100,000 loan over seven years.
1. Select cell B9, type in =C5/12 and press Enter
In order to work out the amount per period, we need to divide the yearly interest rate (12%) by the number of periods in the year. In this case, we’re making payments on a monthly basis, hence dividing the rate by 12.
2. Select cell C9, type in =C4*12 and press Enter.
The loan is over 7 years, with payments made monthly. Therefore we need to work out the total number of periods, hence multiplying the loan years by 12.
3. Select cell D9, type in =C3 and press Enter.
We could directly reference cell C3 when we create our formula, but for consistency in the example layout we will reference it to our demonstration line.
4. Select cell E9, type =pmt(B9,C9,D9) and press Enter
The formula references the interest rate for the period (rate = B9), the number of periods (nper = C9) and the present value of the loan (pv = D9). As [fv] and [type] are in square brackets, they’re optional values and don’t need to be entered. When you press enter, you will see the monthly amount to be repaid:
The amount is represented as a negative figure because this is money that you will be paying out. If you would like to see it as a positive number, simply multiply the result by -1.
5. Select cell F9, type in =E9*C9 and press Enter
You can now see how much will be repaid over the life of the loan. However, you can start playing with the numbers to see what effect it will have on the overall amount.
6. Select cell C4 (the loan years amount), type in 5 and press Enter
Changing the number of years to 5 changes the number of total periods from 84 to 60 (seen in cell C9). This means an increase in the monthly payment amount from $1,765.27, however it decreases the total amount repaid from $148,282.96 to $133,466.69
