I need help with this assignment using excel sheet. I will provide information sheet with some examples and the last picture will be the assignment.
re Q o Calibri 11 B/USA. A B C D 6. Pioneer preferred stock pays a $3.60 annual dividend. E F G H I J K L M N a. The stock is selling for $33 per share in the market, what is the expected rate of return on the stock? Par value Dividend rate 3.60 33.00 r bar 10.91% 10 b. If an investor's required rate of return is 10%%, what is the value of the stock for that investor? 11 Par value 12 Dividend rate 13 3.60 14 0.100 15 Vps = $36.00 16 17 c. Should the investor acquire the stock? Why? 18 Yes. Because the market price ($33) is lower than the budget ($36) (Le. the price that the invester is willing to pay). 19 20 7. Hershey's preferred stock pays an annual dividend of $3.80. 22 a. The stock is selling at $65 on the market, what is the expected rate of return on the stock? 23 Par value 24 Dividend rate 25 3.80 Valuing PS Expected Rate of Return PS Investment Decisions PS Valuing CS Expected Rate of Return CS Investment Decisions CS( ) C * Office Editing for Docs, Sheets & Slides | chrome-extension://bpmcplapdmajSippchkicefolgmkfalc/viewwapp.html X Demo Exercise 6.xisx?globalNavigation=false.xisx File Edit Insert Format Help 11 OFUSA. 11. The common stock of Zaldi Co. Is selling for $32 84 per share. The stock recently paid dividends of $2 94 per share and his a projected constant growth rate of 9.5%% you D E F G H K M 1 1. The common stock of Zaldi Co. Is selling for 132.84 per share. The stock recently paid dividends of 12:94 per share and has a projected constant growth rate of 25% If you purchase the stock at the market price, what is the expected rate of return? DO 2:94 Dividend paid 0.095 annual growth 1.22 - D0 * (1 . E) 12 840 r bar - 19.30% - (D1/ Ppi) . E =[132/ 32.84) + 0.093 13. We know a common stock just paid a $1 00 dividend, has a price of 127 and an rapisted growth rate of 3. What is the stock'i expected rate of return! DO 10 01060 11 3.15 12 77.060 13 14 15 16 17 19 Valuing PS Expected Rate of Return PS Investment Decisions PS Valuing CS Expected Rite of Retum CS Investment Docaven CS O DELLValuing Common Stocks 8. XYZ stock recently paid a $5.00 dividend. The dividend is expected to grow at 10% per year indefinitely. What would we be willing to pay if our required return on XYZ stock is 15%? 9. Crosby Corporation common stock paid $2.32 in dividends last year and is expected to grow indefinitely at an annual 7% rate. What is the value of the stock if you require an 11% return? 10. Lojack Airways common stock just paid a dividend of $2.50. Dividends are expected to grow at a rate of 8% indefinitely. If investors require a rate of return on the stock of 15%, what is the value of Lojack's common stock today? Expected Rate of Return of Common Stockholders 11. The common stock of Zaldi Co. is selling for $32 84 per share. The stock recently paid dividends of 52 94 per share and has a projected constant growth rate of 9.5%. If you purchase the stock at the market price. what is the expected rate of return? 12 We know a common stock just paid a $3.00 dividend, has a price of 527 and an expected growth rate of 5%. What is the stock's expected rate of return? Common Stock - Investment Decisions of NCP paid $1.32 per share in dividends last year. Dividends are expected.XISH File Edit Insert Format Help ra Qa catbri BIUSA. P. . A B C D E F G H J K M O 18 Yes. Because the market price ($23 50) is lower than the budget ($57.02) (Le. the price that the invester is willing to pay]. 19 20 14. Susan's Crane Co. just paid a dividend of $2.25 on its common stock. The company's dividends are expected to grow at a constant rate of 2.5% 22 a. You observe a market price of $21.00 for the stock. What is the expected rate of return of the stock? 23 24 0.025 25 DI 2.31 26 21.000 27 28 29 b. If the required rate of return on this stock is 14.5%, compute the current value per share of Susan's stock. 30 2.25 31 0.03 32 2.31 33 0.145 34 $19.22 35 36 C. Should you purchase this stock? Why? No. Because the market price ($21) is higher than the budget ($19 22] (Le. the price that the invester is willing to pay). 37 38 39 40 41 Investment Decisions CS Valuing PS Expected Rate of Retum PS |Investment Decisions PS Valuing CS Expected Rate of Retum CS O DELLW being provided in the Demo Exercise. Submit the Homework spreadsheet using thisi'enome \"RHIM4322- Lost_Nome-Homework 6 \" 1. What is the value of a preferred stock when the dividend rate is 14% on a $100 par value? The appropriate required rate of return for a stock of this risk level is 12%. 2. Positive Tronics Industries preferred stock pays a dividend of $4.00 per share. It presently sells for $85 per share. What do investors expect as a rate of return on this stock? 3. You own Somner Resources preferred stock, which pays annual dividends of $3 .40 per share. a. If Sonmer's preferred stock currently sells for $40 per share, what is your expected rate of return? b. If you require an 8% return, what is the value of the preferred stock to you? c. Should you sell or buy more Sonrner Resource preferred stock? Why? 4. Header Motor, Inc., common stock paid a $3.50 dividend last year. At a constant growth rate of 5%, what is the value of the common stock if the investors require a 20% rate of return? 5. Creamy Crisp common stock is currently selling for $75 .00. It just PE a dividend of $3.65 and dividends are expected to grow at a rate of 6% indefinitely. What is the expected rate of return on Creamy's stock? 6. Made-It, Inc, common stock currently sells for $22.50 per share. The company's executives m a $2.00 dividend and anticipate a constant growth rate of 10%. a. What is your expected return if you buy the common stock at the current market price of $22.50 per share? b. If you require a 17% return, what is the value of the cornmon stock to you? c. Should you purchase the stock? Why? B /US A BB . A B C D E F G H to you as the investor? J 1. Xerox preferred stock pays an 8.25% dividend on a $50 par value, Suppose our required rate of return on Xerox preferred stock Is 9.5%. What is the value of this preferred stock K L N 2 Par value 50 Dividend rate 0.0625 4.125 - Par value * Dividend rate - 50 * 0.0823 -4.125 5 D.095 $43.42 - D/r -4.125 / in nos B 2. National Pickle Company preferred stock pays a dividend of 2.6%% of its $50 par value. If investors' required rate of return on this stock is 14%, what is the value per share?' 9 Par value 30 10 Dividend rate 0.024 11 D 1.30 12 0.140 13 $9.29 14 15 3. The preferred stock of Arrlo pays an 11% dividend of its $25 par value. What is the value of the stock if your required return is 9%? 16 Fair value 25 17 Dividend rate 0.11 18 2.75 19 D.090 Valuing P5 Expocited Rate of Rotum PS Investment Decisions PS Valuing CS Expected Rate of Return C5 Investment Decisions C5 Oomg extension:/bpmcpldadmajfigpchkicefogmukfalorviews/app.html X Demo Exercise 6.xisx?globalNavigation=false.xlsx File Edit Insert Format Help Calibri 4. If we know the current market price for a preferred stock is $40, and the dividend is $4.125 per share, what is the expected return? A B C D E F G H J K 1 L M N 4. If we know the current market price for a preferred stock is $40, and the dividend is $4.125 per share, what is the expected return? Par value Dividend rate 4.125 No need to calculate D 40.00 r bar - 10.31% = D/ Pps - 4.125 / 40 5. Soledad Company preferred stock has a market price of $20. If it has a yearly dividend of $1.50 per share, what is your expected rate of return if you purchase the stock at its market price? Par value 10 Dividend rate 11 1.50 12 Pps 20.00 13 bar 750% 14 15 16 17 18 19 20 Investment Decisions PS Vwing CS Expected Rule of Retum CS Investment Decisions C5 Valuing PS Expected Rate of Retum PSC * Office Editing for Docs, Sheets & Slides | chrome-extension://borncoldpdmajfippchkicefolgmkfalc/ views/app html X Demo Exercise 6.xisx?globalNavigation=false.xlsx File Edit Inert Format Help a a calibri 1 . B/ USA. P. A B C D E F G H B. XYZ stock recently pald a $5,00 dividend. The dividend is expected to grow at 10% per year indefinitely. What would we be willing to pay if our required return on MY2 stock is L M N 15%7 2 5.00 Dividend pald 0.10 annual growth 5.50 -D0 . (1 + E) - 5.00 * (1 # 0.10] 0.150 6 $110.00 - D1 / [r - ) - 5.50 / 10.13 - ". Crosby Corporation common stock paid $2.32 in dividends last year and is expected to grow Indefinitely at an annual 7% rate. What is the value of the stock if you require an 11% return? 9 DO 10 0.07 11 D1 2.48 12 0.110 13 163.06 14 10. Lojack Alrwary's common stock just pald a dividend of $2.50. Dividends are expected to grow at a rate of 8% Indefinitely. If investors require a rate of return on the Mock of 15 1515, what is the value of Lojack's common stock today? 15 240 17 0.08 18 DI 2.70 0.150 Valuing PS Expected Rate of Return PS |Investment Decisions PS Expected Fate of Return CS|Investment Decisions CS O13. The common stock of NCP paid $1 32 per share in dividends last year, Dividends are expected to grow at an SM annual rate for an indefinite number of yours. B C D E F H 13. The common stock of NCP paid $1 32 per share In dividends Last year. Dividends are expected to grow at an 8%% annual rate for an Indefinite IN P a. I' NCP's current market price is $23.50 per share, what Is the stock's expected rate of return ? 0.080 23 500 bar 14.07% 10 b. your required rate of return is 10.3%, what is the value of the stock for you 11 12 13 01 141 0.105 15 457.02 16 17 C. Should the investor acquire the stock? Why? 18 THE. Because the market price (123.50) Is lower than the budget ($57.02) (Le. the price that the invester is willing to pry). 19 20 14. Susan's Crane Co. just paid a dividend of $2.25 on its common stock. The company's dividend are expected to grow at a contant rate of 2.3% 22 a. You observe a market price of $21.00 for the stock. What h the expected rate of return of the wock? 23 24 0.023 231 3 1.ood Investment Dignpra CS Valuing PS Expected Rate of Retum PS Investment Decisions PS Valuing CS Expected Rate of Retum CS DELLInformation Sheet Valuing Preferred Stocks 1. Xerox preferred stock pays an 8.25% dividend on a $50 par value. Suppose our required rate of investor? retum on Xerox preferred stock is 9.5%. What is the value of this preferred stock to you as the 2. National Pickle Company preferred stock pays a dividend of 2.6% of its $50 par value. If investors' required rate of return on this stock is 14%, what is the value per share? 3. The preferred stock of Armlo pays an 11% dividend of its $25 par value. What is the value of the stock if your required return is 9%? Expected Rate of Return of Preferred Stockholders 4. If we know the current market price for a preferred stock is $40, and the dividend is $4.125 per share, what is the expected retum? 5. Soledad Company preferred stock has a market price of $20. If it has a yearly dividend of $1.50 per share, what is your expected rate of return if you purchase the stock at its market price? Preferred Stock - Investment Decisions 6. Pioneer preferred stock pays a $3.60 annual dividend. a. The stock is selling for $33 per share in the market, what is the expected rate of retum on the stock? b. If an investor's required rate of return is 10%, what is the value of the stock for that investor? c. Should the investor acquire the stock? Why? 7. Hershey's preferred stock pays an annual dividend of $3.80. The stock is selling at $65 on the market, what is the expected rate of return on the stock? b. If an investor's required rate of return is 7%, what is the value of the stock to that investor? Does this stock seem to be a desirable investment? Why