How to Calculate Discount Rate in Excel (3 Quick Methods)

Calculating the time value of money is a very common task in economics and business. One of the most crucial components in determining these is the discount rate. This article will guide you to calculate the discount rate in Excel with some easy and quick methods.


Download Practice Workbook

Download the practice workbook from here.


What Is Discount Rate?

The factor used to return future cash flows to the present day is referred to as a ‘discount rate’. It also refers to the interest rate charged to commercial banks and other financial organizations for short-term loans they obtain from the Federal Reserve Bank.


3 Quick Methods to Calculate Discount Rate in Excel

Here, we are going to learn 3 simple and quick methods to calculate the discount rate in Excel. We have used some excellent examples with explanations for this purpose. So, without further delay, let’s get started.


1. Calculate Discount Rate for Non Compounding Interest in Excel

This method will show you 3 ways to calculate the discount rate for non compounding interest. Here, non compounding or simple interest is computed using a loan or deposit’s principle as the base. Contrarily, compounding interest is based on both the principal amount and the interest that is accrued on it each period. Let’s see the procedures below.


1.1 Apply Simple Formula

In this method, we are going to apply a simple formula for calculating the discount rate for non compounding interest. Here, you can determine the percentage discount if you know the original price and the discounted price. Assuming, we have a dataset (C4:D5) in Excel that contains the Original Price and the Discounted Price of a product. Now, we need to calculate the Percentage Discount. The steps to do so are below.

Calculate Discount Rate for Non Compounding Interest in Excel

Steps:

  • To calculate the Percentage Discount, first, you need to divide the Discounted Price by the Original Price.
  • In our case, we typed the formula below in cell D5 to do so:
=C5/B5

Calculate Discount Rate for Non Compounding Interest in Excel

In this formula, cells C5 and B5 indicate the Discounted Price and the Original Price respectively.

  • After pressing Enter, we will get the output.
  • Secondly, we need to subtract the output from 1. For this, type the formula in cell D5:
=1-(C5/B5)

Calculate Discount Rate for Non Compounding Interest in Excel

  • Therefore, press the Enter key to get the result.

Calculate Discount Rate for Non Compounding Interest in Excel

  • At this time, we need to change the result in percentage For this, select the cell (D5) > go to the Home tab > go to the Number group > select Percent Style (%) symbol.

  • In this way, we will find the desired Percentage Discount.

Note: This Percentage Discount indicates that you are not paying 20% if you are still making a payment of 80%.

1.2 Use ABS Formula

Suppose we have a dataset (B4:D8) in Excel that contains some Original Price and Sales Price of a Product. Therefore, we need to calculate the Discount Rate. Here, we are going to use the ABS function in Excel to do so. The steps are below.

Calculate Discount Rate for Non Compounding Interest in Excel

Steps:

  • In the beginning, to calculate  the Discount Rate, we need to select the first blank cell (D5) of the Discount Rate column and type the formula:
=(C5-B5)/ABS(B5)

Calculate Discount Rate for Non Compounding Interest in Excel

Here, cells C5 and B5 denote the Sales Price and the Original Price respectively.

  • Now, press Enter and get the result.

Calculate Discount Rate for Non Compounding Interest in Excel

  • At this moment, to fill this formula into the desired range, drag the fill handle.

Calculate Discount Rate for Non Compounding Interest in Excel

  • Thus, you will get all the Discount Rate values.

  • If you want the Discount Rate values in percentage form, you need to follow the steps the same as the previous method:

Select the range of cells (D5:D8) > Home tab > Number group > % symbol.

  • In the end, we can see the final result just like the screenshot below.


1.3 Insert Mathematical Formula

Let’s say, we have a dataset (B4:D7) in Excel that contains the Future Cash Flow, Present Value and Number of Years. Here, we need to find out the value of the Discount Rate. In this method, we will use a mathematical formula to do this. The steps are below.

Calculate Discount Rate for Non Compounding Interest in Excel

Steps:

  • To calculate the Discount Rate, in the first place, select the blank cell below the heading Discount Rate and type the formula:
=((C5/C6)^(1/C7))-1

Calculate Discount Rate for Non Compounding Interest in Excel

In the formula, cells C5, C6 and C7 refer to Future Cash Flow, Present Value and Number of Years successively.

  • After pressing the Enter button, you will get the value of the Discount Rate.
  • To get the Discount Rate in percentage format follow the steps:

Select the cell of the Discount Rate value > Home tab > Number group > % symbol.

  • We can see the final output in the screenshot below.

Read More: How to Use Dividend Discount Model Formula in Excel


2. Determine Discount Rate in Excel for Compounding Interest

