For those geeky types who put some Excel and DCF in their in investment strategies; Was just playing around with Excel and thought someone might find this useful?

DCF.jpg

Just add the code at the bottom of this post to a module in a macro enabled file and then type the following function and values in a cell...

Code:
=DCF([EPS], [Growth Rate], [Growth Years], [Discount Rate], [Termination Rate])
Didn't write any error captures but if it works you used it right.

Code:
Public Function DCF(dbl_EPS As Double, dbl_GROWTH As Double, lng_YEARS As Long, _
dbl_DISCOUNT As Double, dbl_TERMINATION As Double)

(c) Halebop 2012 - this work is licensed under a creative commons attribution sharealike 3.0 New Zealand license
http://creativecommons.org/licenses/by-sa/3.0/nz/

'Dimensions
Dim dbl_PRICE As Double
Dim a As Long
Dim dbl_CALC_VALUE As Double
Dim dbl_CALC_TERM As Double
Dim dbl_TERM_EPS As Double

'Calculate annual EPS and compound by Growth (dbl_GROWTH) over the X years (lng_YEARS)
'Also discount the Growth EPS by the discount factor (dbl_DISCOUNT)
For a = 1 To lng_YEARS
    dbl_CALC_VALUE = dbl_CALC_VALUE + (dbl_EPS * ((1 + dbl_GROWTH) ^ a)) / ((1 + dbl_DISCOUNT) ^ a)
Next a

'Calculate the Termination EPS at end of the growth years period (lng_YEARS + 1)
dbl_TERM_EPS = (dbl_EPS * ((1 + dbl_GROWTH) ^ lng_YEARS)) * (1 + dbl_TERMINATION)
'Discount the termination EPS by the dbl_DISCOUNT factor
dbl_CALC_TERM = dbl_TERM_EPS / (dbl_DISCOUNT - dbl_TERMINATION) / (1 + dbl_DISCOUNT) ^ lng_YEARS
'Add the Value calcuation comonants (Growth EPS period) + the termination EPS to determine value
dbl_PRICE = dbl_CALC_VALUE + dbl_CALC_TERM
'Set value back to function name (Output to cell)
DCF = dbl_PRICE

End Function