Sales Tax Calculation

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

LMB - 05 Jul 2006 03:29 GMT

Hi,

What function or calculation could I use in a spreadsheet to calculate the
price of an item minus the local sales tax so that in the end the final
price would be a whole number. I would like to enter the local tax rate in
percent so I could figure out what the price for an item should be so in the
end the final price is $5, $10, $15 etc. Using Excel 2000.

Thanks,
Linda

Reply to this Message

Roy Harrill - 05 Jul 2006 03:49 GMT

If the tax rate is in cell B1 and the final (total) price is in B2, use this
formula in cell B3 (or any other cell):
=ROUND(B2/(1+B1),2)
Roy

> Hi,
>
[quoted text clipped - 6 lines]
> Thanks,
> Linda

Reply to this Message

LMB - 08 Jul 2006 23:07 GMT

I tried this formula but I'm not getting what I need. I need to enter the
tax rate and the final price and my calculation will give me the Item Price.

Tax Rate 7.25
Total Price 15.00
Item Price $1.82 (Calculated Number based on Tax Rate and Total Price)

Thanks,
Linda

> If the tax rate is in cell B1 and the final (total) price is in B2, use
> this
[quoted text clipped - 12 lines]
>> Thanks,
>> Linda

Reply to this Message

Gord Dibben - 08 Jul 2006 23:19 GMT

LMB

Try this formula.........=ROUND(B2/(1+B1),1) returns 14.00

Note that tax rate in B1 is .0725 not 7.25

Gord Dibben MS Excel MVP

>I tried this formula but I'm not getting what I need. I need to enter the
>tax rate and the final price and my calculation will give me the Item Price.
[quoted text clipped - 22 lines]
>>> Thanks,
>>> Linda

Reply to this Message

LMB - 09 Jul 2006 01:36 GMT

Thanks Gord,

Ok..that seems better but if I have an item the is a total price of $20.00
with sales tax .0725 <g>, my Item Price calculates out to $18.60. If I plug
in 18.60 in a sales tax calculator, I get a total price of 19.95. Is the
Round part causing this?

Thanks,
Linda

> LMB
>
[quoted text clipped - 33 lines]
>>>> Thanks,
>>>> Linda

Reply to this Message

Gord Dibben - 09 Jul 2006 02:02 GMT

Yes.

The rounding changes the actual value.

If you use this formula =(B2/(1+B1))

B1 = .0725

B2 = 20.00

Formula returns 18.65

.0725 * 18.65 = 20.00

Gord

>Thanks Gord,
>
[quoted text clipped - 43 lines]
>>>>> Thanks,
>>>>> Linda

Gord Dibben MS Excel MVP

Reply to this Message

LMB - 09 Jul 2006 02:25 GMT

Thanks...I could have just tried it...geesh, I got myself all
confused....not hard to do.

Linda

> Yes.
>
[quoted text clipped - 66 lines]
>
> Gord Dibben MS Excel MVP

Reply to this Message

Bondi - 05 Jul 2006 10:24 GMT

> Hi,
>
[quoted text clipped - 6 lines]
> Thanks,
> Linda

Hi Linda,

Another way to do it would be to format the cell where you enter your
local tax as Percentage (Right Click the cell and chose Format Cells ->
Number and Chose Percentage on the right side). If we say that A1 is
the tax cell and B1 is the price including tax then i think that this
formula will give you the price net tax as a whole number:

=ROUND(B1-(B1*A1),0)

If you want the final price shown as currency then Right Click the cell
and chose Format Cells -> Number and Chose Currency on the right side
and chose $ as symbol.

Regards,

Bondi

Reply to this Message

LMB - 08 Jul 2006 23:18 GMT

>> Hi,
>>
[quoted text clipped - 27 lines]
>
> Bondi

I tried this formula as well as the other one suggested but I'm still not
getting what I need. I need to enter the tax rate and the final price and
my calculation will give me the Item Price. I tried you suggestion and got
this. I am not the best at math but I think Tax on $14.00 is 1.015 so the
total price would be $15.02. I need the final price to be $15.00 even.

Tax Rate Total Price Item Price
7.25% $15.00 $14.00

Thanks

Reply to this Message

LMB - 06 Jul 2006 14:06 GMT

Thanks Roy and Bondi. I am heading out on a trip and will try these
suggestions this weekend.

Linda

> Hi,
>
[quoted text clipped - 6 lines]
> Thanks,
> Linda

Reply to this Message

Rate this thread: