Complex Sales Tax

[Robert]

I have created a spreadsheet for aiding me in an automobile purchase.
It computes the invoice, fees, taxes, and monthly payments. Generally,
sales taxes are straight-forward, yet sales tax in my county remains
complex. My County charges 7% on first $5,000 and 6% on the rest. My
spreadsheet has a cell with the sales tax rate, but the sales tax
calculation formula assumes a constant rate. I need some advice on the
best approach on calculating the sales tax and the related formula.
Should I use the MOD function or compute a fixed rate? Please explain
the formula since I am fuzzy on it.

 

[Tim C]

One way:

=A1*0.06+MAX(A1*0.01,50)

Tim C

 

[Dave R.]

Tim..that doesn't seem to work, as it computes a tax of $50 on an item with
a price of 0.

On the good side, maybe Robert will either have the salesman compute it for
him, or ask his instructor for assistance with this series of "real world"
problems.

 

[Tim C]

Oops.

=A1*0.06+MIN(A1*0.01,50)

Tim C

Tim..that doesn't seem to work, as it computes a tax of $50 on an item
with
a price of 0.

On the good side, maybe Robert will either have the salesman compute it
for
him, or ask his instructor for assistance with this series of "real world"
problems.

 

[NewsMan]

I have created a spreadsheet for aiding me in an automobile purchase.
It computes the invoice, fees, taxes, and monthly payments. Generally,
sales taxes are straight-forward, yet sales tax in my county remains
complex. My County charges 7% on first $5,000 and 6% on the rest. My
spreadsheet has a cell with the sales tax rate, but the sales tax
calculation formula assumes a constant rate. I need some advice on the
best approach on calculating the sales tax and the related formula.
Should I use the MOD function or compute a fixed rate? Please explain
the formula since I am fuzzy on it.

The easy way:

Assume Cell A1 = cost of car

sales tax: = (5000*.07)+((A1-5000)*.06)

 

[Robert]

On the good side, maybe Robert will either have the salesman compute it for

him

When I bought my last car, the dealer over-charged for the sales tax
for additional profit. The dealer presented me with a formally
spreadsheet showing me the fees and taxes. The dealer could not get me
on the financial part since I purchase the vehicle for cash. Luckily,
I caught the inconsistency and other ones as well. So now I learned
that one must calculate everything yourself.

ask his instructor for assistance with this series of "real world"
problems.

No instructor! I graduate from the University ten years in Finance;
yet forgot most of the specifics formulas. Besides I learned it all on
Lotus 123 back in the day. I still retain the theories though. My
career has taken a more creative path. Ask me about color, lighting,
or force perspective photography; then I am your man.

 

[Robert]

The easy way:

Assume Cell A1 = cost of car

sales tax: = (5000*.07)+((A1-5000)*.06)

Thanks Newsman,

I figured this approach would be the easiest.

(5000*.07)+((Total-5000)*.06)

But I had a feeling that other approaches existed. Tim C demonstrated
another method. Recalling the university day, another method existed
for computing the effective rate; yet I do not have notion of the
formula or how to accomplish it in Excel. This approach would allow me
to place a cell with the sales tax rate. I just thought about putting
it out to the newsgroup. But I may just go with the simple route.

 

[Robert]

Oops.

=A1*0.06+MIN(A1*0.01,50)

Tim C

Thanks Tim,

The formula works without a doubt, yet the internal mathematics have
me perplexed. According to Excel's Help files, the MIN Returns the
minimum value in a list of arguments. The optional argument, 50,
returns 50. Please clarify your approach.

 

[NewsMan]

Thanks Tim,

The formula works without a doubt, yet the internal mathematics have
me perplexed. According to Excel's Help files, the MIN Returns the
minimum value in a list of arguments. The optional argument, 50,
returns 50. Please clarify your approach.

The MIN portion of the formula returns the smallest of the numbers
between A1*.01 and 50 (which is 5000 *.01, in this way you are only
calculating 7% tax up to $5000).