Interest rate brackets - calculating final rate depending on amount borrowed

Question

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

Answer 1

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

Answer 2

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.