How To Calculate A Discount Formula In Excel With Examples

Do you know discounts can boost your sales by many times? 83% of sellers agree that a simple discount creates a great impact on the shopping habits of the customers. Thus, it not only increases your sales rate but also increases business engagement.

However, in big business, calculating discounts becomes difficult as there are hundreds and thousands of product variants. And the discounting amount or rate is not the same for all the items.

In this case, calculating discounts in Excel can save you time and effort! All you need to do is put the prices and apply a discount formula; Excel will calculate the price for you. Thus, you can get discounted prices for hundreds of products with a single click.

But you must know the correct formula to get an accurate result. A wrong calculation can bring you a loss in business, which is never expected.

Therefore, we have brought you a complete guide on how to calculate a discount formula in Excel with examples. Here, you will get several discount formulas based on variant discounting cases that you will need for your business. So, let’s get started-

What Is Discount Formula?

Retail stores, services, or businesses often give discounts to boost sales. This refers to a price deduction system where the customers can purchase a product at a lower value than the original price. The formula used to find this discounted product price is termed the discount formula.

Basic Formula For Calculating Discount

To clear up the discount formula, you must be familiar with three terms: discount price, discounted price, and discount rate. The basic formula of these terms is as follows:

1. Discount Price

A discount is the amount of money you will save to reduce the price of an item. You will get the discount price of a product by subtracting its listed or original price from the selling price.

Discount Price = Original Price – Selling Price

For example, if the original or listed price of the product is $10, and the discounted or selling price is $8, the discount is $10-$8 = $2.

2. Discounted Price

The discounted price is the final selling price after offering a discount. You will get the discounted price of a product by subtracting the discount price from the listed or original price.

Discounted Price = Original Price – Discount Price

For example, if the original price of a product is $50 and you get a discount of $10, the discounted price is $50-$10 =$40.

3. Discount Rate

The discount rate is expressed in percentage. It represents the portion of the original price that has been discounted. The formula for the discount rate is as follows:

Discount Rate = (Original price – Discounted price) * 100%

For example, if the original price of an item is $100 and the discounted price is $80, the discount rate is ($100-$80)*100%= 20%.

Discount Formula Calculation In Excel

When running a store, manual discount calculating doesn’t seem professional. It is, in fact, time-consuming and has chances of error. So, if you offer discounts in your business, maintaining an Excel spreadsheet to track the discounts is necessary. Below, we have added the formula for calculating discounts, discounted prices, and discount rates in Excel with examples; check them out:

Formula For Discount Price

• Open the Excel spreadsheet and input the product’s original price in A2.
• Input the selling price of the product after the discount in B2.
• Click on the C2 cell, press ‘=’, and type the formula: A2-B2; you will get the discount product.

Formula:

Discount Price = Original Price – Selling Price C2 = A2-B2

Example:

Suppose you sold a product at $50 after a discount. The usual price of the product is $80. So, how much was the discount on that product?

Solution:

Formula For Discounted Price

Case#1: When You Know The Original Price & Discount Price

• Go to the Excel sheet and input the original price in A2 and the discount price in B2.
• Click on the C2 cell and press ‘=’ to input the following formula: A2-B2.

Using this simple formula, you can find the discounted price if the discount price is known.

Formula:

Discounted Price = Original Price – Discount Price C2 = A2-B2

Example:

Suppose you bought a shirt at a discount of $20. The original price of the shirt was $100. So, what is the buying or discounted price of the shirt?

Solution:

Case#2: When You Know The Original Price & Discount Rate

• Open a blank Excel file, including the original product price in A2 and the discount rate in B2.
• Press ‘=’ on C2 and paste the formula: A2 – (A2*B2)
• Select the C2 cell and drag it down to input the formula for the cells below.

With this formula, you can find the discounted price of any product by inputting the original price and the discounted rate.

Formula:

Discounted Price = Original Price – (Original Price * Discount Rate) C2 = A2 – (A2*B2)

Example:

Suppose the original price of three items is- $10,000, $20,000, and $30,000. Their discount rates are 5%, 10%, and 15%. So, what are the discounted prices of the three products?

Solution:

Formula For Discount Rate

Case#1: Using Discounted Price

• Input the original price of the product in A2 and the targeted discounted price in cell B2.
• Press ‘=’ on cell C2 and type the formula: (A2-B2)/A2
• You will get the result in decimal. Simply multiply the result by 100 to convert it into percentages. However, you can simply modify the above formula to avoid manual conversion. In this case the formula would be: {(A2-B2)/A2}*100

Formula:

Discount Rate = (Original Price – Discounted Price) / Original Price C2=(A2-B2)/A2 Or C2= {(A2-B2)/A2}*100 (to get results in percentage)

