Home MarketXLS
Dashboard MarketXLS
Screener MarketXLS
Options Profit Calculator MarketXLS
Stock Ranks MarketXLS
Spreadsheet Builder MarketXLS
Documentation MarketXLS
Logout MarketXLS
CalculatorsFundamental analysisOthersScreenersStock investmentsTemplatesValuation Models

Discounted Cash Flow Calculator (Stock Valuation Using Marketxls Discounted Cash Flow Template)

Written by Syed Hammadahmed (Individual Contributor)
Thu Apr 15 2021
Discounted Cash Flow Calculator (Stock Valuation Using Marketxls Discounted Cash Flow Template) - MarketXLS
See how MarketXLS helps you take advantage in the markets.
Download Option Template →
Discounted Cash Flow Calculator (Stock Valuation Using Marketxls Discounted Cash Flow Template) - MarketXLS

Discounted cash flow or DCF is the method for estimating the current value of an investment by taking into account its future cash flows. It can be used to determine the estimated investment required to be made in order to receive predetermined returns. In short, DCF analysis attempts to figure out the value of an investment today, based on projections of how much money it will generate in the future.
For example, $50 will be worth more currently than 1 year later owing to interest accrual and inflation. If a person is seeking to invest $50 now, he or she will want to know its return on investment and what its future valuation will be, which can be calculated through DCF.

Where Can the Discounted Cash Flow Method Be Used?
DCF can be used to estimate the valuation of –
• A business
• Real estate
• Stocks
• Bonds
• Long-term Assets
• Equipment

Assumptions in DCF:
The underlying assumption in Discounted Cash Flow is that a company or an asset is expected to make money (generate cash flow) over time. The second assumption, which is also a fundamental theory, is that the value of money today is worth more than it will be tomorrow.
Based on the two principles, the method derives its model and also its name – Discounted Cash Flow
▪ Discounted is adjusting for the diminishing value of money
▪ Cash Flow is the money generated by a business/asset

The Method:
Here’s the equation:
Discounted Cash Flow = CF1 / (1+dr)1 + CF2/ (1+dr)2 +…..+ CFn/ (1+dr)n, where
▪ Discounted Cash Flow – It is the sum of all future discounted cash flows that an asset/company is expected to produce. This sum is the fair value that we are solving for.
▪ CF – It is the total cash flow for a given year. CF1 is for the first year, CF2 is for the second year and so on.
▪ dr – It is the discount rate which is nothing but the target rate of return that you are looking for from the investment. It is the weight average cost of capital (WACC).

DCF formula

 

Application in Real Life:
When you are looking to buy shares of a company, you need to project and discount the expected cash flows.
If you find that an investment that is priced below the sum of discounted cash flows, it depicts undervaluation and, therefore, a potentially rewarding investment.
On the other hand, if the price is higher than the sum of discounted cash flows, the asset may be overvalued.

Pros of DCF Analysis:
• Incredibly detailed and includes all significant assumptions regarding the business

• It helps determine the “intrinsic” value of a business

• It does not require any comparable companies

• Model is suitable for analyzing mergers and acquisition

• Used to compute the internal rate of return IRR of an investment which is critical for making investing decisions

Cons of DCF Analysis:
• The fundamental drawbacks of the model are that significant time is required to project the variables that are involved. While some parameters such as operating cost and revenue may be easy to anticipate in advance, but getting the right understanding of capital expenditure, other investments and funding mix remain an area of concern. Thus, even a minor deviation in any of the metrics leads to a vast change in the company’s valuation.

• The future cash flows would rely on a variety of factors, such as market demand, the status of the economy, technology, competition, and unforeseen threats or opportunities.

• Estimating future cash flows too high could result in choosing an investment that might not pay off in the future, hurting profits. Estimating cash flows too low, making an investment appear costly, could result in missed opportunities. Choosing a discount rate for the model is also an assumption and would have to be estimated correctly for the model to be worthwhile.

How to Apply the Discounted Cash Flow Calculator Using MarketXLS Template:

The output of valuation is the intrinsic value of a stock. The intrinsic value is compared with the current market value.
1) If Intrinsic value < Market value –> The stock is Overvalued
2) If Intrinsic value > Market value –> The stock is Undervalued

Step 1: Enter the stock ticker in cell B15 of the sheet to get the result. Here I have taken stock of NVIDIA Corporation as an example.

DCF TEMPLATE

Link: https://marketxls.com/template/dcf-model/

Step 2: Other useful parameters will be calculated as well. Scroll down below to check them.

DCF CALCULATION

Step 3: For deeper analysis, switch to the calculation sheet and look at the detailed working of a DCF model.

DCF CALCUALTION

Since the calculated intrinsic value per share comes out to be lower than the market value per share, the stock is overvalued by 90.41% according to the DCF model of valuation.

