Question: Please solve using excel please and show formula used in excel please Problem Three (15 marks) You are an analyst in charge of valuing common

Please solve using excel please and show formula used in excel please

Please solve using excel please and show formula
Problem Three (15 marks) You are an analyst in charge of valuing common stocks. You have been asked to value two stocks. The first stock NEW Inc. just paid a dividend of $8.00. The dividend is expected to increase by 80%, 50%, 30%, 15%, 10% and 5% per year respectively in the next six years. Thereafter the dividend will increase by 3% per year in perpetuity. a) Calculate NEW's expected dividend for t = 1, 2, 3, 4, 5, 6 and 7. The required rate of return for New Stock is 20% compounded annually, b) What is NEW's stock price? The second stock is OLD Inc. OLD will pay its first dividend of $22 in 3 years. The dividend will increase by 20% per year for the following 4 years after its first dividend payment. Thereafter the dividend will increase by 4% per year in perpetuity. c) Calculate OLD's expected dividend for t = 1, 2, 3, 4, 5, 6, 7 and 8. The required rate of return for Old Stock is 12% compounded annually, d) What is OLD's stock price? Now assume that both stocks have a required rate of return of 30% per year for the first 5 years, 25% per year for the following 4 years, and thereafter the required rate of return will be 15%. e) What is NEW's stock price? f) What is OLD's stock price? (Hint: you may need to forecast more dividends than you did in parts a, and c.) Note you cannot use the NPV function to immediately value the stocks at time 0, as the required rate of return changes during the forecast period Note: All calculations should be rounded to the nearest cent. That is 2 decimal places

Step by Step Solution

There are 3 Steps involved in it

1 Expert Approved Answer
Step: 1 Unlock blur-text-image
Question Has Been Solved by an Expert!

Get step-by-step solutions from verified subject matter experts

Step: 2 Unlock
Step: 3 Unlock

Students Have Also Explored These Related Finance Questions!