Example:

Suppose you purchased a $1000 product at a discounted price of $500. So, what was the discount rate?

Solution:

Case#2: Using the What-If Analysis Function

What-If Analysis is an effective function of Excel that helps you fix discounts for products. You can implement different discount strategies using this function. Here is how to use it:

• Enter the product’s original price in cell A2 and the current discount rate in B2.
• Insert the formula of case-1, A2-(A2*B2) in cell C2
• Now, click on the C2 cell and go to the Excel ribbon. Next, navigate to the Data tab and click “What-If Analysis.”
• Choose “Goal Seek” from the drop-down menu. Enter the desired discounted price in the “To value” field. Click “OK,” and Excel will give you the discount rate.

Example:

Let’s say the original price of a product is $100, and the current discounted price is $50. But you want to sell this at a discounted price of $75. So, what should the discount rate be?

Solution:

• Go to the “Goal Seek” option following the above procedure. There, you will find “Set cell” is auto-filled with C2.
• Now, fill in the desired discounted price, which is $75, in the “To value” field.

• Click into the “By changing cell” field, then click on cell B2 to select that value and click “OK.” the Excel will show you the discounted rate as 25%.

Why Do Businesses Offer Discounts?- Benefits

Why does a business reduce its product price and lower its profit rate? Surely, there are hidden benefits to it. In fact, discounts don’t lower your profit; in fact, it increases the margin. How? Check the below reasons for why offering discounts is beneficial for business:

Boosting Sales: The primary purpose of offering discounts is to increase sales. It is very natural that customers love to purchase products at low rates.

So, when you reduce the price, it increases sales, and new customers are added to your store.

Besides, customers tend to purchase more products to keep in stock when they get discounts. However, discount prices have not been offered throughout the years.

Clearing Off Old Collections & Making Space In Store: Keeping any items for a long time makes them outdated, or some expire. So, to get rid of these losses, businesses offer discounts to clear their stocks. Inventory or storage is another factor here.

When you add new products to your store, you need additional space for them. This creates an urgency to get rid of the old item. In such circumstances, shop owners sell their products at a discounted price.

For example: at the beginning of summer you will get a discount on winter products. This is because the business will soon launch its summer collection, and so it will sell its winter products at discounted prices.

Marketing Tool: Discounts are a great marketing tool to promote your business. This is a great strategy to attract new customers. You will see big banners and posters in front of the showrooms as ‘50% Off.’

Such deals grab passersby’s attention and encourage them to shop for them. Besides, in the e-commerce business, you can reach your target audience faster by offering discounts.

For instance, you can run ads for your discounted products that will reach the social media accounts of the targeted audience. This way, you can make discounts a crucial part of your marketing strategy to boost sales.

Brand Identity & Increasing Engagement: Brands that offer frequent discounts are more likely to be remembered by the customers.

They wait for the discounts offered all year to get their desired product on sale. This is why you will see crowds in stores during the festive season.

This is the time when most businesses offer discounts. It helps them to build brand identity, increase engagement, and keep them in the shade of their competitors.

Achieving Sales Target: Every business has a weekly, monthly, and yearly sales target. This is how they track the loss and profit of their business.

When they fail to reach the target, they offer discounts on the products. Thus, more products are sold, helping them to meet their sales target.

However, it is essential to determine the discount price wisely. You shouldn’t reduce your price too much, that brings you a loss.

Serving Loyal Customers: Loyal members are those customers who frequently purchase from your store. To hold them, you must offer them a special discount so they can keep purchasing.

You can offer referral discounts, festive discounts, social media sharing discounts, and more. Such loyal customers play a great role in uplifting your business.

Plugin To Automate Discount Calculation For Your Stores

When you run discounts on online stores, calculating on Excel sheets and inserting them into the store is a hassle. To make this task easier, WordPress user can get plugin assistance.

You will get hundreds of plugin that offer discount features; you can integrate these plugin in your store and automate the discount calculation.

These plugin further track the profits and help you manage the discounts. The most popular discount plugin are as follows-

The Bottom Line

To calculate a discount formula in Excel, you must know the discount formula. When offering discounts to your customer, the marketing goes as ‘30% off’ or ‘50% discount.’

This percentage indicates the discount rate. Once you estimate how much of a discount you will offer to the customer, Excel will help you find the discounted price of the product for your input values. The primary benefit of using Excel to calculate discounts is formula setup.

Once you set a formula, Excel will save it and give you the result for any value you put. Thus, you can calculate the discount for hundreds and thousands of products in minutes.

These plugin can automate the discount calculation and make your task easier. These plugin offer you a wide range of discounting categories, including- percentage discounts, flat discounts, custom discounts, and more.

Frequently Asked Questions