How to calculate a discount formula in Excel with examples

By Indeed Editorial Team

Published 6 April 2022

The Indeed Editorial Team comprises a diverse and talented team of writers, researchers and subject matter experts equipped with Indeed's data and insights to deliver useful tips to help guide your career journey.

Calculating discounts can be easy, especially if you use the right discount formula in an Excel sheet. The two primary ways a business offers a discount is either as a percentage or a specific amount off the original price. If you work at a business that offers discounts to its customers, learning how to use a discount formula in Excel is vital. In this article, we explain the formulas for calculating discounts, how to calculate discounts and the benefits of offering discounts to a company.

Calculating a discount

See below the basic steps for calculating a discount:

  • Convert the percentage to a decimal: You can do this by either moving the decimal point two places to the left or by using a calculator. For example, a 20% discount offered by a fashion house converts to a decimal of 0.20.

  • Multiply the original price by the decimal: For example, multiply the original price of the sweatshirts, which was £150, by 0.20 to calculate the discount: £150 × 0.20 = £30. Therefore, the discount amount on each sweatshirt is £30.

  • Subtract the discount from the original price: To calculate the new selling price for the sweatshirts, subtract the discount amount from the original price. For example, £150 – £30 = £120, therefore, the new price for each sweatshirt after the discount is deducted is £120.

Related: 12 common types of discounts

Formulas for calculating a discount

There are different formulas to calculate a discount. Below are examples and how to apply them:

Example 1

Suppose a shop is offering a 10% discount on all footwear with a selling price of £30. To calculate the discount amount, apply the formula:

discount = listed price x discount rate

To calculate the new selling price of each item of footwear, apply the formula:

selling price = listed price - discount

Therefore, the discount amount and the new selling price for each item of footwear is £3 and £27, respectively.

Example 2

A gadget store is selling mobile phones originally priced at £300 each at a discounted price of £50. To calculate the discount amount on each phone,

apply the formula listed price–selling price = discount amount: £300–£50 = £250. To calculate the discount rate, apply the formula:

discount / listed price × 100% = discount rate

£50 / £300 × 100% = 16.6

Therefore, the discount amount and discount rate for each mobile phone are £250 and 16.6%, respectively.

Read more: How to calculate a discount

The discount formula in Excel

Below are the steps to take if you're calculating a discount rate:

  • In a tabular excel worksheet format, type in the sale prices and the original prices.

  • For example, column A may contain the original price (OP) of £150, £200 and £100. The same goes for the sale price (SP) column.

  • Choose a blank cell. You can find different cells that communicate different information. Cell A2 represents the original price, B2 can stand for the sales price.

  • Change the information in each cell. Press 'Enter' and drag to fill in the right formulas into the right spaces. For example, cell C2 can contain results gotten with the formula = (B2-A2)/ABS (A2).

  • Choose a formula range cell. For example, in C2:C5, right-click, then format cells.

  • From the format cell dialogue, select number > percentage and place the decimal points correctly and click 'Ok'.

  • Alternatively, you can choose the range and click the % icon in the number group at the home tab. This shows the percentage discount result of each item.

Calculating discount price in Excel

Do the following to find the discount prices of data listed in an excel worksheet:

  • Choose a blank cell and input the formula A2 - (B2*A2). In a tabular worksheet, A2 shows the original price, cell B2 represents the discount rate of items.

  • Press the 'enter' button, drag the fill handle to fill in the range.

  • With the formula A2 - (B2*A2), you can calculate the discount sale price.

How to use estimation to calculate a discount

There are different ways to use estimation to calculate discounts:

1. Round the original number

Round the original price of a product to the nearest 10.

Example: Pristine's fashion house had sweatshirts originally priced at £157. Rounding £157 to the nearest 10 would be £160.

2. Calculate 10% of the rounded number

Calculate 10% of the rounded original price by moving the decimal point one place to the left.

