Calculating Income Tax

[Guest]

I have a simple spreadsheet for calculating salaries of workers of a small
business. I have a long nested IF expression for calculating the income tax
component. It is working but it is too long and not easy to edit. Is there
any way out?
Assumption:
Income Rate
First $150 0%
Next $150 5%
Next $300 10%
Next $2,100 12%
Next $2,700 15%
Exceeding $6,000 30%

Could anyone be of help?
Thank you

Darkwah

 

[Niek Otten]

Hi Darkwah,

Look here:

http://www.mcgimpsey.com/excel/variablerate.html

Or use this UDF. I include instructions for implementing UDFs in case you're new to them.

' =========================================================
Function PercPerSegment(Amount As Double, Table As Range) As Double
' Niek Otten, March 31, 2006

' Progressive pricing
' First argument is the quantity to be priced
' or the amount to be taxed
' Second argument is the Price or Tax% table (vertical)
' Make sure both ends of the table are correct;
' usually you start with zero and the corresponding price or %
' Any value should be found within the limits of the table, so
' if the top slice is infinite, then use
' something like 99999999999999999 as threshold
' and =NA() as corresponding value

Dim StillLeft As Double
Dim AmountThisSlice As Double
Dim SumSoFar As Double
Dim Counter As Long

StillLeft = Amount

For Counter = 1 To Table.Rows.Count - 1
AmountThisSlice = Application.Min(StillLeft, Table(Counter + 1, 1) _
- Table(Counter, 1))
SumSoFar = SumSoFar + AmountThisSlice * Table(Counter, 2)
StillLeft = StillLeft - AmountThisSlice
Next
PercPerSegment = SumSoFar
End Function
' =========================================================


================================================
Pasting a User Defined Function (UDF)
Niek Otten, March 31, 2006

If you find a VBA function on the Internet or somebody mails you one, and you don't know how to implement it, follow these
steps:

Select all the text of the function.
CTRL+C (that is, press and hold down the CTRL key, press C, release both). This a shortcut for Copy.
Go to Excel. Press ALT+F11 (same method: press and hold the ALT key, press the F11 key and release both). You are now in the
Visual Basic Editor (VBE).
From the menu bar, choose Insert>Module. There should now be a blank module sheet in front of you. Click in it and then
press CTRL+V (same method.). This a shortcut for Paste. You should now see the text of the function in the Module.
Press ALT+F11 again to return to your Excel worksheet.
You should now be able to use the function as if it were a built-in function of Excel, like =SUM(..)
================================================



--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I have a simple spreadsheet for calculating salaries of workers of a small
| business. I have a long nested IF expression for calculating the income tax
| component. It is working but it is too long and not easy to edit. Is there
| any way out?
| Assumption:
| Income Rate
| First $150 0%
| Next $150 5%
| Next $300 10%
| Next $2,100 12%
| Next $2,700 15%
| Exceeding $6,000 30%
|
| Could anyone be of help?
| Thank you
|
| Darkwah

 

[Guest]

this would be a good spot for a lookup
either as an array or as a table
=LOOKUP(A1,{0,151,301,601,2701,6001},{0,0.05,0.1,0.12,0.15,0.3})
you might want to use 150.01 etc depending on your actual cutoover point,

 

[Roger Govier]

Hi


I think you have a mistake with your table, as there is a "hole" between
5400 and 6000 where no tax would be paid.
Cumulatively until the end of the 12% band it is 2700, and the 15% band
needs to be 3300 to reach the final band of 6000

You could do it all in one cell with the following formula
=MIN(A1-150,150)*5%+MAX(0,MIN(A1-300,300))*10%+
MAX(0,MIN(A1-600,2100))*12%+MAX(0,MIN(A1-2700,3300))*15%+
MAX(0,A1-6000)*30%

Or if you wanted to see it set out as a table then
in cells E4 to E9 enter 150,300,600,2700,6000, Over 6000
in cells G4 to G9 enter 0%,5%,10%,12%,15%,30%
in cell F4 enter
=MIN(E4,$A$1)-F3
and copy down to cell F9
in cell H4 enter
=F4*G4 and copy down to H9

In F10 enter = SUM(F4:F9)
in H10 = SUM(H4:H9)

In both cases enter salary figure in cell A1

 

[Guest]

Hello Niek,
I am glad it is working perfectly.

Darkwah