I am trying to make a excel file for calculation of tax


*** age yearly income tax slab

Male <60 <120000 10% of taxable income
Male <60 <250000 20% of taxable income
Male <60 <350000 30% of taxable income
Male >60 original -10 %
FEMale <60 <180000 10% of taxable income
FEMale <60 <300000 20% of taxable income
FEMale <60 <450000 30% of taxable income
FEMale >60 original -10 %

type slab
home loan 100%
childrens fees(<=2) 100%
life insurance(LI) premium 100%
medical insurance(MI) premium 50%

Home Loan EMI 15000(for eg)

Now consider a case

*** age monthly income tax aplicable LI premium MI premium
(quarterly) (quarterly)
(1) Male 48 25000(for eg) 60000 2000 Nil
(2) female 44 30000(for eg) 72000 2500 1000
(3) Male 28 10000(for eg) 12000 1000 Nil
(4) Male 16 Nil(-4200 fees)(for eg) Nil Nil Nil
(5) FEMale 13 Nil(-4200 fees)(for eg) Nil Nil Nil

Now as per this If Home loan premium is considered Tax for Male(48) will be completely deducated

But it applies to Female but that can also be avoided if we consider the 1800 fees paid for both the children
ie 4200*2*12-100800(for fees)+10000(LI premium)+2000(MI premium = 112800
which puts female in slab of <250000 which saves 22000 in tax
But that holds only till the male do not get retired or home loan does not over.
Like that there are many combinations.
I am trying to make a formula for this where the figures of tax slab , ***, age, income, premias, fees, home loan is submmitted and on basis of that
calculation in excel file, apprpriate mix-match is done and the tax applicable to the family is calculated.
Please help me to make the appropriate formula or way to calculate this.

Zohar Batterywala