# Google Sheets formula to calculate gross monthly income based on net annual income

I'm trying to figure out how to get the monthly gross income based on the annual net income. The net income is computed by multiplying the monthly gross by 12 and then subtracting the annual tax.

The annual tax varies depending on the annual income. There is a fixed amount and a variable amount which is a percentage of the annual income less the lower limit for that range. The table is shown below:

``````  A       B       C           D           E       F
1 Annual Income   Fixed Tax   Excess Tax  Monthly Annual
2 0       5000    0           0           900     10800
3 5000    8000    0           20%
4 8000    16000   600         25%
5 16000   40000   2600        30%
6 40000   160000  9800        32%
7 160000          48200       35%
``````

If a person's monthly gross income is 900, then

``````net income =F2 - (LOOKUP(F2, A2:B7, C2:C7) + LOOKUP(F2, A2:B7, D2:D7) * (F2 - LOOKUP(F2, A2:B7, A2:A7)))
= 10800 - (600 + 0.25 * (10800 - 8000))
= 9500
``````

Is getting the monthly gross income based on the annual net income possible in Google Sheets?

• Divide what by 12? The input is supposed to be the net income. The monthly gross income isn't net income divided by 12. – dork Apr 28 '18 at 14:48
• How? Say I know what my NET INCOME is, how do I get the gross annual income? – dork Apr 28 '18 at 14:53
• Yes, the only input is net income (9500) and it just has A2:D7 as reference to get the monthly gross income (900). – dork Apr 28 '18 at 15:43

``````=IF(LOOKUP(B15, A2:A7)=LOOKUP(((B15+LOOKUP(B15, A2:B7, C2:C7)-LOOKUP(B15, A2:B7, D2:D7)*LOOKUP(B15, A2:B7, A2:A7))/(1-LOOKUP(B15, A2:B7, D2:D7))),A2:A7),((B15+LOOKUP(B15, A2:B7, C2:C7)-LOOKUP(B15, A2:B7, D2:D7)*LOOKUP(B15, A2:B7, A2:A7))/(1-LOOKUP(B15, A2:B7, D2:D7)))/12,((B15+LOOKUP(B15, A2:B7, C3:C8)-LOOKUP(B15, A2:B7, D3:D8)*LOOKUP(B15, A2:B7, A3:A8))/(1-LOOKUP(B15, A2:B7, D3:D8)))/12)
• By the way, why are the lookup arrays for the `value on false` section from `3:8`? Row 8s don't have values. – dork May 1 '18 at 11:11