Table of Contents
Table of Contents

How to Calculate a Discount Rate in Excel

For discounted cash flow analysis, 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.

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 that results in the NPV equaling zero. Doing so allows us to determine the internal rate of return (IRR) of a project or asset.

Key Takeaways

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

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 relating to competing yet comparable projects, a project with the highest NPV should tip the scale toward its selection.

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. You also 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 question is, what is the discount rate that sets the IRR to zero? This is the same rate that will give the NPV a value of 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. It's often used to calculate the interest rate for a loan or to determine the rate of return required to meet a particular investment objective.

Calculating the Discount Rate in Excel

In Excel, you can solve for the discount rate in two 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.

Method One

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%.

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

Method Two

Let's now look at the second method, using Excel's What-If calculator. This assumes that 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 select "OK," Excel will recalculate WACC to equal the discount rate that makes the NPV zero (57%).

Discount Factor

When working with the discount rate, you may come across the discount factor, as well. They aren't the same thing although they may be used together in calculations. The discount factor, when multiplied by a cash flow value, discounts that value and provides a present value.

It's 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

  • Along with time period, it's used in the formula that calculates the discount factor.
  • It represents the time value of money.
  • It's a rate of return determined by a company.
  • It's used in the calculation of present value.

Discount Factor

  • As the discount rate increases due to compounding over time, the discount factor increases.
  • 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. In discounted cash flow analysis, it's used in the calculation of the present value 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?

It's the difference between the present value of cash flows and the present value of cash outlays. It's used by businesses for corporate budgeting and can help them determine the potential profitability of a proposed project or investment.

Article Sources

Investopedia requires writers to use primary sources to support their work. These include white papers, government data, original reporting, and interviews with industry experts. We also reference original research from other reputable publishers where appropriate. You can learn more about the standards we follow in producing accurate, unbiased content in our editorial policy.
  1. Corporate Finance Institute. "Discount Factor."

Take the Next Step to Invest
×
The offers that appear in this table are from partnerships from which Investopedia receives compensation. This compensation may impact how and where listings appear. Investopedia does not include all offers available in the marketplace.
Service
Name
Description