Making an Income Tax Calculator

**Pstar** · 08-18-2010, 01:21 AM

Hi all,

New to this forum and an utter newbie to excel!

I would like to make an Income Tax Calculator using with the sheet attached.

For example, a person making $20,000 a year would pay a total of $475 in taxes for that year

E.g. $175 for the first $10,000 and the next $10,000 would be charged at 3%.

I was wondering how I could do this where I can input the value in say cell A1, and it will spit out the tax chargeable on that income in say cell A2 in Excel 2007.

Many thanks

**DonkeyOte** · 08-18-2010, 01:48 AM

It's not clear to me what happens say if the salary is $13,000 ?

Presumably the $175 is a fixed amount for any salary between $10,001 and $20,000 else you would pay less tax earning $13,000 than you would if you earned $10,000

**DonkeyOte** · 08-18-2010, 01:56 AM

In retrospect - having now understood that the fixed amount is merely the aggregate of the prior band

=SUMPRODUCT(--(A1>{0,2500,5000,10000,20000,35000,50000,70000,100000,150000,250000}),A1-{0,2500,5000,10000,20000,35000,50000,70000,100000,150000,250000},{0,0.01,0.02,0,0.04,0.05,0.07,0.05,0.02,0,0})

where A1 holds the salary of interest.

(replace the inline arrays with range references if preferred but note the values that should be in the ranges - ie the low values of each band and the incremental % between the bands)