Tax Table function

  • Thread starter Thread starter orrbill
  • Start date Start date
O

orrbill

My Problem is similar to a tax table that has a rate for differen
incremental levels of income. I have been told that an array using I
statements may be the solution, if some one has a solution or can tel
me where to look for a solution I would be most grateful.
My objective is to develop a function to calculate the amount for eac
level and then sum the total and divide by the quantity for a over al
% 64 is Input Value
From/To Rate table
0-19 *40%
20- 39 *30%
40- 69 *20%
70- 79 *10%
More than 80 *12%

For Example by hand it would be
From/To
1st Level 0 to 19 =19 *40% = 7.6
2nd Level 20 to 39 =19 *30% = 11.7
3rd Level 64-38 =26 *20% = 1.2
4th Level 0 = 0 *10% = 0
5th Level 0 = 0 *12% = 0
Total value = 64
sum of rates = 20.5
Divide by Value 64
Equals Over all Rate 32%
32%*64 = 20.
 
Hi orrbill!

See:

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

Exactly the approach you need for this type of problem.

(JE! I can't see this indexed on your Excel home page.)

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Hi JE!

Got it!

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Norman Harker & JE McGimpsey,

Thank you for your help, I have been to Je Gimpsey's site and workin
through the details of the sumproduct() function for the marginal rat
tax formula. It is just what I need thanks alot.
Bil
 
One more Question if you folks don't mind.

The Sumproducts() works great and I appreciate your help.

Is there a function to calculate the detail value for each row?

I have tried modifing the Sumproduct() function for each row but hav
been unsucessful,that function does not appear to be the answer.

Again any help would be most appreciated.Below is my table.

1...Q
2
Input value
3...300,000
4
5.........R..........................T
6Threshold......Rate......Diff
7
8......0........ .....40%......40%
9......19999......35%.....-5%
10.....39999.....30%.....-5%
11.....49999.....25%.....-5%
12.....69999.....20%.....-5%
13.....99999.....12%.....-8%

Total...52,999.72

SUMPRODUCT(--($Q$3>$R8:$R$13),($Q$3-8:$R$13),$T$8:$T$13)
The information on JE McGimpsey site has been most helpful thank you!

Thanks in advance,
Bil
 
Hi Bill!

Without your workbook it's difficult but

($Q$3-8:$R$13)

This is the bit that's wrong.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Your reply
Hi Bill!
Click to expand...
Without your workbook it's difficult but

This is the bit that's wrong.
Click to expand...

Hi Norman,

Thanks for pointing out the error. In formatting the reply and the
request for additional help I indvertently dropped the $R$ in th
formula.

It should read.
SUMPRODUCT(--($Q$5>$R8:$R$13),($Q$5-$R$8:$R$13),$T$8:$T$13)

This function works very well in calculating the total tax.

Sorry, my explaination for my second request was not very clear.

The work book I have created is mostly identical to the one J.E
McGimpsey (changed the threshold values and marginal tax rates an
inclued the first band in my calculations) has on his site that yo
were so kind to direct me to yesterday. I also had problems gettin
directly to the variablerate.html but found the link on the excel page


www.mcgimpsey.com

On reading the instructions on J.E. McGimpsey's site for usin
Sumproduct() to calculate variable rates he has a note that says:

"One way to do it would be to use 4 different cells and calculate th
tax owed for each marginal band"

That is the problem I am trying to solve, what I would like to be abl
to do to show the total tax that applies to each individual band.

Thanks, again for all your help.
Bil
 
Hi Bill!

Not too difficult. Are you happy with entries in four separate cells
with perhaps a fifth cell to sum them? In fact it's a lot easier that
calculating the overall liability in one hit.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Norman,

Thank you for the reply.

Yes, I would be very happy with entries in four separate cells with
fifth cell to sum them.

In my mind to be able to calculate the individual levels would make th
overall table calculations easier to understand, especially, whe
showing to others.

Also

the individual level calculations would provide a good trail of th
over all sumproduct() calculation provided by Mr. McGimpsey.

The sumproduct solution gives me a great overall solution and I wil
use it, but I find it difficult to audit/understand all th
calculations that the function is preforming for all the levels a
once.

If you could point me in the right direction I would be mos
appreciative.

Thanks again,
Bil
 
Hi Orrbill!

Send me a copier of your workbook. It will be a lot easier and result
that you get won't be generic. I'll post back and update newsgroup
with a generic solution.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Norman,

Thank you, I have sent a copy of my workbook to your e-mail address.
Not sure if it is allowed to send it through the forum.

Regards,
Bil
 
Hi Bill!

I'll take a look. Never attach files top postings to the newsgroup.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Back
Top