Thread: Loan Repayment

Hi, This is a query relating to the calculation method for the repayments of loan.
Example:- Using the PMT Function on a Loan of £7500, Over 4 years Monthly repayments @ 8.7%. The Function instructions uses the Yearly interest rate divides by 12.
So basing the calculation on "0.087/12" you get :-
In this case the answer :-=PMT(0.087/12,48,7500) Ans =£185.57
If you now look at the actual Repayments on this loan from any Reliable Loan Company, you will see the actual repayment is £184.41 This is calculated as follow:- Using the Twelth root of the (interest rate+1 )-1
i.e.1.087^(1/12)
=PMT(1.087^(1/12)-1,48,7500)= £184.41
As you are Compounding the Interest, the repayments from the second method seems the more logical, so WHY does the PMT Function return the value it does, by calling for the interest rate to be divided by 12.
Any enlightenment , much appreciated.
NB:- I have check these result over several Loan Companies and various loans amounts /interest rates and get the same Basic result.
Regards Mick

185.57 is the correct payment IF the rate is quoted as 8.7% annual
compounded monthly.

184.41 is the correct payment IF the rate is quoted as 8.7% EFFECTIVE
annual compounded monthly.

Rate needs to be converted:
(1 + r)^12 = 1.087 : after compounding, result is 8.7%
r = .006976.... (monthly)

.006976 * 12 = .0837122.... (approx. 8.37%)
In other words, an annual rate of 8.37 if compounded monthly
will result in an EFFECTIVE rate of 8.7%

Hope that helps.

Hi, Thank you for your reply.
I understand your equations, but not so sure on the logical purpose of them.
This is an example of my query.:-
If you invest £100 for 2 years @ 10% the equation is 100 x 1.1^2 = £121
i.e Monthy compounded = 100 x 1.1^(1/12)^24 = 100x1.007974^24 = £121. i.e. The rate used is the 12th root of 1.1 = 1.1^(1/12) =1.07974

If you do this monthy compounding the "PMT" way you get :-
100 x[(1.1-1)/12+1]^24 = £122.039 i.e. The compounding rate used is the interest rate 0.1 divided by 12 +1= 1.0083333.
This means in the first way ( to my mind) you get the correct answer using a rate of 10% and in the second , using the PMT way you get an interest rate of = [(0.1/12)+1]^12 =10.4713.
Which does not appear correct.
The real question is, if Loan companies use the first method (returns a logical and fair answer) Why does "Excel" with its PMT Function use a methods that apparently returns an illogical Ans.
Regards Mick

To start, on your original example, 184.41 is the CORRECT payment IF the rate used is 8.37%, NOT 8.7%; already told you that, and explained as
well as I could (not in a classroom!).

We'll now take your 100 at 10% example; make it one year:

100 * 1.10 = 110.00 : EVIDENTly 10% annual
If paid monthly:
100 * (1 + .10/12)^12 = 110.47 : 10.47% annual

The object is to pay monthly and end up with 10% annual;
so of course a lesser rate must be used:
(1 + r)^12 = 1.10 ; solve for r ; r = .007974 monthly :
.007974 * 12 = .09568, 0r 9.57%

In other words:
paying 10% only once per year results the SAME as paying 9.57% 12 times per year;
BOTH methods will have 100 end up at 110.