Finance (excel functions)
h4919
2020S_FIN325_ClassExercise_Ch7.ValuingStock_A.xlsxDDM
| Discount Dividend Model. | |||
| Q1. Gentleman Gym just paid its annual dividend of $3 per share, and it is widely expected that the dividend will increase by 5% per year indefinitely. | |||
| a. What price should the stock sell at if the discount rate is 15%? | |||
| Current Price | $31.50 | =3*(1+5%)/(15%-5%) | |
| b. What price should the stock sell at if the discount rate is 12%. | |||
| Current Price | $45.00 | =3*(1+5%)/(12%-5%) | |
| Q2. Arts and Crafts, Inc., will pay a dividend of $5 per share in 1 year. It sells at $50 a share, and firms in the same industry provide an expected rate of return of 14%. What must be the expected growth rate of the company’s dividends? | |||
| Expected growth rate | 4.00% | =14%-5/50 | |
Yields
| Yields. Income Gain and Capital Gain in DDM Model | ||
| Horse and Buggy Inc. is in a declining industry. Sales, earnings, and dividends will be all shrinking at a rate of 10% per year with the following financial data. | ||
| Input data. | ||
| Annual growth rate | -10.00% | |
| Required return | 15% | |
| DIV1 | $3 | |
| a. what is the value of a share today? | ||
| Value of a share | $12.00 | =3/(15%+10%) |
| b. What price do you forecast for the stock next year? | ||
| Stock price | 10.800 | =3*(1-10%)/(15%+10%) |
| c. What rate of return should you expect if you buy the stock today and sell it in one year? | ||
| Expected rate of return | 15.00% | =15% |
| c-1. What is the income gain and the capital gain if you buy the stock today and sell it in one year? | ||
| Income gain (Dividend Yield) | 25.00% | =3/12 |
| Capital gain (Investment Yield, g) | -10.00% | =-10% |
Growth
| Growth | |||
| Here are the forcasted data on two stocks of the year 1, both of which have discount rates of 15%: | |||
| a. What are the dividend payout ratios for each firm? | |||
| b. What are the expected dividend growth rates for each firm? | |||
| c. What is the proper stock price for each firm? | |||
| Stock A | Stock B | ||
| Discount rate | 15% | 15% | |
| Return on equity at year 1 | 15% | 10% | |
| Earnings per share at year 1 | $2.00 | $1.50 | |
| Dividends per share at year 1 | $1.00 | $1.00 | |
| a. Dividend payout ratio | 50.00% | 66.67% | =1/1.5 |
| b. Expected dividend growth | 7.50% | 3.33% | =10%*(1-66.67%) |
| c. Stock price today | $13.33 | $8.57 | = 1/(15%-3.33%) |
PS valuation
| Preferred Stock Valuation. | |||
| Preferred Products has issued preferred stock with an annual dividend of $8 that will be paid in perpetuity. | |||
| Input variables: | |||
| Annual dividend | $8.00 | ||
| Discount rate | 12.00% | ||
| a. If the discount rate is 12%, at what price should the preferred sell? | |||
| Price0 | $66.67 | =8/12% | |
| b. At what price should the stock sell 1 year from now? | |||
| Price1 | $66.67 | =8/12% | |
| c. What is the dividend yield, the capital gains yield, and the expected rate of return of the stock? | |||
| Dividend yield | 12% | =8/66.67 | |
| Capital gains yield | 0% | =(66.67-66.67)/66.67 | |
| Rate of return | 12% | =12% - 0% | |
PE ratio
| Price Earning (P/E) Ratio | |||
| Favorita Candy’s stock is expected to earn $2.40 per share this year. Its P/E ratio is 18. What is the stock price? | |||
| Data. | |||
| EPS | $2.40 | =2.4 | |
| P/E Ratio | 18 | =18 | |
| Price | $43.20 | =C5*C6 | |
Growth & Multiples
| Return on Equity & P/E, P/B ratio | |||||
| Start-Up Industries is a new firm that has raised $200 million by selling shares of stock. Management plans to earn a 24% rate of return on equity, which is more than the 15% rate of return available on comparable-risk investments. 40% of all earnings will be reinvested in the firm. | |||||
| a. when ROE 24% | b. when ROE 12% | ||||
| Book value ($, million) | 200 | =200 | Book value ($, million) | 200 | =200 |
| Return on equity (%) | 24% | =24% | Return on equity (%) | 12% | =12% |
| Earnings ($, million) | 48 | =200*24% | Earnings ($, million) | 24 | =200*12% |
| Plowback(Retention) ratio (%) | 40% | =40% | Plowback(Retention) ratio (%) | 40% | =40% |
| Dividends ($, million) | 29 | =48*(1-40%) | Dividends ($, million) | 14 | =24*(1-40%) |
| g (%) | 10% | =24%*40% | g (%) | 5% | =12%*40% |
| Discount rate (%) | 15% | =15% | Discount rate (%) | 15% | =15% |
| Market value ($, million) | 533 | =C10/(C12-C11) | Market value ($, million) | 141 | =F10/(F12-F11) |
| P/E ratio | 11.11 | =C13/C8 | P/E ratio | 5.88 | =F13/F8 |
| P/B ratio | 2.67 | =C13/C6 | P/B ratio | 0.71 | =F13/F6 |
Permanent value
| Stock Price with Permanent Value | ||||
| Q. Tattletale News Corp. has been growing at a rate of 20% per year, and you expect this growth rate in earnings and | ||||
| dividends to continue for another 3 years. If the last dividend paid was $2, the discount rate is 15% and | ||||
| the steady growth rate after 3 years is 4%, what should the stock price be today? | ||||
| Data. | ||||
| Growth rate for Years 1-3 | 20% | |||
| Last dividend paid | $2 | |||
| Discount rate | 15% | |||
| Growth rate after 3 years | 4% | |||
| Stock price PV0 | DIV1 | DIV2 | DIV3 | |
| 28.02 | 2.40 | 2.88 | 3.46 | =D15*(1+C9) |
| 28.02 | =C10*(1+C9) | =C15*(1+C9) | 32.67 | PV3 |
| Short-cut of PV0 | =(E15*(1+C12))/(C11-C12) | |||
| =C15/(1+C11)^1+D15/(1+C11)^2+(E15+E16)/(1+C11)^3 | =D15*(1+C9) | |||
| =NPV(C11,C15,D15,E15+E16) |
Holding Return
| Expected Holding Period Rate of Return | |||
| Better Mousetraps has come out with an improved product, and the world is beating a path to its door. As a result, the firm projects growth of 20% per year for 4 years. By then, other firms will have copycat technology, competition will drive down profit margins, and the sustainable growth rate will fall to 5%. The most recent annual dividend was DIV0 = $1 per share. | |||
| Input variables: | |||
| Initial growth rate | 20% | ||
| Initial growth rate years | 4 | ||
| Sustainable growth rate | 5% | ||
| DIV0 | $1.00 | ||
| Discount Rate | 10% | ||
| a. What are the expected values of DIV1, DIV2, DIV3, and DIV4? | |||
| DIV1 | $1.2000 | =C9*(1+C6) | |
| DIV2 | $1.4400 | =C13*(1+$C$6) | |
| DIV3 | $1.7280 | =C14*(1+$C$6) | |
| DIV4 | $2.0736 | =C15*(1+$C$6) | |
| b. If the discount rate is 10%, what is the expected stock price 4 years from now? | |||
| Stock Price in 4 yrs (HV4) | $43.5456 | =C16*(1+C8)/(C10-C8) | |
| c. What is the stock price today? | |||
| Stock price today | $34.738 | =NPV(C10,C13,C14,C15,C16+C19) | |
| d. Find the dividend yield, DIV1 / P0. | |||
| Dividend yield year 1 | 3.45% | =1.2/C22 | |
| e. What will next year's stock price, P1, be? | |||
| Expected Stock price at year 1 | $37.012 | =NPV(C10,C14,C15,C16+C19) | |
| f. What is the expected rate of return to an investor who buys the stock now and sells it in 1 year? | |||
| Expected Return at year 1 | 10.00% | =(37.012-34.738+1.2)/34.738 | |
PVGO
| Present Value of Growth Opportunities (PVGO) | ||
| Web Cites Research projects a rate of return of 20% on new projects. Management plans to pay back 70% of all earnings into the firm. Earnings this year will be $3 per share, and investors expect a 12% rate of return on stocks facing the same risks as Web Cites. | ||
| Input variables: | ||
| Return on Equity | 20% | |
| Payback ratio | 70% | |
| Earnings per share | $3 | |
| Required return | 12% | |
| a. What is the sustainable growth rate? | ||
| Sustainable growth rate | 6.00% | =C6*(1-C7) |
| b. What is the stock price? | ||
| Stock price | $35.00 | =3*70%/(12%-6%) |
| c. What is the P/E ratio? | ||
| P/E ratio | 11.67 | =C15/C8 |
| d. What would the price and P/E ratio be if the firm paid out all earnings as dividends? | ||
| Stock price | $25.00 | =C8/C9 |
| P/E ratio | 8.33 | =C21/C8 |
| e. What is the present value of growth opportunities (PVGO)? | ||
| PVGO | $10.00 | =C15-C21 |
