The discount rate is the interest rate used when calculating the net present value (NPV) of an investment. NPV is a core component of corporate budgeting and is a comprehensive way to calculate whether a proposed project will add value or not. For this article, when we look at the discount rate, we will be solving for the rate such that the NPV equals zero. Doing so allows us to determine the internal rate of return (IRR) of a project or asset.

Discount Rate

First, let's examine each step of NPV in order. The formula is:

NPV = ∑ {After-Tax Cash Flow / (1+r)^t} - Initial Investment

Broken down, each period's after-tax cash flow at time t is discounted by some rate, shown as r. The sum of all these discounted cash flows is then offset by the initial investment, which equals the current NPV. Any NPV greater than $0 is a value-added project. In the decision-making process among competing projects, having the highest NPV should go a long way toward being chosen, if the projects are comparable.

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

The NPV, IRR, and discount rate are all connected concepts. With an NPV, you know the amount and timing of cash flows, and you know the weighted average cost of capital (WACC), which is designated as r when solving for the NPV. With an IRR, you know the same details, and you can solve for the NPV expressed as a percentage return. 

The big question is—what is the discount rate that sets the IRR to zero? This is the same rate that will cause the NPV to be zero. As you will see below, if the discount rate equals the IRR, then the NPV is zero. Or to put it another way, if the cost of capital equals the return of capital, then the project will break even and have an NPV of 0. 

=RATE (nper, pmt, pv, [fv], [type], [guess])

The Excel formula for calculating the discount rate—often used for calculating the interest rate for a loan or determining the rate of return required for hitting a particular investment objective.

Calculating the Discount Rate in Excel

In Excel, you can solve for the discount rate a few ways:

  • You can find the IRR, and use that as the discount rate, which causes NPV to equal zero. 
  • You can use What-If analysis, a built-in calculator in Excel, to solve for the discount rate that equals zero.

To illustrate the first method, we will take our NPV/IRR example. Using a hypothetical outlay, our WACC risk-free rate, and expected after-tax cash flows, we've calculated an NPV of $472,169 with an IRR of 57%.

Since we've already defined the discount rate as a WACC that causes the IRR to equal 0, we can just take our calculated IRR and put it in place of WACC to see the NPV of 0. That replacement is shown below:

Let's now look at the second method, using Excel's What-If calculator. This assumes we did not calculate the IRR of 57%, as we did above, and have no idea what the correct discount rate is.

To get to the What-If solver, go to the Data Tab —> What-If Analysis Menu —> Goal Seek. Then simply plug in the numbers and Excel will solve for the correct value. When you hit "OK," Excel will recalculate WACC to equal the discount rate that makes the NPV zero (57%).