1. Using the PMT function for an Annuity
In our previous lesson, we walked through the calculation of the monthly
auto loan payment or the annuity payment in Excel, this annuity payment
was the constant payment which pays off the loan in full and also
pays the interest. We had to calculate 36 discount factors and add them
to calculate the annuity factor, this took an extra bit of work.
There is a very useful Excel function we can use to find the
annuity payment instead, the payment or PMT function will calculate the
fixed regular payments of an annuity. We can use the PMT function to
calculate the payments on the auto loan that we've just looked at,
but we're going to need to think carefully about the inputs,
the five inputs we need are; the rate, NPER, the present value,
the future value and the type or when the annuity payments are made
within each period. Let's give this a go back in our Excel workbook.
So back on my workbook, I'll just scroll down far enough so I
can see some of my working from the previous lesson. And if you
look at the near the top left hand corner of the screen in E53, we
can see my annuity payments of 300.83, but there was also a lot
of work to get there. So let's see if we can use the
Excel PMT function to make our life a little bit easier.
Now remember, the loan was 9600, all right, so that's how much we're borrowing
and the interest rate was 8%, we're paying back over 3 years and
we're monthly payments, so the payments per year is 12. So the number
of payments is 12 times 3. So I'm just going to select the
inputs for the PMT function down below here just to make
working with the PMT function that little bit easier. The rate is the
rate per period, so we're going to take our annual rate and we're
going to divide it by 12, the number of months. The number of
periods. We've already calculated these 36 periods, the present value equals
9600, and the future value is 0, in other words, we want to
fully pay off this loan. So what are our monthly payments?
Well, we can use the PMT function, but I'm going to put my
minus at the front, just so I get a positive answer.
PMT open bracket, the rate per period, 0.67, the number of periods,
36, the present value, 9600, the future value is 0 because we're fully
paying off the loan and then the type is always in this course,
we are assuming that they're discounting the compounding or the payments
happen at the end of each periods, so the type is 0. So
notice how easy it is to use the PMT function when you put
the inputs in order like that. And what's the answer? 300.83, which is
the same answer that we got in the earlier lesson.
2. Let's practice!