Let’s look at another illustration to show how compounding affects the discount rate. Assuming, we have a dataset (B4:D8) in Excel that contains the values of Future Cash Flow, Present Value, Number of Years and Number of Compounding Per Year. Here, we need to calculate the Discount Rate. The steps to do so are below.

Determine Discount Rate in Excel for Compounding Interest

Steps:

  • To determine the Discount Rate, firstly, select the cell where you want to keep the value of the Discount Rate and type the formula:
=C8*((C5/C6)^(1/(C8*C7))-1)

Determine Discount Rate in Excel for Compounding Interest

In this formula, cells C5, C6, C7 and C8 indicate the Future Cash Flow, Present Value, Number of Years and Number of Compounding Per Year respectively.

  • After that, press the Enter button to find the result.

Determine Discount Rate in Excel for Compounding Interest

  • Now, to get the result in percentage format, follow the steps just like the previous methods:

Select the cell containing the Discount Rate value > Home tab > Number group > % symbol.

  • Lastly, we will see the final result the same as the screenshot below.

Read More: How to Calculate 10 Percent Discount in Excel (With Easy Steps)


3. Calculate Discount Rate for NPV in Excel

The value of all future cash flows, both positive and negative, discounted to the present, is known as net present value (NPV). In this method, we are going to learn 2 ways to calculate the Discount Rate for NPV.


3.1 Use Excel What-If-Analysis Feature

To determine the Discount Rate for NPV, we may also use the Excel What-If-Analysis feature. This approach involves setting an NPV and letting Excel determine the Discount Rate. Assuming, we have a dataset (B4:C9) in Excel containing the values of Future Value, NPV and Number of Years. Now, to calculate the Discount Rate using the What-If-Analysis feature in Excel, follow the steps below.

Calculate Discount Rate for NPV in Excel

Steps:

  • To input the NPV, first, select cell C6 and type the formula below:
=C5/(1+C9)^C7

Calculate Discount Rate for NPV in Excel

  • Then, press Enter.

Calculate Discount Rate for NPV in Excel

  • Due to the absence of an interest rate, Excel computed $9,000 as NPV. You can disregard this number because we will determine our own NPV and Discount Rate.
  • Next, select cell C9 > go to the Data tab > Forecast > What-If Analysis dropdown menu > Goal Seek.

Calculate Discount Rate for NPV in Excel

  • In turn, a Goal Seek window will pop up.
  • Therefore, we will set C6 to 7000 based on the NPV of $7000 by modifying the Discount Rate C9. Accordingly, Excel will compute the required Discount Rate to achieve an NPV of $7000.
  • After that, click OK.

Calculate Discount Rate for NPV in Excel

  • At this moment, another window named Goal Seek Status will appear.
  • Again, click the OK button.

  • In this way, we will get the desired Discount Rate.

  • Besides, to get the result in percentage format:

Select cell C9 > Home tab > Number group > % symbol.

  • Finally, we will see the final result just like the picture below.


3.2 Apply Excel RATE Function

You can also compute the discount rate using the RATE function in Excel. However, this strategy is beneficial when dealing with a sequence of cash flows.

Let’s say you got a loan from a bank today for $30,000. As a result, you must repay the loans. The terms are that you must pay $12000 per year for the following 5 years. In these cases, you can use the RATE function to calculate the Discount Rate.

Calculate Discount Rate for NPV in Excel

Steps:

  • To calculate the Discount Rate, firstly, select the blank cell and type the formula:
=RATE(C6,-C5,C7)

Note:

  • The first argument, nper, states that there will be 5 installments.
  • The following one is pmt, which displays the cash flow for every installment. A minus sign () is there before C5 as you can see. That is because you are paying that amount.
  • The net present value, or pv, is the following argument.
  • Lastly, press the Enter key and Excel will return the result accordingly.

  • According to this outcome, you are paying a 28.65% discount rate on the loan.

Read More: How to Calculate Discount Rate for NPV in Excel (3 Useful Methods)


Things to Keep in Mind

Keep in mind that the payment (pmt) should be negative when using the RATE function.


Conclusion

I hope the above methods will be helpful for you to calculate the discount rate in Excel. Download the practice workbook and give it a try. Let us know your feedback in the comment section. Follow our website ExcelDemy to get more articles like this.


Related Articles

Sagufta Tarannum

Sagufta Tarannum

Hi, I am Sagufta. I have completed my graduation in Civil Engineering from Bangladesh University of Engineering and Technology. I am very much interested about research and innovation in the field of Civil Engineering.

We will be happy to hear your thoughts

Leave a reply

5 Excel Tips
You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo