2
$\begingroup$

I'm looking at a way that I can solve the following issue.

If I borrow £1000 from a lender, and the interest rates we're different dependent on the value. How could I work out the total interest percentage regardless of the amount being lended?

For instance, say I have the following interest rates

Amount borrowed / Interest

£0 - £100 : 10%

£100 - £500: 20%

£500 - £1000: 30%

I want to borrow £1000, I would have the first £100 charged at 10% interest, then the next £400 would be 20% interest, and final £500 would be 30%.

Is there a formula that exists that could tell me the final combined interest rate? and would this formula work if I wanted to borrow say £400

Thanks

$\endgroup$
2
  • $\begingroup$ That's an awfully odd way of dividing interest between the parts of a sum borrowed ... Did you see this in real life, somewhere? $\endgroup$ – Matti P. Aug 13 '19 at 9:33
  • 1
    $\begingroup$ As Doug M points out, after one year, the amount due would be $$ 100 \cdot 1.1 + 400 \cdot 1.2 + 500 \cdot 1.3= 1240 $$ That's a growth of $24~\%$. After the second year, the amount would be $$ 100 \cdot 1.1^2 + 400 \cdot 1.2^2 + 500 \cdot 1.3^2= 1542 $$ That's a growth of $24.35~\%$ from the first year. So as you see, a "split up" interest like this doesn't have a constant combined interest rate. $\endgroup$ – Matti P. Aug 13 '19 at 9:37
2
$\begingroup$

$\frac {100\times 0.10 + 400\times 0.20+ 500\times 0.30}{1000}$

$\endgroup$
1
  • $\begingroup$ So simple now I see it. Thankyou $\endgroup$ – juicy89 Aug 13 '19 at 10:17
2
$\begingroup$

Are you referring to an Excel formula? Assuming that the amount is in cell A2, and in cells A4 to C6 we have respectively:

$A4=0, B4=100, C4=10$%

$A5=100, B5=500, C5=20$%

$A6=500, C6=30$%

Then the formula must be

=IF(AND(A2>=A4,A2<=B4),A2*(1+C4),IF(AND(A2>A5,A2<=B5),A5*(1+C4)+(A2-A5)(1+C5),IF(A2>A6,B4(1+C4)+(A6-A5)(1+C5)+(A2-A6)(1+C6))))

and it works for any amount.

$\endgroup$
1
  • $\begingroup$ Thanks for taking the time to reply. It's not for excel no, however this function does appear to work after inputting it into excel. I'm sure it's something I will use. $\endgroup$ – juicy89 Aug 13 '19 at 10:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.