PMT Function

[John Davies]

If I use the PMT function the montly instalment does not agree using
traditional maths. e.g. £10000.00 loan over 48 months @ 8% per annum. i.e
£10000.00 x 8% x 4 = £4800.00 interest
Total Loan amount = £10000.00 + £4800.00 interest = £14800.0
Monthly instalment = £14800.00/48 = £275.00

Using the PMT function i.e.
=PMT(8%/12,48,£10000.00) gives a monthly figure of £244.13
Please advise where I am going wrong
Thanks

 

[Niek Otten]

<£10000.00 x 8% x 4 = £4800.00 interest>

No. It is 1 month rent over 10000, 1 month over 9822.54, 1 month over
9643.89, etc. If you write out a 48 lines table, you'll see it matches the
result of PMT exactly

--
Kind regards,

Niek Otten

Microsoft MVP - Excel

 

[John Davies]

I understand what you are saying but loan companies agree with my monthly
instalment, as they are not worked on a reducing balance. Is there another
function that can be used to give the same figures as my traditional method?

Thanks

 

[Morrigan]

First off 10000 x 8% x 4 = 3200 not 4800

Interest is directly related to the principal amount. The way you
calculate is what it is like on a line of credit where you not forced
to repay the principal as long as you pay the interests. Therefore if
you borrow 10000, you will be paying 66.667 a month. After 4 years, if
you don't pay for principal, you have paid 3200.

PMT() is used to calculate mortgage where you pay both interest and
principal at the same time. After the first payment, your principal is
now less than 10000. Thus, the interest you pay on the next month is
less.

To calculate the amount you pay on the principal on your payment is as
follow:

PayOnPrincipal = iA/(1-(1+i)^(-n))

i = interest rate
A = loan
n = number of payment

In your case:
PayOnPrincipalOnFirstMonth = 8%/12*10000/(1-(1+8%/12)^(-48)) = 177.463

If you add this to the interest:
PaymentOnFirstMonth = 177.463 + 66.667 = 244.13

That is what PMT() gives you for constant monthly payment which will
pay off a loan of 10000 at 8% over a period of 4 year.

 

[Morrigan]

Don't you already know how to calculate it then? I am not understanding
what you want.