How to calculate simple discount

We can set up a Discount Tool in Excel by using arithmetic functions.

Spreadsheet Data 1 shows the initial setup.

Spreadsheet Data 1

We start by removing the VAT from the retail price, in cell C17 we write;

=B17*(1/(1+($C$10/100)))

where B17 is the retail price, including VAT, and C10 is the VAT rate. (Remember, we put dollar signs in the VAT cell reference so that it will not change when we copy the formula down the column.) For the first product, Shoes, which have a retail price of £117.50, this gives a retail price excluding VAT of £100.00, as in Spreadsheet Data 2.

Spreadsheet Data 2

To then calculate the first band of discount, for dealers, we type the following into cell D17;

=C17-(C17*$C$11/100)

For the next band of discount, distributors, we type the following into cell E17;

=C17-(C17*$C$12/100)

And for the third band of discount, Key Account, we type the following into cell F17;

=C17-(C17*$C$13/100)

Copying each of these four formula down their respective columns gives us the complete discount tool as in Spreadsheet Data 3.

Spreadsheet Data 3

The sample discount tool with some extra formatting is available as a free download. Remember to always virus scan any file you download from the internet.

..back to top