dcf calculator - v2.0
DESCRIPTION
DCF Calculator - V2.0TRANSCRIPT
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