Google Sheets – Formula to Calculate Gross Monthly Income from Net Annual Income

formulasgoogle sheets

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?

P.S. Not really sure about the title/question. Please edit accordingly.

Best Answer

Disclaimer: All the formulae and explanations below have lot of assumptions and are highly error prone. Solution provided is for educational purposes only. Any risk/damage, financial or otherwise resulting from usage of this solution will be borne by the end user only. Consult your financial advisor.

B15(Net Income): 9500

B17(Monthly Gross Income Probable Value):

=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)
Related Topic