Reference no: EM133067953

Excel problem

Your sister has just won $300,000 (tax-free) in the state lottery. She's decided to quit her job and devote herself to writing novels for the next ten years, using her lottery winnings to support herself. She figures that she will need $30,000 of income at the start of the coming year; $31,000 at the start of next year; $32,000 for the third year; and so on. In order to meet these expenses, she plans to invest her lottery winnings all at once, in bonds. If she finds that she has extra cash in any year (including the first), she plans to place it in her savings account, which pays 3 percent annual interest, but she does not want to purchase any additional bonds in the future.

At the start of the coming year, bonds with one-, three-, five-, and 10-year maturities will become available on the market. If a bond matures in k years, it pays $100 at the end of each of k years, as well $1,000 at the end of the kth year. Currently, one-year bond sells for $1,075, three-year bonds for $1,100, five-year bonds for $1,200, and 10-year bonds for $1,300. Your sister wants to make sure that the income from her investments will provide for her living expenses year by year. She has asked you to advise her on how many bonds to purchase and has offered to give you any funds left over at the end of the 10-year period.

a) Assume that you wish to maximize the amount of money available to you at the end of 10 years. How many of each type of bond should your sister purchase? (Assume that these bonds can be purchased in fractional amounts, as part of an investment pool.)

b) How much money will be available to you at the end of 10-years?

c) Suppose you investigate other savings bank and find that interest rates higher than 3 percent are available. What is the minimum interest rate that would alter which of the four types of bonds your sister should buy?