Create a worksheet as the one below (Figure 3.9.1). 1. You invested $1,000 at the beginning of the first year (B2). The market value of the investment becomes $1,020 at the end of the first year. Enter =B31.02 in B4. 2. The dividend is equal to 5% of the investment at the beginning of the year. Enter B30.05 in B5. 3. Cash investment at the beginning of the second year is equal to the cash investment at the beginning of the first year plus the dividend reinvested. Enter B2+B5 in C2. 4. Market value at the beginning of the second year is equal to the market value at end of the first year plus the dividend from the previous year. If you sell the investment, your return will be equal to the current market value plus the dividend from the previous year. Enter B4+B5 in C3. 5. Market value at the end of year is equal to the market value at the beginning of the year times 1.02. Enter C31.02 in C4, or copy the formula from B4. 6. Dividend at end of the year is equal to the cash investment at the beginning of the year. Enter C20.05 in C5, or copy the formula from B5. 7. Copy formulas in C2:C5 to columns D through G. 8. The investment may be sold at the beginning of the sixth year for $1,391.08 (market value). Figure 3.9.1 EXERCISE 3.9 1. Compute the Internal Rate of Return of the above investment. (Hint: Use the Market value at beginning of the years in B2 and in G2. (Value: 1000,0,0.0,1276.28 ) 2. In the above case in Figure 3.9.1, if you keep the investment for 10 years, what is the IRR? Answers: EXERCISE 3.9.1: 7.00% EXERCISE 3.9.2: 7.00%