Why Use This Template?
The DCF valuation can be used to assess how the market has Overvalued/Undervalued a company and by what percentage.
The model also estimates and calculates loads of important data about a company such as Future cash flows, Firm’s value, Beta, Cost of equity, Cost of debt, WACC, etc.

Disclaimer:
All trademarks referenced are the property of their respective owners. Other trademarks and trade names may be used in this document to refer to either the entity claiming the marks and names or their products. MarketXLS disclaims any proprietary interest in trademarks and trade names other than its own, or affiliation with the trademark owner.
None of the content published on marketxls.com constitutes a recommendation that any particular security, portfolio of securities, transaction, or investment strategy is suitable for any specific person. The author is not offering any professional advice of any kind. The reader should consult a professional financial advisor to determine their suitability for any strategies discussed herein. The article is written for helping users collect the required information from various sources deemed to be an authority in their content. The trademarks if any are the property of their owners and no representations are made.

Reference:
https://www.investopedia.com/terms/d/dcf.asp
https://groww.in/blog/what-is-dcf-discounted-cash-flow-and-why-is-it-important/

Image Source:
https://www.zilculator.com/real-estate-analysis/calculate-discounted-cash-flow-formula-excel-example

Interested in building, analyzing and managing Portfolios in Excel?
Download our Free Portfolio Template
I agree to the MarketXLS Terms and Conditions
Call: 1-877-778-8358
Ankur Mohan MarketXLS
I am so happy you are here. My name is Ankur and I am the founder/CEO of MarketXLS. Over the past four years, I have helped more than 2500 customers to implement their own investment research strategies and monitoring systems in Excel.
Implement “your own” investment strategies in Excel with thousands of MarketXLS functions and starter sheets.
Get started today
Search for a stock

Get Access to 1 Billion Usable Market data points IN YOUR EXCEL SHEETS WITH EASY TO USE EXCEL FUNCTIONS

Get started today

Top MarketXLS Rank stocks

Rush Enterprises Inc. Class A Common Stock logo

Rush Enterprises Inc. Class A Common Stock

52.88
 
USD
 
1.12
 
(2.16%)
85
Rank
Optionable: Yes
Market Cap: 2,812 M
Industry: Auto & Truck Dealerships
52 week range    
42.72   
   55.06
Williams-Sonoma Inc. (DE) logo

Williams-Sonoma Inc. (DE)

126.10
 
USD
 
1.70
 
(1.37%)
84
Rank
Optionable: Yes
Market Cap: 8,021 M
Industry: Specialty Retail
52 week range    
101.58   
   176.89
Magnolia Oil & Gas Corporation Class A logo

Magnolia Oil & Gas Corporation Class A

23.81
 
USD
 
0.70
 
(3.03%)
83
Rank
Optionable: Yes
Market Cap: 4,657 M
Industry: Oil & Gas E&P
52 week range    
18.01   
   30.31
Foot Locker Inc. logo

Foot Locker Inc.

43.30
 
USD
 
2.59
 
(6.36%)
83
Rank
Optionable: Yes
Market Cap: 3,673 M
Industry: Footwear & Accessories
52 week range    
23.85   
   45.59
Shoe Carnival Inc. logo

Shoe Carnival Inc.

27.66
 
USD
 
-1.04
 
(-3.62%)
82
Rank
Optionable: Yes
Market Cap: 747 M
Industry: Apparel Stores
52 week range    
19.43   
   36.48
Group 1 Automotive Inc. logo

Group 1 Automotive Inc.

206.44
 
USD
 
9.36
 
(4.75%)
82
Rank
Optionable: Yes
Market Cap: 2,814 M
Industry: Auto & Truck Dealerships
52 week range    
136.16   
   206.49
Boot Barn Holdings Inc. logo

Boot Barn Holdings Inc.

87.42
 
USD
 
13.05
 
(17.55%)
81
Rank
Optionable: Yes
Market Cap: 2,188 M
Industry: Apparel Stores
52 week range    
50.20   
   105.66
Weis Markets Inc. logo

Weis Markets Inc.

87.46
 
USD
 
1.22
 
(1.41%)
80
Rank
Optionable: Yes
Market Cap: 2,298 M
Industry: Grocery Stores
52 week range    
58.40   
   95.57
PulteGroup Inc. logo

PulteGroup Inc.

52.37
 
USD
 
0.39
 
(0.75%)
80
Rank
Optionable: Yes
Market Cap: 11,760 M
Industry: Residential Construction
52 week range    
35.03   
   54.71
Penske Automotive Group Inc. logo

Penske Automotive Group Inc.

122.57
 
USD
 
2.42
 
(2.01%)
80
Rank
Optionable: Yes
Market Cap: 8,328 M
Industry: Auto & Truck Dealerships
52 week range    
88.58   
   131.55

More Features