Assuming that the formula goes into cell BD3, try the following:
Formula:
=ROUND(IF((TODAY()-E3)/365<60,IF(BB3<=250000,0,IF(BB3<=500000,(BB3-250000)*5%,IF(BB3<=750000,(BB3-500000)*10%+12500,IF(BB3<=1000000,(BB3-750000)*20%+37500,IF(BB3<=1250000,(BB3-1000000)*25%+75000,IF(BB3<=1500000,(BB3-1250000)*30%+125000,(BB3-1500000)*30%+187500)))))),IF((TODAY()-E3)/365>80,IF(BB3<=500000,0,IF(BB3<=750000,(BB3-500000)*10%,IF(BB3<=1000000,(BB3-750000)*15%+25000,IF(BB3<=1250000,(BB3-1000000)*20%+62500,IF(BB3<=1500000,(BB3-1250000)*25%+112500,(BB3-1500000)*30%+175000))))),IF(BB3<=300000,0,IF(BB3<=500000,(BB3-300000)*5%,IF(BB3<=750000,(BB3-500000)*10%+10000,IF(BB3<=1000000,(BB3-750000)*15%+35000,IF(BB3<=1250000,(BB3-1000000)*20%+72500,IF(BB3<=1500000,(BB3-1250000)*25%+122500,(BB3-1500000)*30%+185000)))))))),0)
Note that I agree with Pepe in that there are simpler ways to accomplish this.
Note that I only checked that the formula returns a value, not sure if the value yielded is correct.
Let us know if you have any questions.
Bookmarks