Question

Suppose the Price/Earnings Ratio for the S&P 500 is 22 and the dividend payout ratio of the S&P

500 is 45%. The future growth rate of dividends is expected to be 4.80%. (USE EXCEL AND SHOW ALL FORMULAS)

a. Compute the expected return of the Market.

b. Use Goal Seek or Solver to determine the dividend growth rate that would yield an

expected Market return of 8%.

On Goal Seek please identify the..

i. Set Objective (Cell)

ii. To Value

iii. By Changing Variables Cell

iv. Subject to (if used

Answer 1

a: Expected return = 6.85%

b: Growth rate = 5.95%

Workings

	P/E Ratio	22
	Dividend payout ratio	45%
	Growth rate = G	4.80%
a	P/E = Dividend Payout Ratio / R – G
	Expected rate = R = Dividend payout/(P/E Ratio) + G	6.8455%
b	Expected Market return	8%
	Growth rate required	0.059545455
	P/E Ratio	22
	Dividend payout ratio	45%

Formulae

Book1 - Excel Sign in a - File Home Insert Page Layout Formulas Data Review View Help ? Tell me what you want to do w 50- G Data Analysis ? Solver Get External Data Show Queries From Table New Query- to Recent Sources Get & Transform 2 Sort 1x Clear Reapply Filter Advanced Sort & Filter Text to EV- Outline Connections E Properties Refresh All LJ Edit Links Connections P/E Ratio Columns Data Tools What-If Forecast Analysis Sheet Forecast Analyze 09 - X for в E X Goal Seek ? wN P/E Ratio Dividen payout ratio Growth rate = G 22 0.45 0.048 Set cell: B1 To value: 8% 4 5 6 a P/E = Dividend Payout Ratio / R-G Expected rate = R = Dividend payout/(P/E Ratio) + G =C2/C1+C3 By changing cell: $C$9 7 OK Cancel 8 b Expected Market return Growth rate required P/E Ratio 11 Dividen payout ratio 12 13 =C11/C10+C9 0.0595454545454545 22 0.45 14 15 16 17 18