Calculate Monthly Payment from APR

[mjg060468]

Hi

I am trying to create a workbook that calculates a monthly payment from just inputting the loan amount, APR and Term of loan.

I am currently using the following formula, however, it is calculating a monthly payment which is out by between £2 - £4

P = (L * t * (1+t)^n) / ((1+t)^n -1)

Where
L is loan amount
t is the interst rate ((APR/100)/12)
n is the number of month the loan is over

Can anyone help me?

[Sam Capricci]

There is a payment function in excel. You input the rate which can be like 5%/12, the NPR which is the total number of payments, the present value, the future value and type (payment at the first of the month or the end of the month - I usually omit this or leave it at 0).
it will likely give you a negative number but it is pretty accurate.

[Richard Buttrey]

Hi,

I think the formula you want is

L * t / (1 - (1 + t)^-n

or using Excel's PMT function

Please Login or Register  to view this content.

[mjg060468]

Thanks for the replies. However, both the formula and the PMT function are giving quite a bif minus number.

Example. Loan amount £545 over 48 months with an APR of 10.9%.

The formula I am using is giving a figure of £14.06 per month. The PMT function of PMT(10.9,48,545) is giving a result of £-5,940.50.

However, calculating it through a finance company online proposal form, the monthly amount is £17.69

Confused, am I not using the PMT function correctly?

[Sam Capricci]

I'm getting 14.06 (mine is in dollars but numbers are numbers) and this is how it looks =PMT(10.9%/12,48,545,0,0) and my finance calculator gives the same value.
the excel output will show as a negative value. Now if I change "10.9%/12" to 10.9 without the percent or the divide by 12 it gives me the 5940.50 value. Try it as I've shown it and see if that isn't correct. Not sure what your finance company online proposal form is showing but I have two different ways giving me the same 14.06 value.

[Richard Buttrey]

Hi,

Correct. You're not using the PMT function correctly.

You are using an annual % of 10.9% but your number of periods are months.
You must always use the period % and not use an annual % if you're calculating payments per month.

You should divide the APR by 12 . i.e.

PMT(10.9%/12,48,545)

Which will give you £14.06, as will the formula

Without seeing the proposal form or understanding how they are choosing to calculate the payments it's difficult toknow how they get 17.69.
The nearest I can get is 17.66 if the period is 36 months and the payments are due at the end of the month rather than the beginning.
Who is the finance company?

[mjg060468]

Thanks guys

Sorry, user error on the finance proposal form. You all correct is around £14.60, give or take a few pennies which I can live with. Really appreciate responses.