Excel formula for adding VAT and commission

[in2play]

I'm a bit stuck with Excel so any help will be appreciated. I'm working on some product pricing sheets. I have the price I want to make and I want to add to this the VAT and the percentage commission of the sales channel on top.

I thought it would be as easy as - price * VAT * commission (A1*1.175*1.0875) or A1*1.2625. Neither of these methods seem to add enough as when I check the total cost and work out the inclusive VAT and inclusive fees, they are quite a bit out.

Any ideas of a formula that works?

 

[Leo-InstallingIT]

That formula should work. Do you have an example with the values and what you think it should be?

Many Thanks

Leo

 

[wykthorr]

if the commission is added on the price excluding VAT you should:
(price*1.175)+(price*commission) - commission in 1.percent format

if you want the commission to be calculated on the price including VAT you should use:
price*1.175*commission. This is though different from price*(VAT+commission) because the commission is applied to the price with included VAT.

Hope I'm making sense. Give me a PM with your e-mail address and I'll prepare an excel worksheet for you and send it over.

 

[in2play]

I'm trying to get commission taken from the VAT inclusive price. So if I take a figure of £10 and add the VAT and commission on (price*1.175*1.0875), this gives me an inclusive total of £12.778125 (£2.778125 added on).

The problem is, if I then go back to a VAT inclusive calculator, it tells me that the VAT inclusive to £12.778125 rounds to £1.90, while the commission rounds to £1.03. So the rounded total of the commission and VAT I'd have to pay on a price of £12.778125 comes out as £2.93 - but I've only added £2.778125 on to cover this outgoing.

This is the bit I'm getting stuck on.

 

[wykthorr]

I think the easier solution would be if you would tell me what you want to get accomplished and then I can build a simple excel worksheet for you. Otherwise I'm getting a bit confused with all those numbers

 

[Mike George]

The net price is £10.00
The commission before VAT is £0.875
The VAT on £10.00 is 1.75
The VAT on £0.875 is £0.153
The total price including VAT is £11.75
The total commission including VAT is 1.028
The total including commission but before VAT is £10.875
The total including VAT on the product and VAT on the commission is
£12.778
The VAT element of a gross figure of £12.778 is £10.875

Simples

 

[sanjiv]

If you want to do price excluding VAT and have commision and add VAT to that, you would do:
=(A1*comm)*1.175
If you want price incuding VAT + commision, you would do:
=(A1*1.175)*comm

I am assuming that cell A1 has the price ex VAT.

 

[in2play]

Lol, so I've been getting confused by the 'total commission including VAT' bit.

The net price is £10.00
The commission before VAT is £0.875
The VAT on £10.00 is 1.75
The VAT on £0.875 is £0.153
The total price including VAT is £11.75
The total commission including VAT is 1.028
The total including commission but before VAT is £10.875
The total including VAT on the product and VAT on the commission is
£12.778
The VAT element of a gross figure of £12.778 is £10.875

Simples

 

[in2play]

If you want price incuding VAT + commision, you would do:
=(A1*1.175)*comm

That's it, works perfectly

 

[sanjiv]

Brilliant
Glad I could have helped.