# Calculating an APR without all variables

• Nov 27th 2007, 10:48 AM
Artorius
Calculating an APR without all variables
Interesting forum and probably my last hope of success.

I have until 2pm UK time tomorrow to complete a project that hinges on the following:-

I need to calculate the APR on 15,000 loans using Excel where the following data only exists:-

Column A: Original Amount Advanced
Column B: Term (Months)
Column C: Total Repayment

For example,
Original Amount Advanced: 10,000
Term(Months) 120 (i.e. 10 years)
Total Repayment: 14,000

In Excel Column D, I'd like to calculate the APR.

I have tried an Excel forum and its too advanced even for the "Guru's" who said it cannot be done without splitting out payments etc. I've taken a look at the APR calculation on the OFT (UK Office of fair trading) website and it's way too advanced for my primitive brain.

Can someone provide some form of solution for me? I'm still in my probation period at work and I'm really desperate for a result on this. Thank you in advance.
• Nov 27th 2007, 12:10 PM
Artorius
A big thank you for the reply and its similar to the formulas that I have stared at and through for most of today:-

"For this simple type of loan you have to solve the following equation:

$P=\frac{MR}{(1+r/100)^{1/12}} ~
\frac{1-(1+r/100)^{(N-1)/12}}{1-(1+r/100)^{1/12}}$

Where MR is the monthly repayment, N the number of periods, r the
percentage APR, and 12 the number of periods in a year."

The bad news is that I do not have the MR and want to calculate r.

But thank you anyway, was almost saved :-)
• Nov 27th 2007, 12:48 PM
CaptainBlack
Quote:

Originally Posted by Artorius
A big thank you for the reply and its similar to the formulas that I have stared at and through for most of today:-

"For this simple type of loan you have to solve the following equation:

$P=\frac{MR}{(1+r/100)^{1/12}} ~
\frac{1-(1+r/100)^{(N-1)/12}}{1-(1+r/100)^{1/12}}$

Where MR is the monthly repayment, N the number of periods, r the
percentage APR, and 12 the number of periods in a year."

The bad news is that I do not have the MR and want to calculate r.

But thank you anyway, was almost saved :-)

I have deleted the post you refer to becuase it was wrong, and too fiddly to get right

RonL
• Nov 27th 2007, 12:49 PM
CaptainBlack
Quote:

Originally Posted by Artorius
A big thank you for the reply and its similar to the formulas that I have stared at and through for most of today:-

"For this simple type of loan you have to solve the following equation:

$P=\frac{MR}{(1+r/100)^{1/12}} ~
\frac{1-(1+r/100)^{(N-1)/12}}{1-(1+r/100)^{1/12}}$

Where MR is the monthly repayment, N the number of periods, r the
percentage APR, and 12 the number of periods in a year."

The bad news is that I do not have the MR and want to calculate r.

But thank you anyway, was almost saved :-)

The equation is wrong, but you do have the monthly repayments.

Total repayed 14000, number of periods 120, so repayment per period is
14000/1200.

RonL