Sunday, March 17, 2013

yogi_Set Up Formulation For Multi Tier Tax Computation


                                          Google Spreadsheet   Post  #1083
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Mar 17, 2013
user Ryan Roga :(http://productforums.google.com/forum/?zx=9bydteme3h15#!category-topic/docs/spreadsheets/jyolpqUi0ZM)
Case of elaborate IF type statement in Google Docs Sheets

Using Google Docs spreadsheet to do the following:
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