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.
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
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)
- Therefore, press the Enter key to get the result.
- 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.
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.
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)
Here, cells C5 and B5 denote the Sales Price and the Original Price respectively.
- Now, press Enter and get the result.
- At this moment, to fill this formula into the desired range, drag the fill handle.
- 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.
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
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.
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)
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.
- 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.
Steps:
- To input the NPV, first, select cell C6 and type the formula below:
=C5/(1+C9)^C7
- Then, press Enter.
- 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.
- 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.
- 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.
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.
