Question 1 D/10 Video Submit Excel Online Structured Activity: Nonconstant growth Computech Corporation is expanding rapidly and currently needs to retain all of its earnings; hence, it does not pay dividends. However, investors expect Computech to begin paying dividends, beginning with a dividend of $1.00 coming 3 years from today. The dividend should grow rapidly - at a rate of 33% per year - during Years 4 and 5; but after Year 5, growth should be a constant 4% per year. The data has been collected in the Microsoft Excel Online file below. Open the spreadsheet and perform the required analysis to answer the question below. X Open spreadsheet If the required return on Computech is 16%, what is the value of the stock today? Round your answer to the nearest cent. Do not round your intermediate calculations. $ Check My Work Reset Problem X Excel File Home Insert Formulas Data Review View Help Tell me what you want to do X Cut Arial 10 AA ab Wrap Text Currency LCopy Paste BI U Dab A ESE E Merge & Center $ % ! Conditional Format Insert Delete Format Painter Formatting was Table Styles Undo Clipboard Font Alignment Number Tables Cells C17 fx -NPV(B6,011:H13) A B D E F G H 1 Nonconstant growth 2 3 Year 3 Dividend, D $1.00 4 Supernormal growth rate, gs 33.00% 5 Normal growth rate, gn 4.00% 6 Required return, is 16.00% 7 33.00% 33.00% 4.00% 8 0 1 2 3 4 5 6 9 Dividends 0 0 0 $1.00 $3.03 $3.36 $3.40 10 PS $28.34 11 Cash flows to common stockholders 0 0 $1.00 $3.03 $31.70 12 13 PV of cash flows to common stockholders $0.00 $0.00 $0.64 $1.67 $15.09 14 Stock Price, P. $17.41 15 16 Alternatively, using Excel NPV function: 17 Stock Price, Po S21.46 18 19 20 Formulas 21 33.00% 33.00% 4.00% 22 0 1 2 3 4 4 5 6 23 Dividends 0 0 $1.0000 =F9/F7 =G9+G7 =H9+H7 2 24 PS =19/(B6-B5) 25 Cash flows to common stockholders 0 0 =F9 =G9 =H9+H10 26 27 PV of cash flows to common stockholders =D11/(1+B6) =D13/(1+SBS6YE8 =F11/(1+$B$6Y"F8 =G11/(1+$B$6Y"G8 =H11/(1+$B$6 "H8 28 Stock Price, Po =D13+E13+F13+G13+H13 29 30 Alternatively, using Excel NPV function: 31 Stock Price, P. =NPV(B6,D11:H13) 32 33 34 25