I need help with this problem. The previous answer was not helpful. This exercise is from Problem Set #1 - FINC-UB.0002

Excel exercises

These problems are to be completed in Excel. Please print out and hand in a copy of the Excel

spreadsheet. (Note that the problems above can also be done in Excel. However, the following

problems MUST be done in Excel.)

5. The table below gives the dividends paid and price of a stock at the end of each of the last

5 years, e.g., on December 31, 2007 the stock paid a dividend of $2 and the price after

this payment was $70. Note that the price is ex-dividend, i.e., it is the price after the

payment of the dividend for that year. If you buy the stock for $70 at the end of 2007 you

do NOT receive the dividend of $2 paid at the end of that year.

2006 2007 2008 2009 2010

Price $100 $70$70$90$100

Div. $2$3 $4 $5

a. What are the annual HPRs on the stock for each of the 4 years, i.e., the return if

you bought the stock at the beginning of the year and sold it at the end of the year

after receiving the dividend?

b. What is the arithmetic average of the 4 annual returns calculated in part (a)

above?

c. What is the geometric average of the 4 annual returns calculated in part (a) above?

(You can use the GEOMEAN function in Excel, but be careful.)

d. What is the IRR on an investment in this stock if you bought it for $100 at the end

of 2006 and sold it for $100 at the end of 2010 (after receiving the $5 dividend)?

(Use the IRR function.)