Spreadsheet Exercises 10.1 The NIO The Richter Company, a technology firm, has been crowing rapidly. After examining the company's operations very carefully, analysts at Meril Link estimate that dividends and earnings will grow at 99 percent a year for the next eight years, followed by 16 percent growth for another six years. After 14 years, the expected growth rate is 5 percent. The risk-free rate is 5.5 percent, and the expected return on the market is 10.5 percent. The beta for Richter is 1.1. and its current dividend is $1.10. As major stock- holders, the Richter family has asked you to estimate the stock's intrinsic value. 2. Calculate the dollar dividend for the first 14 years in cells B5 through B18 and the present value of these amounts in cells C5 through CI8. Be sure to allow for the change in growth rates in Year 9. 3. In cell G19, calculate the price of the stock at the beginning of Year 15 using the then-constant- growth rate of 5 percent. 4. In cell G20, discount the price found in (3) back to today using the proper number of periods for discounting 5. Sum the present value of the dividends in cell C21. The present value of the price found in (4) should be placed in C22 6. In cell C23, add the values found in (5). 1. Calculate the required return in cell H2 using the Curr Divid $1.1 Year First gr Rate 0.22 8 yrs Dividend Second gr Rate 0.16 6 yrs PV of Div Normal gr 0.05 RF Exp Mk RH Bota 1.1 k 0.11 Price at beginning of Year 15 PV of Price today-Sum of of dividends for first 14 years + PV of price at beginning of Year 15 Sum of PV of dividends and PV of price