# How to calculate simple discount

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

## Purchase the Simple Discount Spreadsheet Example

Click the button below to purchase the Simple Discount Spreadsheet Example

Spreadsheet Data 1 shows the initial setup. 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. 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. 