Question

You invest $1,000 in a large company stock and $1,000 in a corporate bond. If you earn 10.0 percent on the stock and 6.0 percent on the bond and hold each security for 10 years, what are the terminal values for each investment? If you continue to hold each security and earn the same returns for 20 years, how much more will the stock generate than the bond over the entire time period? (When you invest for retirement or to finance a child’s college education, you should think about the answer to this problem.)

Please show how to solve in Excel, step by step please.

Answer 1

Please refer to below spreadsheet for calculation and answer. Cell reference also provided.

B C D Bond $1,000.00 Stock $1,000.00 10% 101 $2,593.74 Amount invested Rate of return Years Value at end of Year 10 6% 10 $1,790.85 Bond $1,000.00 Amount invested Rate of return Years Value at end of Year 20 Stock $1,000.00 10% 20 $6,727.50 6% 20 $3,207.14 Excess amount earned by Stock $3,520.36

Cell reference -

ДА c Nm 7 Amount invested Rate of return Years Value at end of Year 10 Stock 1000 0.1 10 =FV(C4,C5,0,-C3,0) Bond 1000 0.06 10 =FV(D4,D5,0,-D3,0) 8 9 b. Stock Bond 10 11 1000 0.1 1000 0.06 Amount invested Rate of return Years Value at end of Year 20 12 20 =FV(C11,212,0,-C10,0) =FV(D11,D12,0,-D10,0) Excess amount earned by Stock =C13-D13

Hope it will help, please do comment if you need any further explanation. Your feedback would be highly appreciated.