Example: If the rounded original price is £160, moving the decimal point to one decimal place to the left would indicate 10%. To do this mathematically: 10% x 160 = £16.

3. Determine the number of '10s' and estimate the discount

This means calculating how many 10s are in a discount. For example, there are two 10s in 20%. Then, calculate the estimated discount by multiplying the number of 10s in the discount by 10%.

Example: The rounded price for sweatshirts is £160, and 10% of £160 = £16. There are two 10s in 20%, and £16 × 2 = 32. Therefore, the estimated discount is £32.

4. Account for 5%

Accounting for 5% is necessary in instances where discounts have 5 in the hundreds place. In cases like these, divide the original dollar amount for 10% by two.

Example: The discount amount for sweatshirts at Pristine fashion house may be 25%. To account for the remaining 5%, divide £16, which was 10% of the original rounded price of £160, by 2: £16 / 2 = £8. Therefore, 5% of £160 is £8.

6. Add 5%

Add 5% of the dollar amount to 20% of £160, which is £32. Then, consider that 5% of £160 is £8 and that £32 + £8 = £40. Therefore, 25% of £160 is £40.

7. Calculate the sale price

To calculate the sale price, subtract the discount from step 6, which is 20%, from the original rounded price of £160. The rounded price for the £157 sweatshirts was £160. Therefore, 25% of £160 is £40, and £160 - £40 = £120. The estimated sale price for the sweatshirt is £120.

Related: Cash discount versus trade discount: key differences

Benefits of offering discounts to a company

The benefits of offering discounts to a company include:

Increase in sales

Discounts attract buyers to a business, which results in a gradual increase in sales and profit. Businesses do not always offer discounts, and that is why many customers look forward to special days like Black Friday and the opportunity to enjoy cheaper shopping. For example, a clothing store may decide to sell footwear at a 20% discount during the holiday season.

Improvement in reputation and business image

Developing brands can boost their business image and reputation by offering their products at discounted prices to less privileged people. For example, as part of the festive season celebration, a food company may decide to sell food products to the elderly or handicapped at discounted prices. Kind gestures like this can boost the reputation of a business. In return, these communities can help market the product and brand to others.

Sales target achievement

One good way of meeting sales targets is through discounted prices. Businesses have their yearly, monthly, weekly and daily targets, and usually, when working beyond expectations, discounting prices on goods can help achieve these sales goals. It's important for business owners to determine discounted prices wisely. Fixing fair and realistic discounted prices can help to ensure a business achieves at least some profit.

New and repeat customer attraction

Most customers love buying products at discounted prices and look forward to those special occasions when brands offer their products on sale. Most online brands prepare and advertise their products at discounted prices through graphics and videos. In the same way, offline brands prepare to showcase their products at discounted prices to their customers. During this period, the discounted prices may attract new customers, and such customers impressed with the prices, products and packaging may become repeat customers.

Related: 10 customer relations examples for success

Increase in-store space

Businesses sometimes offer discounted prices on their goods to sell off old stock and buy new products. To achieve that, they fix attractive prices on products to sell them and free up room in the store. It is also beneficial to discount products that depreciate quickly, to reduce the time they remain in stock. By doing so, you can quickly sell them and replace them with new products.

Saves money

Discounts can help both the business owners and customers save money. Business owners save money by selling off old commodities instead of discarding them. Customers save money by buying the goods at discounted prices instead of the original price. They can also save money depending on the payment method used. Some businesses slash the prices of goods for customers paying with cash since credit cards often result in additional charges.

Great marketing tool

Offering discounts, especially during holiday seasons, can serve as a great marketing tool. Discounted prices draw many customers to new brands. Many people look forward to the holiday season when businesses are more likely to offer their goods at discounted prices. Customers use this opportunity to buy their favourite products and may also purchase for friends. Some customers may even decide to create videos with their newly purchased products, increasing brand awareness and attracting new customers.

Please note that none of the companies mentioned in this article are affiliated with Indeed.

Explore more articles