Question

There are some government bonds that John and his wife inherited from her grandmother. The bonds (65 in total) mature in 10 years; have a face value of $1000 each and a coupon rate of 8% paid semi-annually.

Given the low yields in the current bond market environment, our bonds must now be traded at a premium. The yield on 10-year government bonds is currently at about 2.5%. John and his wife can either sell the bonds now and invest the proceeds immediately into their retirement fund or collect the coupon payments over the next 10 years and then invest the face value of the bonds at maturity into their retirement fund.

Q 1) What is your estimate of the cash they can have if they sell the bonds at the current market conditions? Please use (display + name) the excel function/ formula used for each yellow cell.

Q1. proceeds from bond sales
rate
#periods
coupons
Bond price
total proceeds from sale

Q 2) Is it better for them to sell the bonds now and invest the proceeds into their retirement fund immediately or should we wait until they mature in 10 years to collect the coupon payments and then put the money collected from the reimbursement of the face value into their retirement savings plan? Please use (display + name) the excel function/ formula used for each yellow cell.

Note: If you keep the bonds until maturity, assume that the coupons received are reinvested once a year in the retirement fund at the same annual rate of 8.5% compounded annually.

Q 2. sell bonds now or wait to cash face value at maturity

there are many ways to answer this question. Use the space here to show your calculations

Note: If you keep the bonds until maturity, assume that the coupons received are reinvested once a year in the retirement fund at the same annual rate of 8.5% compounded annually.

Q 3) Do you suggest a better way of using the proceeds from selling these bonds? Please use (display + name) the excel function/ formula used for each yellow cell.

Q3. Other suggestions to improve plan
there are many ways to answer this question. Use the space here to show your calculations

Answer 1

Answer 1) The value of Bond :

or Use of excel function , PV(rate,nper,pmt,fv)

Rate	2.50%
Period	10years	10*2= 20
Coupon	8%	80
Face Value	$1000
Bond Price	$1,857.40	PV(2.5%,20,-80,-1000)
Number of bond	65
Total proceeds from sale	$1,20,731.26	65*1857.40

Answer b)

option 1
Total Proceeds Now	$1,20,731.26
Maturity Value @ 8.5% for 10 years	$2,72,971.37	120731.26*(1+8.5%)^10
Option 2
Annual Proceeds	80
Maturity value from bond	$65,000.00	65*1000
Future Value of coupon proceeds	$1,186.81	FV(8.5%,10,-80)
Total proceeds	$66,186.81	65000+1186.81

Answer 3)

Total proceeds in Option 1 is better , i.e. selling the bond today and invest in retirement fund at 8.5% .

Answer b)