Calculate annual Gross income from Fortnightly Net Income

[james.mckenzie]

Hi,

I'm trying to calculate a persons Annual Gross Income, based on their fortnightly Net Income.

I have constructed a worksheet to calculate the Tax and Net Income based on the gross, but I can't work out how to go the other way...

My worksheet is set up with the following table:

Column E | F | G | H | I
Tax Bracket | Income | Tax Rate | tax already paid up to | tax paid
R5 | 1 | 1E+18 | 0.47 | 70000 | 18612
O6 | 2 | 70000 | 0.42 | 58000 | 13572
W7 | 3 | 58000 | 0.3 | 21600 | 2652
8 | 4 | 21600 | 0.17 | 6000 | 0
9 | 5 | 6000 | 0 | 0 | 0

Then I calculate the Tax from the following formula:

=(C8-(VLOOKUP(MATCH(C8,F5:F9,-1),E5:I10,4,FALSE)))*((VLOOKUP(MATCH(C8,F5:F9,-1),E5:I10,3,FALSE)))+((VLOOKUP(MATCH(C8,F5:F9,-1),E5:I10,5,FALSE)))

Where C8 is the Gross annual income.

Please note the tax rates are for Australia...

Thanks for your help.

James.

 

[Peter_SSs]

Calculate gross annual income from net income

James

Try this:

In the range A1:F6 put these entries:

Index|Tax Paid|Marginal Rate|Bracket Step|Net Income|Index
1|0|0|0|0|1
2|0|0.17|6000|6000|2
3|2652|0.3|21600|18948|3
4|13572|0.42|58000|44428|4
5|18612|0.47|70000|51388|5

In the range B8:D8 put:
Net Income|Index|Gross Income

In cell B9 put the annual net income (26 * fortnightly net income)

In C9 put the formula:
=VLOOKUP(B9,E3:F6,2)

In D9 put the formula:
=VLOOKUP(C9,A2:E6,4)+(B9-VLOOKUP(C9,A2:E6,5))/(1-VLOOKUP(C9,A2:E6,3))

Hope this helps. I have assumed that you are using 2005 tax rates and ignoring Medicare levy.


Good luck
Peter

 

[james.mckenzie]

That works great.

Thanks Peter