How do I calculate different percentages depending on the amount?

**woofer*wolf** · 09-13-2012, 12:46 AM

I'm having a great deal of trouble figuring out a formula... Say a company is paying $75,000 a month for royalties for a patent. The patent office pays the inventor 40% for the first $50,000 of total royalties accrued; 35% when the total amount accrued is between $50,000 and $100,000; and 30% when the total amount accrued is over $100,000. How would I make a formula that figures out the total amount the inventor gets after each pay period?

I thought that a nested IF statement would work, and I came up with this (A1 would be the total amount accrued), but it doesn't quite work:
=IF(A1<=50000,A1*0.4,IF(A1>=50000<=100000,(50000*0.4)+((A1-50000)*0.35),IF(A1>100000,(50000*0.4)+(50000*0.35)+((A1-100000)*0.3))))

Any help the community can provide would be deeply appreciated!

**dilipandey** · 09-13-2012, 12:49 AM

Hi woofer,

Welcome to the forum.

Just corrected the syntax issues in your formula:-

Formula:

Please Login or Register  to view this content.

Regards,
DILIPandey

<click on below 'star' if this helps>

**woofer*wolf** · 09-13-2012, 12:54 AM

Thanks for the syntax correction, but the formula is still not outputting the correct answer. The percentages should be calculated from each $75,000 payment, but they need to account for the total amount accrued. This is where I am having trouble.

For example:
For the first accrual period ($75,000 in royalties), the inventor should receive $28,750. This is because he gets 40% of the first $50,000 (which is $20,000) and 35% of the remaining $25,000 (which is $8,750). This comes to a total of $28,750. For the next accrual period (again, $75,000 in royalties, now a total of $150,000), the inventor should receive $23,750. This is because he gets 35% for the first $25,000 (which is $8,750), and 30% of the remaining $50,000 (which is $15,000). For the next accrual period (again, $75,000 in royalties) and all subsequent accrual periods, the inventor would only receive 30% of the $75,000 because the total accrued is over $100,000. Does this make sense?