Help Calculating APR's & Interest!

[Taffam]

Hi guys

First post here, please be gentle!

I am desperately trying to work out a formula to calculate loan repayments and more specifically how to calculate an APR. I am really stuck trying to do this for short term loans (loans that are over a period of up to 12 months, repaid either weekly or monthly.

The data I have is the loan amount, the loan period and the interest rate p.a. I think I have the calculation right to work out monthly payments using the PMT function, yet calculating the APR seems somewhat trickier

Here's the data for monthly loans:

screenshot.19.jpg

The formula in Monthly Payment (B8) is =PMT(b6/12,b5,-b4), the formula in Total Repayment (B9) is =B8*B5 and in Total Interest is =B9-B4

The formula I have tried to calculate the APR is =((B9/B4)^(12/B5))*100-100 however the result seems to be a million miles off what the lender states - mine says 44699 and the lender states an APR for this amount/loan of 2261%

Actually, this is the advertised example from the lender: Loan Amount £500, duration 5 months (5 payments). Rate of interest 30% (fixed). Total repayable £950. Interest rate 353% per annum (fixed). Representative APR 2261% APR.


Thanks!!