# Interest rate brackets - calculating final rate depending on amount borrowed

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

• That's an awfully odd way of dividing interest between the parts of a sum borrowed ... Did you see this in real life, somewhere? – Matti P. Aug 13 '19 at 9:33
• 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. – Matti P. Aug 13 '19 at 9:37

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

• So simple now I see it. Thankyou – juicy89 Aug 13 '19 at 10:17

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.

• 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. – juicy89 Aug 13 '19 at 10:19