The discount rate is the interest rate used when calculating the net present value (NPV) of something. 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 Defined

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, and in the decision-making process among competing projects, having the highest NPV should go a long way towards being chosen.

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 a NPV of 0. 

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 a NPV of $472,169 with an IRR of 57%. (Note: If table is hard to read, right-click and hit "view" for a bigger image.)

Since we 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 to discount rate that makes the NPV zero (57%).

  1. How do you calculate IRR in Excel?

    Understand how to calculate the internal rate of return (IRR) in Excel and how it's used to determine anticipated yield per ... Read Answer >>
  2. What's the difference between weighted average cost of capital (WACC) and internal ...

    Both weighted average cost of capital (WACC) and internal rate of return (IRR) are great measures for assessing value, but ... Read Answer >>
  3. How do you use internal rate of return to calculate a capital budget?

    Learn about how the internal rate of return is used in the creation of a capital budget along with net present value and ... Read Answer >>
  4. How do you use discounted cash flow to calculate a capital budget?

    Learn how discounted cash flows are used in creating capital budgets as a part of the net present value and internal rate ... Read Answer >>
  5. What is the relationship between the hurdle rate (MARR) and the Internal Rate of ...

    Find out how companies and managers use hurdle rate, or MARR, and internal rate of return, or IRR, to evaluate projects and ... Read Answer >>
  6. What's the difference between net present value and internal rate of return? How ...

    Both of these measurements are primarily used in capital budgeting, the process by which companies determine whether a new ... Read Answer >>
Related Articles
  1. Investing

    Return on Investment (ROI) Vs. Internal Rate of Return (IRR)

    Read about the similarities and differences between an investment's internal rate of return (IRR) and its return on investment (ROI).
  2. Investing

    Internal Rate of Return Formula for Excel

    The internal rate of return, or IRR, is a popular metric businesses use to measure a project’s return on investment.
  3. Investing

    An Introduction To Capital Budgeting

    We look at three widely used valuation methods and figure out how companies justify spending.
  4. Financial Advisor

    Understanding Internal Rate Of Return

    Internal rate of return, or IRR, is one of the most popular methods of evaluating potential projects. Learn more about this important metric.
  5. Investing

    Private Equity on Steroids: How Debt Fuels Outsized Returns

    Hot Air: Many private equity firms use a legal technique that can boost returns by 25% or more, Bloomberg says
  6. Financial Advisor

    A Guide on the Risk-Adjusted Discount Rate

    When a project or investment faces higher amounts of risk or uncertainty, it may be appropriate to utilize the risk-adjusted discount rate.
  7. Investing

    Investors Need A Good WACC

    Weighted average cost of capital may be hard to calculate, but it's a solid way to measure investment quality.
  1. Internal Rate of Return - IRR

    Internal Rate of Return (IRR) is a metric used in capital budgeting ...
  2. The Net Internal Rate Of Return - Net IRR

    A measure of a portfolio or fund's performance that is equal ...
  3. Pooled Internal Rate Of Return - PIRR

    A method of calculating the overall internal rate of return (IRR) ...
  4. Modified Internal Rate Of Return - MIRR

    While the internal rate of return (IRR) assumes the cash flows ...
  5. Discounting

    The process of determining the present value of a payment or ...
  6. Present Value - PV

    The current worth of a future sum of money or stream of cash ...
Hot Definitions
  1. Earnings Per Share - EPS

    Earnings per share (EPS) is the portion of a company's profit allocated to each outstanding share of common stock.
  2. Trustee

    A person or firm that holds or administers property or assets for the benefit of a third party. A trustee may be appointed ...
  3. Gross Domestic Product - GDP

    GDP is the monetary value of all the finished goods and services produced within a country's borders in a specific time period, ...
  4. Debt/Equity Ratio

    The D/E ratio indicates how much debt a company is using to finance its assets relative to the value of shareholders’ equity.
  5. Exchange-Traded Fund (ETF)

    A security that tracks an index, a commodity or a basket of assets like an index fund, but trades like a stock on an exchange.
  6. Net Present Value - NPV

    Net Present Value (NPV) is the difference between the present value of cash inflows and the present value of cash outflows ...
Trading Center