I have created a small spreadsheet to derive the Gross Pre-tax Income when all that is known is the Net Income, and the Tax Brackets and Rates.
In the example that follows, when the known Net Income is entered in D18, E18 displays the correct Gross Income.
The Tax Brackets and Rates are not static, but are in turn drawn from a set of 13 different Provincial rate groups using an INDIRECT function.
This was the best solution I could think of and it is accurate, but if possible, I'd like to condense this a bit more if there is a simple way to do it that I have overlooked and am very receptive to any ideas that may be obvious to others with more experience.
Thank you for your consideration
In the example that follows, when the known Net Income is entered in D18, E18 displays the correct Gross Income.
The Tax Brackets and Rates are not static, but are in turn drawn from a set of 13 different Provincial rate groups using an INDIRECT function.
This was the best solution I could think of and it is accurate, but if possible, I'd like to condense this a bit more if there is a simple way to do it that I have overlooked and am very receptive to any ideas that may be obvious to others with more experience.
Thank you for your consideration
| Excel Workbook | ||||||||
|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | |||
| 1 | Threshold | Rate | * | Cummulative Tax | Cummulative Net | Gross Sums | ||
| 2 | * | * | * | * | $0.00 | FALSE | ||
| 3 | $0 | 0.00% | * | $0.00 | $10,320.00 | FALSE | ||
| 4 | $10,320 | 15.00% | * | $1,011.15 | $16,049.85 | FALSE | ||
| 5 | $17,061 | 20.60% | * | $1,057.29 | $16,227.71 | FALSE | ||
| 6 | $17,285 | 23.26% | * | $3,884.78 | $25,556.22 | FALSE | ||
| 7 | $29,441 | 20.06% | * | $5,143.54 | $30,572.46 | FALSE | ||
| 8 | $35,716 | 22.70% | * | $6,280.81 | $34,445.19 | $56,230.71 | ||
| 9 | $40,726 | 29.70% | * | $15,400.79 | $56,032.21 | FALSE | ||
| 10 | $71,433 | 32.50% | * | $18,656.97 | $62,795.03 | FALSE | ||
| 11 | $81,452 | 36.50% | * | $18,862.10 | $63,151.90 | FALSE | ||
| 12 | $82,014 | 38.29% | * | $25,591.18 | $73,996.82 | FALSE | ||
| 13 | $99,588 | 40.70% | * | $36,448.32 | $89,815.68 | FALSE | ||
| 14 | $126,264 | 43.70% | * | $2,166,270.95 | $2,833,729.05 | FALSE | ||
| 15 | $5,000,000 | * | * | * | * | * | ||
| 16 | * | * | * | * | * | * | ||
| 17 | * | * | * | Net (Entered): | Gross (calculated): | * | ||
| 18 | * | * | * | $45,345.00 | $56,230.71 | * | ||
,
FF.
Sheet1 | ||||||||
