How to Calculate a Discount Rate in Excel

The discount rate refers to the interest rate used when calculating the net present value (NPV) of an investment. It represents the time value of money, which is the concept that a sum of money today is worth more than the same sum at a future date. Why? Because of its earning potential in the intervening period. NPV helps determine whether a proposed project will add value, i.e., generate a profit. As such it’s a crucial part of corporate budgeting.

In this article we are looking for the discount rate that results in the NPV equaling zero. Doing so allows us to determine the internal rate of return (IRR) of a project or an asset. The IRR is the rate of growth that an investment must generate in order not to lose money.

Key Takeaways

• The discount rate is the interest rate used to calculate net present value (NPV).
• It represents the time value of money.
• NPV can help companies determine whether a proposed project may be profitable.
• NPV is essential to corporate budgeting.
• Excel can find the internal rate of return (IRR) and use that as the discount rate for an NPV of zero.

Discount Rate

Let’s begin by examining each step of NPV in order. The formula is:

NPV = ∑ {After-Tax Cash Flow ÷ (1+r)^t} – Initial Investment (where “t”  is a time period and “r” is the discount rate)

Each period’s after-tax cash flow at time “t” is discounted by some rate, which is shown as “r.” The sum of all these discounted cash flows, representing the total sum of money the investment is expected to generate expressed in today’s dollars, is then offset by the cost of the initial investment (what will be spent). The result is the current NPV. Any NPV greater than zero indicates a profitable project. The higher the NPV, the more likely the project will be green-lighted.

Note

The IRR is the discount rate that makes the NPV of future cash flows equal to zero.

The NPV, IRR, and discount rate are connected concepts. You know the amounts and timings of cash flows with an NPV. You also know the weighted average cost of capital (WACC), which is designated as “r” when solving for the NPV. You know the same details with an IRR, and you can solve for the NPV expressed as a percentage return. 

What is the discount rate that sets the IRR to zero? It’s the same rate that will give the NPV a value of zero. The NPV is zero if the discount rate equals the IRR. The project will break even and have an NPV of zero if the cost of capital equals the return of capital.

Important

The Excel formula for calculating the discount rate is =RATE (nper, pmt, pv, [fv], [type], [guess]). It’s often used to calculate the interest rate for a loan or determine the rate of return required to meet a particular investment objective.

Calculating the Discount Rate in Excel

You can solve for the discount rate in Excel in two ways:

• Find the IRR in Excel by employing the IRR function, then use it as the discount rate that causes NPV to equal zero. 
• Use What-If analysis, a built-in calculator in Excel, to solve for the discount rate that equals zero.

Method One

Take our NPV/IRR example. We’ve calculated an NPV of $472,169 with an IRR of 57% using a hypothetical outlay, our WACC risk-free rate, and expected after-tax cash flows.

We’ve already defined the discount rate as a WACC that causes the IRR to equal zero. We can therefore just take our calculated IRR and put it in place of WACC to get the NPV of zero. It looks like this:

Method Two

Let’s assume that we didn’t calculate the IRR of 57% as we did above and have no idea what the correct discount rate is. We can use Excel’s What-If calculator in this case.

Go to the Data Tab —> What-If Analysis Menu —> Goal Seek to get to the What-If solver, plug in the numbers, and Excel will solve for the correct value. Excel will recalculate the WACC to equal the discount rate that makes the NPV zero (57%) when you select “OK.”

Discount Factor

You may also come across the discount factor when working with the discount rate. They aren’t the same thing, but they may be used together in calculations. The discount factor is used in the calculation of present value (PV), which is what a future sum of money is worth in today’s dollars. It’s the number that you multiply the future amount of money by to get its worth today.

The discount factor is used by Excel to shed added light on the NPV formula and the impact that discounting can have. Here’s a comparison of the discount rate and the discount factor.

Discount Rate

• It’s used in the formula that calculates the discount factor along with the time period.
• It represents the time value of money.
• It’s a rate of return determined by a company.
• It’s used in the calculation of PV.

Discount Factor

• It increases as the discount rate increases due to compounding over time.
• It facilitates audits of a discounted-cash-flow model.
• It illustrates the effect of compounding.
• It’s an alternative to using the XNPV and XIRR functions in Excel.

What Is the Formula for the Discount Rate?

The formula for calculating the discount rate in Excel is =RATE (nper, pmt, pv, [fv], [type], [guess]).

What Does the Discount Rate Indicate?

The discount rate represents an interest rate. It’s used in the calculation of the PV of future money. It can tell you the amount of money you’d need today to earn a certain amount in the future.

What Is Net Present Value (NPV)?

NPV is the difference between the PV of cash flows and the PV of cash outlays. In other words, the amount of money your investment should earn translated into today’s dollars minus the amount of money in today’s dollars that your investment will cost. If it’s a positive number, your investment should be a profitable one, as it will make more money than it will cost. NPV is used by businesses for corporate budgeting.

The Bottom Line

The discount rate is the interest rate used to calculate NPV, which tells you the likelihood that a contemplated project might be profitable. It accounts for the difference between what an amount of cash is worth today and what the same amount will be worth at a later date in today’s dollars.

You can solve for the discount rate in Excel in two ways. Find the IRR and use it as the discount rate that causes NPV to equal zero. Alternately, you can use the What-If analysis tool, a built-in calculator in Excel, to solve for the discount rate that equals zero.