DCF missing results

[msmith01]

I am having issues I cannot seem to find the solution to in regards to the DCF analysis here.

I am running the DCF analysis for a number of firms and for many, the code works as expected.

The problem is that when I run the analysis for some firms I have 'gaps' in the DCF data.

For example when I run the following code for ticker 'LOW' I do not get any gaps.

###############################################################################
# Load Systematic Investor Toolbox (SIT)
# http://systematicinvestor.wordpress.com/systematic-investor-toolbox/
###############################################################################
con = gzcon(url('http://www.systematicportfolio.com/sit.gz', 'rb'))
source(con)
close(con)

#*****************************************************************
# Load historical fundamental and pricing data
#****************************************************************** 
load.packages('quantmod') 
tickers = spl('LOW')
tickers.temp = spl('NASDAQ:LOW')

# get fundamental data
data.fund <- new.env()
for(i in 1:len(tickers)) {
  data.fund[[tickers[i]]] = fund.data(tickers.temp[i], 80, 'quarterly') #Put 'annual' for annual data
}

# get pricing data
data <- new.env()
getSymbols(tickers, src = 'yahoo', from = '1970-01-01', env = data, auto.assign = T)
for(i in ls(data)) data[[i]] = adjustOHLC(data[[i]], use.Adjusted=T)            

# prepare data

fund = data.fund[[tickers[1]]]
fund.date = date.fund.data(fund)            
price = Cl(data[[tickers[1]]]['1995::'])

#*****************************************************************
# Extract Inputs for DCF Valuation
#******************************************************************                 
# Free Cash Flows
FCF = get.fund.data('free cash flow', fund, fund.date)

# Invested Capital
IC = get.fund.data('invested capital', fund, fund.date)

# Sales
SALE = get.fund.data('total revenue', fund, fund.date)

# Common Equity
CEQ = get.fund.data('total equity', fund, fund.date)

# Common Shares Outstanding
CSHO = get.fund.data('total common shares out', fund, fund.date)

# Growth Rate
CROIC = FCF/IC

# Average inputs
g = runMean(CROIC, 5)
cash = runMean(FCF, 5)

#*****************************************************************
# Helper function to compute Intrinsic Value
#******************************************************************                 
compute.DCF.IV <- function(cash, eqity, shares, g, R) {
  if( cash <= 0 ) return(NA)
  
  if( len(R) == 1 ) R = rep(R, len(g))
  
  value = eqity + sum(cash * cumprod(1 + g) / cumprod(1 + R))
  return( value / shares )
}

#*****************************************************************
# Compute Intrinsic Value, assumptions:
# Company will grow for the first 3 years at current Growth Rate
# slowed down by 20% for the next 4 years, and slowed down by a further 20% for the next 3 years
# and finally 3% growth for the next 10 years
#
# The Discount Rate is 9%
#
# http://www.oldschoolvalue.com/blog/stock-analysis/apple-aapl-valuation/
#******************************************************************                 
dcf.price = NA * g
i.start = which(!is.na(g))[1] 

for(i in i.start : nrow(g)) {
  # Create Growth Rate scenario:      
  g.scenario = c(rep(g[i],3), rep(g[i],4)*0.8, rep(g[i],3)*0.8*0.8, rep(3/100,10))
  
  # Compute Intrinsic Value
  dcf.price[i] = compute.DCF.IV(cash[i], CEQ[i], CSHO[i], g.scenario, 9/100)
}

#*****************************************************************
# Create Plots
#****************************************************************** 

#Remove last row of dcf.price 

#dcf.price <- dcf.price[-nrow(dcf.price),]

plota(price, type='l', log = 'y', col='blue', main=tickers[1],
      ylim=range(price,dcf.price,na.rm=T))
plota.lines(dcf.price, type='s', col='red', lwd=2)
plota.legend('Close,Intrinsic Value', 'blue,red', list(price, dcf.price))   


# plota(g, type='b', col='blue', pch=0, main='Growth Rate')   
# 
# 
# plota(cash, type='b', col='blue', pch=0, main='Free Cash Flows')

Which gives the following output:

However when I change the firm to ticker 'MU' I obtain gaps in the DCF analysis and obtain the following graphic.

The data for both firms downloads fine from the ADVFN website so I assuming something is not going right in the calculations for 'MU'.

Any help on how I can proceed with this would be great!

[systematicinvestor]

The gaps for MU are due to cash being negative. I.e. following line in the compute.DCF.IV function

compute.DCF.IV <- function(cash, eqity, shares, g, R) {
if( cash <= 0 ) return(NA)

[msmith01]

Thanks for the answer!

I have two questions regarding the time periods. In your code I changed the accounts from 'annual' to 'quarterly' at this line data.fund[[tickers[i]]] = fund.data(tickers.temp[i], 80, 'quarterly').

Firstly, what does the 80 represent in this line?

Secondly if I change the annual accounts to quarterly accounts do I have to change the following line?

g.scenario = c(rep(g[i],3), rep(g[i],4)*0.8, rep(g[i],4)*0.8*0.8, rep(3/100,10))

In your example on R bloggers and on github when you have annual data you say that it grows at a constant rate for 3 years, and then 20% less for 4 years etc. and finally a constant rate of 3% for the final 10 years. If the data was changed to quarterly how will I need to change the above line?

Would my following intuition be correct?

[g.scenario = c(rep(g[i],4), rep(g[i],16)*0.8, rep(g[i],16)*0.8*0.8, rep(3/100,40))](url)

The firm would grow at a constant rate for 4 quarters or 1 year then for 3 years (16 quarters) grow at a 20% less... etc. and finally grow for 10 years or 40 quarters at a constant 3%?

Again, thanks, great coded DCF!

[systematicinvestor]

The 80 represents number of periods to download. I.e. 80 quarters

If you change from annual reports to quarterly reports, you will need to use 12 month trailing SALEs. I.e
SALE = get.fund.data('total revenue', fund, fund.date, is.12m.rolling=T)
and
# Average inputs
g = runMean(CROIC, 5 * 4)
cash = runMean(FCF, 5 * 4)

Also change
g.scenario = c(rep(g[i],3), rep(g[i],4)*0.8, rep(g[i],3)0.80.8, rep(3/100,10))
to
g.scenario = c(rep(g[i],3 * 4), rep(g[i],4 * 4)*0.8, rep(g[i],3 * 4)0.80.8, rep((3 / 4)/100,10 * 4))

and
# Compute Intrinsic Value; scale rates to quarterly
dcf.price[i] = compute.DCF.IV(cash[i], CEQ[i], CSHO[i], g.scenario / 4, (9 / 4)/100)