This businessman named John asked his buddy Peter to invest a big chunk of money in his company. But Peter’s on the fence about it. He wants to check out the potential revenue first because he’s worried that it could be a bad investment or, even worse, lead to bankruptcy.
At this point, Peter should be looking for the Discounted Cash Flow Model of John’s company, just to be sure.
What is the Discounted Cash Flow?
The Discounted Cash Flow (DCF) model in Excel is a great way to determine how much a company or investment is really worth. It looks at the money the company is expected to make in the future and figures out what that’s worth today.
To get started, you’ll want to think about a few key things:
- The potential revenue of the company.
- Its growth rate.
- How the present value of money stacks up against the future.
Finance teams use the DCF model to find value. This article shows you how to easily use the DCF model in Excel, guiding you through each step.
The DCF method helps evaluate finances and investments by estimating the present value of future cash flows. It recognizes that future money is worth less than money today due to factors like inflation and risk.
You’ll find discounted cash flow analysis used in a bunch of different situations, including:
Business Valuation – This method helps investors, analysts, and finance pros figure out what a business is really worth when they’re thinking about investing, buying, or merging.
Investment Analysis – You can use DCF to see how appealing different investment opportunities are, whether it’s stocks, bonds, real estate, or projects. It’s all about comparing what future cash flows might be worth today against what you’d have to spend now.
Capital Budgeting – Companies turn to DCF to make smart decisions about spending on things like new projects or buying equipment. It helps them figure out if those investments will bring in enough cash to make it worth the upfront costs.
Corporate Finance – DCF comes in handy for allocating capital, figuring out the cost of capital, and assessing whether strategic initiatives in the business world are financially sound.
Financial Planning – On a personal level, folks can use DCF to value their assets, like retirement plans, investments, or businesses. This is super useful for things like estate planning, succession planning, or managing wealth.
How to Compute the DCF
The DCF formula is basically a way to look at cash flows from different time periods. You take the cash flow for each period and divide it by one plus the discount rate (which we often call the WACC) raised to the power of that period number. It sounds complicated, but it helps us understand the value of future cash flows!
DCF Formula:
Let’s assume the following:
- The discount rate (r) is 10% (0.10).
- The cash flows over five years are as follows:
For example, for Year 1:
DCF=2000×0.9091=1818.18
Final DCF Value:
To find the Net Present Value (NPV) of these cash flows, you sum all the discounted cash flows:
NPV=1818.18+2892.86+3756.94+2732.28+4035.71=15236.97
You can create this table in Excel using formulas:
- Discount Factor Formula in Excel: =1/(1+$B$2)^A4 (assuming the discount rate is in cell B2 and the period in column A).
- Discounted Cash Flow Formula in Excel: =B4*C4 (where B4 is the cash flow and C4 is the discount factor).
What the DFC Formula Should Have
- Cash Flow (CF) – It is all about the total cash you get from owning a financial asset, like bonds or stocks, over a certain period. When you’re building a financial model for a company, you might hear this referred to as ‘unlevered free cash flow.’ If you’re looking at a bond, cash flow means the interest and/or principal payments you receive.
- Discount Rate (r) – When valuing a business, the discount rate usually matches the company’s Weighted Average Cost of Capital (WACC). Investors use WACC because it shows the expected return they want when putting their money into the company. For bonds, the discount rate is basically the interest rate tied to that security.
- Each cash flow happens over a specific time frame, which could be in years, quarters, or months. These intervals can be the same or different, and if they’re different, you’ll see them shown as a percentage of a year.
What does the DCF Tells You?
When considering an investment, it’s important to think about the time value of money and the return you expect. The Discounted Cash Flow (DCF) formula helps you decide how much to invest to reach your target return.
Basically, if the payment is below the DCF value, you’re looking at a rate of return that’s higher than the discount rate. But if you end up paying more than the DCF value, your return will dip below that discount rate.
When figuring out how much a business is worth, it’s pretty common to project its cash flows for the next five years and then add a terminal value. This makes sense because predicting how a business will perform beyond that timeframe can get tricky.
There are two popular ways to determine that terminal value:
- Exit Multiple Method – This one works on the idea that the business will be sold, and it calculates the terminal value using a specific multiple.
- Perpetual Growth Method – Here, we assume the business will keep growing at a steady rate forever.
What is the Relationship of DCF to NPV?
When we talk about the overall DCF for an investment, we usually refer to it as the Net Present Value (NPV). Breaking that down, “net” means we’re looking at all the cash flows—both the good and the bad. And “present value”? That’s just a fancy way of saying we’re bringing those cash flows back to the time when the investment starts.
DCF in Excel
If you’re using Microsoft Excel to figure out discounted cash flows, you’re looking at two handy formulas called “NPV.”
First up is the standard NPV formula, which you can write like this:
=NPV(discount rate, series of cash flows)
This one assumes that your cash inflows happen at regular intervals—like every year, quarter, or month. Just remember, that the discount rate you use should match the timing of those cash flows. So if you’re looking at annual cash flows, stick with an annual discount rate of r%.
Now, if your cash flows are a bit all over the place, that’s where the Time-Adjusted NPV formula, or XNPV, comes into play. You’d set it up like this:
=XNPV(discount rate, series of all cash flows, dates of all cash flows)
XNPV is great for cash flows that come at irregular times, making it ideal for things like acquisitions or non-standard cash flow schedules.
Valuing Future Cash Flows
The Discounted Cash Flow (DCF) method is a key tool in financial analysis that calculates the present value of future cash flows. By considering expected earnings, growth rates, and the time value of money, DCF helps determine the value of investments and projects.
Microsoft Excel offers NPV and XNPV formulas to handle both regular and irregular cash flows, making it easier to value investments accurately. Understanding these tools helps people make smart financial decisions and assess the true value of their investments and projects.
FAQs
How to do a discounted cash flow model in Excel?
To create a DCF model in Excel, first estimate the future cash flows of the business for a set number of years. Then, choose a discount rate (like the company’s cost of capital). Discount each future cash flow to its present value using the formula: Present Value = Cash Flow / (1 + Discount Rate)^Year. Finally, sum up all these present values to get the total value of the business.
What is DCF Modeling?
DCF modeling is a method used to estimate the value of a business or investment based on its future cash flows. It calculates how much those future cash flows are worth today by applying a discount rate.
What is the discounted FCF model?
The discounted free cash flow (FCF) model is a type of DCF model that specifically uses free cash flow (cash available after expenses and investments) to determine the value of a business. It’s popular because free cash flow shows what’s truly left for investors.
Is DCF the same as NPV?
DCF and NPV are closely related but not exactly the same. DCF calculates the present value of future cash flows. NPV (Net Present Value) takes this a step further by subtracting the initial investment cost from the DCF to show whether an investment is profitable.