0

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.

  • 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
0
+50

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)
| improve this answer | |
  • it's way off. The output is 791.67. (It's supposed to be 900.) – dork May 1 '18 at 7:06
  • Oops, my bad. Was checking with extra deductions. Thanks! – dork May 1 '18 at 9:55
  • Still trying to figure out how to include deductions though – dork May 1 '18 at 10:41
  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.