XLS www.stern.nyu.edu

This model is designed to value the equity in a stable firm payingdividends, which are roughly equal to Free Cashflows toEquity.Assumptions in the model:1. The firm is in steady state and will grow at a stable rate forever.2. The firm pays out what it can afford to in dividends, i.e., Dividends = FCFE.User defined inputsThe user has to define the following inputs to the model:1. Current Earnings per share and Payout ratio (Dividends/Earnings)2. Cost of Equity or Inputs to the CAPM (Beta, Riskfree rate, Risk Premium)3. Expected Growth Rate in Earnings and dividends forever.Please enter inputs to the model:Current Earnings per share =4.33(in currency)Current Payout Ratio =0.63(in percent)Are you directly entering the cost of equity? (Yes or No)NoIf yes, enter cost of equity =(in percent)If no, enter the inputs for the CAPMBeta of the stock =0.95Riskfree rate =0.07(in percent)Risk Premium=0.055(in percent)Expected Growth Rate =0.06 (in percent)The expected growth rate for a stable firmcannot be significantly higher than the nominalgrowth rate in the economy in which the firmoperates. It can be lower.Warnings:=IF(D28>10%,"This is high for a stable growth rate", "")=IF(D24>1.5,"This Beta is high for a stable firm"," ")=IF(D190.1,"This is high for an infinite growth rate. Check it"," ")Gordon Growth Model Value ==D39*(1+D42)/(D41-D42)Growth rateValue=D42+2%=$D$39*(1+B48)/($D$41-B48)=D42+1%=$D$39*(1+B49)/($D$41-B49)=D42=$D$39*(1+B50)/($D$41-B50)=D42-1%=$D$39*(1+B51)/($D$41-B51)=D42-2%=$D$39*(1+B52)/($D$41-B52)=D42-3%=$D$39*(1+B53)/($D$41-B53)=D42-4%=$D$39*(1+B54)/($D$41-B54)=D42-5%=$D$39*(1+B55)/($D$41-B55)=D42-6%=$D$39*(1+B56)/($D$41-B56) ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download