## Sunday, March 17, 2013

### yogi_Set Up Formulation For Multi Tier Tax Computation

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Mar 17, 2013
Case of elaborate IF type statement in Google Docs Sheets

I'm building a commission calculator that estimates your monthly paycheque. Based on your monthly estimate you may fall into a higher tax bracket. For this reason, to accurately estimate income I need to evaluate the estimated income and apply appropriate tax bracket assessments.

In Alberta, Canada, the tax brackets go as follows (I'm only doing 3 for practicality reasons)

1, Taxable amount = \$43,561, Monthly that's equivalent to = \$3,630.08, 10% AB tax + 15% CDN tax
2, Taxable amount = \$43,562, Monthly that's equivalent to = \$3,630.17, 10% AB tax + 22% CDN tax
3, Taxable amount = \$47,931, Monthly that's equivalent to = \$3,994.25, 10% AB tax + 26% CDN tax

Running totals
If you made up to \$3,630.08/mo  = up to \$3,630.08 is taxable at 25%
If you made up to \$7,260.25/mo  = \$3,630.08 taxable at 25%,  and remainder up to \$3,630.17 is taxable at 32%
If you made up to \$11,254.50/mo = \$3,630.08 taxable at 25%,  \$3,630.17 taxable at 32%, and remainder up to \$3,994.25 is taxable at 36%

So essentially, in sudo code:

if( bracket 1, tax accordingly, else if( bracket 2, tax accordingly, else if( bracket 3, tax accordingly )))

or... if this were code of some kind...

case bracket1: tax1 break;
case bracket2: tax2 break;
case bracket3: tax3 break;

I'm having a hard time with this, just can't get my brain in gear. Any help you can offer would be appreciated.
--------------------------------------------------------------------------------------------------
following is the illustration of a little play I had with it ... there are several loose ends because of the incomplete spec from the use