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
See how MarketXLS helps you take advantage in the markets.
Download Option Templates →
 

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.

STEP1.jpg”>STEP1-300×108.jpg” alt=”DCF TEMPLATE” width=”300″ height=”108″ />

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

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

STEP2.jpg”>STEP2-300×117.jpg” alt=”DCF CALCULATION” width=”300″ height=”117″ />

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
Download Option Templates

Top 100 Gainers Today

Top 100 losers Today

Real gdpReal personal consumption expReal private investmentReal govt expenditureReal net exportsReal exportsReal importsFederal receiptsFederal outlaysFederal surplus or deficitFederal debtReal private investment nonresidentialReal private investment residentialReal potential gdpReal personal incomeReal personal consumption exp monthlyRpce durable goodsRpce nondurable goodsRpce servicesPersonal savings rateMonetary baseCurrency in circulationBank reservesMoney supply m1Money supply m2Sp500DjiaWilshire indexVixFinancial stress indexCorporate bond index aaCorporate bond index bbbFederal funds rateTreasury rate 3mTreasury rate 1yTreasury rate 5yTreasury rate 10yTips 5yTips 10yBond yield aaaBond yield baaMortgage rate 15yMortgage rate 30yUs dollar weighted averageUsdollar to euroUsdollar to poundYuan to usdollarCanadiandollar to usdollarYen to usdollarCpiCpi wo food energyCpi foodCpi energyChain price indexChain price index wo food energyGdp price deflatorPpi final demandPpi finished goodsPpi materialPpi crude goodsPpi final demand wo food energyPpi finished goods wo food energyHouse price indexHouse price index 20cityCrude oil priceGasoline priceNatural gas priceIndustrial productionCapacity utilisationInventoriesSales retail foodVehicle sales light weightManufacture orders durablesManufacture orders capital goodsLoansConsumer credit outstandingCorporate profitsHousing startsBuilding permitsResidential constructionEmployees nonfarmEmployees privateEmployees goods producingEmployees service providingEmployees governmentUnemployment rateInitial cliamsAverage weeks unemployedJob openingsHiresSeparationsQuitsLayoffs dischargesHours of productionHourly earningsReal outputPopulationLabor forceLabor force participation rate
Search for a stock

Top MarketXLS Rank stocks

Booz Allen Hamilton Holding Corporation logo

Booz Allen Hamilton Holding Corporation

92.84
 
USD
 
2.48
 
(2.74%)
84
Rank
Optionable: Yes
Market Cap: 11,592 M
Industry: Business Services
52 week range    
69.32   
   93.45
Rush Enterprises Inc. Class B Common Stock logo

Rush Enterprises Inc. Class B Common Stock

51.55
 
USD
 
1.94
 
(3.91%)
81
Rank
Optionable: No
Market Cap: 2,753 M
Industry: Auto & Truck Dealerships
52 week range    
35.64   
   57.55
BWX Technologies Inc. logo

BWX Technologies Inc.

56.44
 
USD
 
1.35
 
(2.45%)
80
Rank
Optionable: Yes
Market Cap: 4,846 M
Industry: Aerospace & Defense
52 week range    
42.22   
   61.75
Ritchie Bros. Auctioneers Incorporated logo

Ritchie Bros. Auctioneers Incorporated

67.49
 
USD
 
2.43
 
(3.74%)
77
Rank
Optionable: Yes
Market Cap: 6,928 M
Industry: Business Services
52 week range    
48.44   
   75.28
Ituran Location and Control Ltd. logo

Ituran Location and Control Ltd.

24.33
 
USD
 
-0.16
 
(-0.65%)
77
Rank
Optionable: Yes
Market Cap: 504 M
Industry: Communication Equipment
52 week range    
19.49   
   29.33
General Mills Inc. logo

General Mills Inc.

75.79
 
USD
 
0.34
 
(0.45%)
76
Rank
Optionable: Yes
Market Cap: 42,311 M
Industry: Packaged Foods
52 week range    
55.38   
   75.96
CRA InternationalInc. logo

CRA InternationalInc.

89.15
 
USD
 
-0.17
 
(-0.19%)
76
Rank
Optionable: Yes
Market Cap: 646 M
Industry: Business Services
52 week range    
78.05   
   115.47
ConAgra Brands Inc. logo

ConAgra Brands Inc.

34.56
 
USD
 
0.32
 
(0.93%)
76
Rank
Optionable: Yes
Market Cap: 16,287 M
Industry: Packaged Foods
52 week range    
29.80   
   36.67
Agree Realty Corporation logo

Agree Realty Corporation

73.53
 
USD
 
1.40
 
(1.94%)
76
Rank
Optionable: Yes
Market Cap: 5,522 M
Industry: REIT - Retail
52 week range    
60.78   
   74.67
International Money Express Inc. logo

International Money Express Inc.

21.77
 
USD
 
1.30
 
(6.35%)
75
Rank
Optionable: Yes
Market Cap: 781 M
Industry: Business Services
52 week range    
14.66   
   22.86

More Features

Stand with Ukraine

As the situation in Ukraine escalates, many of us in MarketXLS are left with emotions too overwhelming to name. If you’d like to show your support, but aren’t sure how to, we want to help make it easier for you to act.

For any amount donated, we’ll extend your MarketXLS subscription for double of the donated amount. Please send proof of your payment to support@marketxls.com to avail the extention

From all of us at MarketXLS, thank you!