dcf calculator - v2.0

Upload: marc-edwards

Post on 13-Jan-2016

25 views

Category:

Documents


7 download

DESCRIPTION

DCF Calculator - V2.0

TRANSCRIPT

DCF ValuationClick File > Make a copy.. to start using this spreadsheet

Ticker
: Insert a ticker symbol in cell A4 to automatically run the DCF modelIntrinsic Value
: This figure shows what the stock is worth per share according to the DCF modelmsftAnalyze US listed companies by filling in their ticker symbol on the left$60More spreadsheets onvaluespreadsheet.comCompany infoNameMicrosoft CorporationIndustryBusiness Software & ServicesMarket Cap (mil)$366,134ExchangeNASDAQ

InputsCash and Short Term Investments$95,156Cash from Operating Activities$32,231Capital Expenditures-$5,485Free cash flow (mil $)$26,746Shares outstanding (mil)$8,090Return on Equity21.97%Growth rate7.16%Margin of Safety25.00%
: This determines how conservative your estimate will be. Higher is more conservative.Conservative growth rate5.37%Growth decline rate5.00%
: The rate at which you expect future growth to decline.Discount rate9.00%
: The discount rate used to calculate the Net Present Value (NPV) of the cash flowsValuation last FCF12
: An arbitrary multiple at which you believe the company might be sold in 10 years time. This is a necessary assumption for the DCF model to work. Choose a value between 12 and 15, where 12 is more conservative and 15 more aggressive.

CalculationsYearFCF * Growth rateNPV FCF1$28,182.26$25,855.282$29,619.98$24,930.543$31,055.49$23,980.534$32,485.32$23,013.425$33,906.19$22,036.706$35,315.06$21,057.227$36,709.10$20,081.148$38,085.72$19,113.949$39,442.55$18,160.4510$40,777.46$17,224.84

Total NPV FCF$215,454.05Year 10 FCF value$206,698.07Cash on Hand$95,156.00Total Debt$31,868.00Company value:$485,440.13

Creation date:11/25/2014 12:44:00