Akib Bin Rashid
Excel is the most widely used tool when it comes to dealing with huge datasets. We can perform myriads of tasks of multiple dimensions in Excel. In this article, I will illustrate 3 useful methods for how to calculate discount rate for NPV in Excel.
Download Practice Workbook
Download this workbook and practice while going through the article.
Introduction to Net Present Value (NPV)
Net Present Value (NPV) is the value of all future cash transactions spanning the entire life of an investment after being discounted to the present.
The formula for Net Present Value is
NPV = FV/(1+i)^n
Where,
FV = Future Value of a cash flow
i = Discount Rate
n = number of time periods
3 Easy Methods to Calculate Discount Rate for NPV in Excel
This is the dataset for today’s article. We have the Future Value (after 1 year) and the Net Present Value (NPV). We will calculate the discount rate.
1. Calculate Discount Rate Manually Using NPV Formula
First, I will show how to calculate the discount rate manually. But before that, let’s understand the scenario first.
Suppose you are going to invest $8,000 today in a certain business. The return will be $10,000 after one year. Now you are going to calculate the rate of return i,e the discount rate.
Since you invest $8,000 today, this can be considered your NPV. And the return after 1 year ($10,000) will be your Future Value.
Now, let’s calculate the discount rate.
Steps:
Go to C8 and write down the following formula
=(C4/C5)^(1/C6)-1
- Now press ENTER. Excel will calculate the discount rate.
That means your rate of return on this investment will be 25.00%
Read More: Formula to Calculate Discount Percentage in Excel
2. Apply What-If-Analysis Feature to Calculate Discount Rate for NPV
We can also use the What-If-Analysis feature to calculate discount rate for NPV. In this method, we will set an NPV and Excel will calculate the discount rate.
Steps:
- Go to C5 and write down the following formula.
=C4/(1+C8)^C6
- Now press ENTER.
- Excel returned $10,000 as NPV since we have no interest rate. You can ignore this value as we will set our own NPV and will calculate the discount rate.
- Now, go to the Data tab >> Forecast >> What-If Analysis >> select Goal Seek.
- Goal Seek window will appear. Considering the NPV $8000, we will set C5 to 8000 by changing the discount rate C8. By doing so, Excel will calculate the discount rate required to have the NPV as $8000. Now click OK.
- Excel will calculate the discount rate. Format the rate to percentage, your final output will be like this.
Read More: How to Use IF Function in Excel for Discounts (4 Suitable Examples)
3. Use of RATE Function to Determine Discount Rate for Loan Payment
You can also use the RATE function to calculate the discount rate. This method is useful when you are dealing with a series of cash flows.
Before describing the method, let me explain the context first.
Suppose you have taken a loan of $25,000 today from a bank. Hence, you have to pay the loans. The condition is such that, you have to pay $10000 per year for the next 4 years. In these circumstances, if you want to calculate the interest rate, you can use the RATE function.
Steps:
- Go to C9 and write down the formula.
=RATE(C5,-C4,C6)
Explanation:
- The first argument NPER indicates the number of installments which is 4.
- The next one is PMT which indicates the cash flow in each installment. Note that there is a negative sign (–) before C4. That’s because you are paying that amount. So this is a cash outflow.
- The next argument is PV indicating the net present value.
- Now press ENTER. Excel will calculate the Interest Rate.
This result means you are paying the loan at a 21.86% interest rate.
Read More: How to Calculate Discount in Excel (2 Easy Methods)
Things to Remember
- While using the RATE function, remember that the payment amount (PMT) is negative.
Conclusion
In this article, I have demonstrated 3 effective methods to show how to calculate discount rate for NPV in Excel. I hope it helps everyone. If you have any kind of suggestions, ideas, or feedback, please feel free to comment down below.
