Question

Adam’s fellow graduate, Jenna, has been trying to decide how much of her new salary she could save for retirement. Jenna is considering putting $3,000 of her annual savings in a stock fund. She just turned 22 and has a long way to go until retirement at age 60, and she considers this risk level reasonable. The fund she is looking at has earned an average of 6.10% over the past 15 years and could be expected to continue earning this amount, on average. While she has no current retirement savings, eight years ago Jenna’s grandparents gave her a new 30-year U.S. Treasury bond with a $20,000 face value with 4.25% semiannual coupons. She plans on withdrawing until she is 94.

Jenna expects her salary to grow regularly. While there are no guarantees, she believes an increase of 2.50% a year is reasonable. She plans to save $3,000 the first year, and then increase the amount she saves by the amount of her annual salary increase. Unfortunately, prices will also grow due to inflation. Suppose Jenna assumes there will be 1.90% inflation every year. In retirement, she will need to increase her withdrawals each year to keep up with inflation.

a.     How much money will Jenna have at her retirement?

b.     How much can she withdraw at the end of the first year of her retirement in today’s dollars? (Hint: Value Jenna’s Retirement Fund at Retirement Age = FV of Treasury Bond + FV of Jenna’s Savings)

Should Jenna sell her Treasury bond and invest the proceeds in the stock fund? Give at least one reason for and against this plan.

If you could tell me how to use excel to solve for these, I'd greatly appreciate it!

Answer 1

30-year U.S. Treasury bond
Face value	20,000
Interest	4.25%
Interest (Semi annual)	2.125%
Time till retairement age (60-22)	38
Time when bond was received	8
Total time	46
FV of TB 20,000*(1.02125^76)	98,871
FV of annual savings
Annual savings	3,000
Period	38
growth	2.50%
Return	6.10%
FV	577,711

A) Amount Jenna will have at the end of retirement 98,871+577,711 = 676,582

B) Amount to be withdrawn at the time of retirement 3,000*((1.019)^37) = 6,020

For and against sale of bond

For

Jena is still young and therefore has a higher risk appetite, therefore she can sell and invest the proceeds of bond in equity.

Against

She is currently investing only in equity, therefore if she sells the bond, her portfolio will not be diversified in terms of class of asset