Tiered balance calculation

[essjays]

Hi guys,
I am trying to create a formula which will calculate the interest rate based on a tiered structure for an account balance

For example

Interest structure-

0 - 999,999 = 0.00% p/a
1,000,000 - 1,999,999 = 0.5% p/a
2,000,000 - 9,999,99 = 1.25% p/a
anything above 10,000,000 = 2.00% p/a

Say the 5 day of account balances are as follows..
Day 1 = 200,000
Day 2 = 1,300,00
Day 3 = 12,000,000
Day 4 = 3,000,000
Day 5 = 1,750,000



Would really appreciate some help!

 

[James006]

Hi,

Instead of writing a long explanation ...

Better to dive into a working example : Excel Formula to Calculate Commissions with Tiered Rate Structure - Excel Campus