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

It’s used by Excel to shed added light on the NPV formula and the impact that discounting can have.1

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

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.

NPV Formula

What is the NPV Formula?

The NPV formula is a way of calculating the Net Present Value (NPV) of a series of cash flows based on a specified discount rate.  The NPV formula can be very useful for financial analysis and financial modeling when determining the value of an investment (a company, a project, a cost-saving initiative, etc.).

Below is an illustration of the NPV formula for a single cash flow.

NPV formula single cash flow

 

NPV for a Series of Cash Flows

In most cases, a financial analyst needs to calculate the net present value of a series of cash flows, not just one individual cash flow.  The formula works in the same way, however, each cash flow has to be discounted individually, and then all of them are added together.

Here is an illustration of a series of cash flows being discounted:

NPV formula for a series of cash flows

 

What is the Math Behind the NPV Formula?

Here is the mathematical formula for calculating the present value of an individual cash flow.

NPV = F / [ (1 + i)^n ]

Where,

PV = Present Value
F = Future payment (cash flow)
i = Discount rate (or interest rate)
n = the number of periods in the future the cash flow is

 

How to Use the NPV Formula in Excel

Most financial analysts never calculate the net present value by hand nor with a calculator, instead, they use Excel.

=NPV(discount rate, series of cash flow)

(See screenshots below)

Example of how to use the NPV function:

Step 1: Set a discount rate in a cell.

Step 2: Establish a series of cash flows (must be in consecutive cells).

Step 3: Type “=NPV(“  and select the discount rate “,” then select the cash flow cells and “)”.

Congratulations, you have now calculated net present value in Excel!

Download the free template.

 

NPV function in Excel

 

 

NPV Excel formula

 

 

If you need to be very precise in your calculation, it’s highly recommended to use XNPV instead of the regular function.

To find out why, read CFI’s guide to XNPV vs NPV in Excel.

 

Video Explanation of the NPV Formula

Below is a short video explanation of how the formula works, including a detailed example with an illustration of how future cash flows become discounted back to the present.

 

DCF Modeling

The main use of the NPV formula is in Discounted Cash Flow (DCF) modeling in Excel.  In DCF models an analyst will forecast a company’s three financial statements into the future and calculate the company’s Free Cash Flow to the Firm (FCFF). Additionally, a terminal value is calculated at the end of the forecast period. Each of the cash flows in the forecast and terminal value are then discounted back to the present using a hurdle rate of the firm’s weighted average cost of capital (WACC).

How to Calculate Discounted Cash Flow in Excel

Do you need to calculate the present value of future cash flows or assess two options that will impact your cash flow over many years? Excel’s a great place to do that and below I’ll show you how you can easily set up a template to calculate discounted cash flow that you can adjust for changes in the discount rate and cash flow. And if you don’t want to create your own template, you can download mine at the bottom of this post.

In this example, I’ll compare a lump sum lottery win versus a scenario where you receive an annual amount for 25 years. Step one is knowing to calculate present value, which is what I’ll cover next:

Calculating the preset value

To calculate the present value of future cash flow, you need to know what discount rate to use. What you can use is the rate that you can earn on a typical investment. For instance, if you invest in stocks and assume you can make 5% per year, on average, then you might want to use that as your discount rate. If you want to be more conservative, you could use a rate of 2%. Below, you’ll see how the discount rate can play a big impact in your calculations.

That’s because when calculating today’s present value, you have to use the discount rate to bring the future value back to what it would be worth today. For example, suppose you were to receive a $10,000 payment a year from now, and your discount rate was 5%. An easy way to calculate this is as follows:

Calculating discounted cash flows one year out.

You might see other formulas on the web involving fractions to calculate present value but just using a negative power does the trick. This calculation yields a result of $9,523.81. Because you’re not getting the payment today, the value of that money is worth less than the full amount. Consider that if you were to receive $10,000 today and invest it and earn 5%, then a year from now it would be worth $10,500 — more than if you were to receive the $10,000 in a year.

Now, suppose you used a discount rate of just 2%. In that scenario, the $10,000 payment a year from now would be worth $9,803.92 today. Since the discount rate is lower, there’s less of a cost associated with waiting for your payment. If the discount rate was 0%, then there would be no incentive for you to invest your money since a year from now it would still be worth the same value it is today. That’s why when interest rates fall and get closer to zero, people will be less inclined to keep their money at the bank and there’s more demand for gold — since that can be a better way to store wealth at that point.

Creating a template to calculate discounted cash flow in Excel

Now that we’ve gone over how to calculate discounted cash flow in Excel, we can set up the template. All that’s really necessary here is to map out the payment schedule, including how much cash you’ll receive every year. Here’s an example scenario of receiving $100,000 for 25 years:

Receiving 100,000 every year for 25 years.

All the payments don’t have to be the same, but for the lottery example, I’m going to keep them that way. What I can do is create another column that will tell me the present value of each one of those payments. To do that, I’ll use a formula that takes the cash flow value, multiples it by the discount rate (I’ll use 5%) raised to a negative power (the year). Here’s how that looks:

Discounted cash flow template.

I created a discount rate named range so that it’s easy to reference the percentage and to change it. The only thing left here is to calculate the total of all these payments, to arrive at the present value of all of them:

Present value calculation of all the payments over 25 years.

The total present value of the payments comes in at just over $1.4 million. Even though the total of all the payments over 25 years is $2.5 million, we’re losing a lot of that value because of the time value of money, at a rate of 5% per year.

However, let’s prove this out, and to do that let’s look at the future value of all these payments. Let’s assume that these funds will be reinvested and earning a rate of 5% every year. Here’s how much we’d have by the end of year 25:

Calculating future value of payments over 25 years.

In this situation, we’re benefitting from compounding and earning 5% on each year’s ending balance, which includes the prior-year return. By the end of year 25, if we were to invest all of these $100,000 payments at a rate of 5%, we’d have a future ending value of $4,772,709.88.

Now, remember, the equivalent of these annual payments is a present value of $1,409,394.46. Let’s assume that rather than receiving annual payments of $100,000, we simply receive a lump sum payment of this and invest it and also earn 5% every year. Here’s how that will look like:

Lump sum payment earning 5% every year.

The ending value after 25 years is the same, $4,772,709.88. This tells us that if you’re given the option of 25 annual payments of $100,000 or a lump sum of $1,409,394.46 today, there’s no difference to you (if the discount rate you’re using is 5%). If the discount rate is 2%, then the present value climbs to $1,952,345.65.

As you can see, depending on which discount rate you use, it can have a significant impact on your present value calculations. This template will allow you to quickly change the discount rate and see how the calculation looks under different scenarios. You can also add more years to this calculation by just extending the formulas down. The amounts also don’t need to be identical, they were only set up this way purely for the purpose of comparing lottery winnings in a scenario where you earn one lump sum amount versus equal payments over multiple decades.