APR calculation on an overdraft

S

Smoggonthetyne

New member
Joined
Jun 4, 2016
Messages
6
Hi everyone. This is my first time posting so please be nice :D

I am looking to calculate the Apr for an overdraft based on a formula that I have been prescribed by the FCA. In theory this is quite straightforward, but I'm getting a bit stuck.

The formula itself looks a bit scary for people like me who haven't done proper maths for a decade, but I can summarise it as the following:

W/((1+i)^(m/12)) + W/((1+i)^(m/12)) = R/((1+i)^(m/12)

where:
W is a withdrawal and is variable (the above should show that there is a 1st W and a 2nd W)
R is a repayment and just like the withdrawal is variable and can form multiple flows
i is the APR and is constant throughout the formula
m is the month since the first withdrawal and varies depending on when the withdrawal or repayment takes place

The issue I'm having is that I need to calculate the Apr as I know all the other factors, but for my simple brain the it is too difficult to do. Using goal seek in excel makes this a dream, but I have been told not to use an iterative "guess work" system and instead re-arrange the formula to output this in a simple form.

Here is an example that should help. A customer makes a withdrawal of £100 and repays £55 after 6 months and £55 after 12 months. From the formula I know that the following is true:
£100/((1+i)^(0/365)) = £55/((1+i)^(6/12)) + £55/((1+i)^(12/12))
when simplified I think it chould read as:
£100 = £55/((1+i)^0.5) + £55/(1+i)

Can anyone help me to find i and show the workings to do so? Ideally I would like to be able to rearrange it all into a neat formula. Remember, I can't use guess work. In pretty sure with goal seek it comes out at around 14% or 15%.

Thanks
 
Picking it up at this stage and for simplicity let 1+i =x for now

£100 = £55/((1+i)^0.5) + £55/(1+i)

\(\displaystyle 100=\frac{55}{x^1/2}+\frac{55}{x}\)

\(\displaystyle 100x = 55 x^{1/2} + 55 \) multiplying both sides by x

\(\displaystyle 100x - 55 x^{1/2} - 55 = 0 \) rearranging

\(\displaystyle 20x - 11x^{1/2} -11 = 0 \) dividing both sides by 5

You should recognise this now as a quadratic in \(\displaystyle \sqrt{x}\). Are you right from here?
 
Harry_the_cat said:
Picking it up at this stage and for simplicity let 1+i =x for now

£100 = £55/((1+i)^0.5) + £55/(1+i)

\(\displaystyle 100=\frac{55}{x^1/2}+\frac{55}{x}\)

\(\displaystyle 100x = 55 x^{1/2} + 55 \) multiplying both sides by x

\(\displaystyle 100x - 55 x^{1/2} - 55 = 0 \) rearranging

\(\displaystyle 20x - 11x^{1/2} -11 = 0 \) dividing both sides by 5

You should recognise this now as a quadratic in \(\displaystyle \sqrt{x}\). Are you right from here?
Click to expand...

Thanks for the reply Harry. If I'm right in my thinking, would I need to square each number to get 20x^2 + 11x - 121 = 0 then just apply the quadratic equation? It's been a long time so apologies if I'm being slow!

Realistically the equation could be longer as there could be 100s of deposits and withdrawals. Would it be possible to derive x (carrying forward your terminology) if for instance we had:

100 + 50/(x^0.5) = 90/(x^0.4) + 30/(x^0.7) + 60/(x)

I think the answer is that x=146, using goal seek, but no idea how to get there with formula.

Thanks again in advance for you help.
 
Smoggonthetyne said:
Thanks for the reply Harry. If I'm right in my thinking, would I need to square each number to get 20x^2 + 11x - 121 = 0 then just apply the quadratic equation? It's been a long time so apologies if I'm being slow!

Realistically the equation could be longer as there could be 100s of deposits and withdrawals. Would it be possible to derive x (carrying forward your terminology) if for instance we had:

100 + 50/(x^0.5) = 90/(x^0.4) + 30/(x^0.7) + 60/(x)

I think the answer is that x=146, using goal seek, but no idea how to get there with formula.

Thanks again in advance for you help.
Click to expand...

Yes and No.

Substitute u = √x

x = u2
[FONT=MathJax_Main]20[/FONT][FONT=MathJax_Math]x[/FONT][FONT=MathJax_Main]−[/FONT][FONT=MathJax_Main]11[/FONT][FONT=MathJax_Math]x[/FONT][FONT=MathJax_Main]1[/FONT][FONT=MathJax_Main]/[/FONT][FONT=MathJax_Main]2[/FONT][FONT=MathJax_Main]−[/FONT][FONT=MathJax_Main]11[/FONT][FONT=MathJax_Main]=[/FONT][FONT=MathJax_Main]0[/FONT]
[FONT=MathJax_Main]
20[/FONT][FONT=MathJax_Math]x [/FONT][FONT=MathJax_Main]− [/FONT][FONT=MathJax_Main]11[/FONT][FONT=MathJax_Math]x[/FONT][FONT=MathJax_Main]1[/FONT][FONT=MathJax_Main]/[/FONT][FONT=MathJax_Main]2[/FONT][FONT=MathJax_Main] − [/FONT][FONT=MathJax_Main]11[/FONT][FONT=MathJax_Main]=[/FONT][FONT=MathJax_Main]0[/FONT]→
20 x - 11√x - 11 = 0 → 20u2 - 11u -11 = 0

u1,2 = 1/40 * [11 ± √(121+880)] = 1/40 * [11 ± 31.63858] = 1.065965, -0.51596

x = u2 → x = 1.136281, 0.266219

x = 1 + i → i = 0.1363 = 13.63%
 
Last edited by a moderator:
Thanks both for the replies. I realise the formula doesn't make sense, which I think is a combination of my description and the formula itself!

Your answers give me a great steer for the original question. Any clues how to complete the follow up question?

Thanks
 
Denis said:
What d'heck is the "follow up" question?
Click to expand...

The follow up question was:

100 + 50/(x^0.5) = 90/(x^0.4) + 30/(x^0.7) + 60/(x)


I'll write it out in question form of it helps:
Mr A uses £100 of his overdraft facility. After 4 months he repays £90, but the next month draws down a further £50. He make two further repayments of the loan, the first for £30, 7 months after taking the original £100 drawdown, and a final payment 10 months after the original loan of £60. What is the APR of the loan facility?

You must use the formula that can be found at the following web address.

https://www.handbook.fca.org.uk/handbook/MCOB/10/3.html

As I mentioned in my opening post, this is something I haven't had to look at for a long time, so I might be a bit slow on these things. You'll note that my interpretation is an attempt the use the formula as per the link, but substituting 1+i with x.

Thanks
 
Smoggonthetyne said:

The follow up question was:

100 + 50/(x^0.5) = 90/(x^0.4) + 30/(x^0.7) + 60/(x)


I'll write it out in question form of it helps:
Mr A uses £100 of his overdraft facility. After 4 months he repays £90, but the next month draws down a further £50. He make two further repayments of the loan, the first for £30, 7 months after taking the original £100 drawdown, and a final payment 10 months after the original loan of £60. What is the APR of the loan facility?

You must use the formula that can be found at the following web address.

https://www.handbook.fca.org.uk/handbook/MCOB/10/3.html

As I mentioned in my opening post, this is something I haven't had to look at for a long time, so I might be a bit slow on these things. You'll note that my interpretation is an attempt the use the formula as per the link, but substituting 1+i with x.

Thanks
Click to expand...

That equation will convert to:

10u10 + 5u5 -9u6 - 3u3 + 6 = 0

There is no general equation - like quadratic equation - for solution of 10th degree polynomial. So there is no "closed-form solution"

To numerically approximate it - using Newton-Raphson method would be best (Solver in ms-excel uses this method).
 
Denis said:
Subhotosh borrows $100 at rate i%. He repays the loan in full with 2 semiannual payments of $55. Calculate i, then the resulting APR.
Click to expand...

Subhotosh does not borrow no stinking $. He would borrow ₹ and pay back in $ (better return rate).
220px-Indian_rupees.png
 
Thanks everyone for taking the time to reply. From what I have understood, there is no simple solution to the issue. Using the Newton-Raphson method, or another iterative estimating process would probably be the best way to solve it.

Looks like I'm going to have to work our how to code this now!

Thanks
 
You must log in or register to reply here.
Top