How to Calculate Discount Rate in Excel

This guide will explain how to calculate the discount rate in Excel.

Discounting refers to the process of adjusting the value of future cash flows to their present value. This method takes recognizes  that money received in the future is worth less than the same amount received or paid today.

The discount rate refers to the rate to use when calculating the net present value of an investment. In discounted cash flow analysis, the discount rate will help determine the present value of future cash flows.

We can find the discount rate using the formula for weighted average cost of capital (WACC) in Microsoft Excel. In this guide, we will provide a step-by-step tutorial on how to calculate the discount rate in Excel.

A Real Example of Calculating the Discount Rate in Excel

The weighted average cost of capital or WACC is used to calculate the value of a firm considering several factors.

We’ll need to know the following values to calculate a firm’s WACC:

  • The market value of equity (E)
  • The market value of debt (D)
  • The total market value of equity and debt (V)
  • The cost of equity (Re)
  • The cost of debt (Rd)
  • The corporate tax rate (Tc)

The weighted average cost of capital can be calculated using the following formula:

WACC = (E/V * Re) + (D/V * Rd * (1 - Tc))

The first term in our formula (E/V * Re) calculates the weighted value of our equity capital, while thesecond term, (D/V * Rd * (1 – Tc)) calculates the weighted average of debt capital.

The output of the WACC formula is used as  the discount rate when calculating the net present value of a business.

Let’s explore a simple example where we can use the WACC formula.

Suppose you run a company with the following details:

  • A Market value of equity of $100,000
  • A Market value of debt of $50,000
  • 8& cost of equity
  • 5% cost of debt
  • Corporate tax rate of 25%

We can input our data into an Excel table to reference later.

calculate discount rate in Excel

Using this table of values, we can use the following WACC formula:

= ((B1/B3)*B4) + ((B2/B3)*B5*(1 - B6))
calculate discount rate in Excel with the WACC formula

After evaluating our formula, we now know that the WACC of our company is 6.58%. This rate can then be used as the interest rate for calculating the net present value of your company’s future cash flows.

Click on the link below to create your own copy of our examples.

Head to the next section to read our step-by-step tutorial on how to calculate the discount rate of a business.

How to Calculate Discount Rate in Excel

  1. Indicate in your Excel spreadsheet the market value of both your equity (E) and debt (D).
    enter market value of equity and debt
  2. Next, solve for the total market value of both equity and debt.
    solve for the total market value of equity and debt
    In our example, we’ll use the formula =B1+B2 to find the sum of E and D.
  3. Write down the cost of equity, cost of debt, and corporate tax rate. Ensure these are written down as percentage values.
    Write down the cost of equity, cost of debt, and corporate tax rate
  4. Use the WACC formula to find the weighted average cost of capital of your company.
    Use the WACC formula to find the weighted average cost of capital of your company or the discount rate
    In this example, we’ll use the formula =((B1/B3)*B4)+((B2/B3)*B5*(1-B6)).

These are all the steps you need to calculate the discount rate using the WACC formula in Excel.

FAQs

  1. What does a higher WACC indicate?
    A higher WACC indicates a higher overall cost of capital, rendering potential projects or investments less attractive.

  2. How often should WACC be recalculated?
    WACC should be recalculated when there are significant changes in a company’s capital structure, cost of debt, or cost of equity. It’s advisable to update WACC periodically to reflect current market conditions.

To learn more about using Excel for making informed decisions with investing, you can read our post on how to forecast cash flow in Excel.

That’s all for this guide! Be sure to check out our library of spreadsheet resources, tips, and tricks! 

Get emails from us about Excel.

Our goal this year is to create lots of rich, bite-sized tutorials for Excel users like you. If you liked this one, you'd love what we are working on! Readers receive early access to new content.

0 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like