Question

*PLEASE SHOW ALL SOLUTIONS USING MICROSOFT EXCEL FORMULAS, thank you!*

On December 31^st, 2014 you decided to buy a 30 year Government of Canada bond. The bond had a face value of $100,000. The coupon rate on the bond was 6%. Coupons were paid semi-annually. On December 31^st, 2014 the yield to maturity on Government of Canada bonds was 5% per year, compounded semi-annually. (The term structure of interest rates was flat.)

After holding the bond for 4 years you decided to sell the bond on December 31^st, 2018. Prior to selling the bond you received the December 31^st, 2018 coupon payment. On December 31^st, 2018 the yield to maturity on Government of Canada bonds was 4% per year, compounded semi-annually. (The term structure of interest rates was flat.)

a) How much did you pay for the bond on December 31^st, 2014?

b) How much did you sell the bond for on December 31st, 2018?

c) What was the effective annual rate of return that you earned on your investment during the 4 years?

d) Suppose you postponed selling the bond until April 30^th, 2019. On April 30^th, 2019 the yield to maturity on Government of Canada bonds is 3.5% per year, compounded semi-annually. (The term structure of interest rates is flat.) What is the full price, the clean price, and the accrued interest on April 30^th, 2019? Please note: the settlement date falls between coupon payment dates. For simplicity, assume that there are 30 days in a month and 360 days in a year.

Answer 1

	a)	Amount paid for the bond on December 31st, 2014
		Face Value of the Bond	$100,000
	Pmt	Semi annual Coupon payment	$3,000	(100000*(0.06/2)
	Rate	Semi annual yield to maturity=(5/2)%	2.5%
	Nper	Number of semi annual period	60	(30*2)
	Fv	Payment at maturity at end of 3o years	$100,000
		Amount paid on December 31, 2014=Present Value of cash flows(Coupon payment + payment at maturity)discount at yield to maturity
	PV	Amount paid on December 31, 2014	$115,454.33	(Using PV function of excel with Rate=2.5%, Nper=60, Pmt=-3000,Fv=-100000)
	b)	Amount for which the bond was sold on December 31st, 2018
		Face Value of the Bond	$100,000
	Pmt	Semi annual Coupon payment	$3,000	(100000*(0.06/2)
	Rate	Semi annual yield to maturity=(4/2)%	2%
	Nper	Number of semi annual period	52	(30-4)*2
	Fv	Payment at maturity at end of 26 years	$100,000
		Amount at which bond was sold=Present Value of cash flows(Coupon payment + payment at maturity)discount at yield to maturity
	PV	Amount for which bond was sold	$132,144.95	(Using PV function of excel with Rate=2%, Nper=52, Pmt=-3000,Fv=-100000)
	c)	Effective annual rate of return:
		CASH FLOW:
			Semiannual period	Cash Flow
			0	($115,454.33)
			1	$3,000
			2	$3,000
			3	$3,000
			4	$3,000
			5	$3,000
			6	$3,000
			7	$3,000
			8	$135,144.95	(3000+132144.95)
		Internal Rate of Return	4.16%	(Using excel IRR function over the cash flows)
		Semi annual return	4.159%
		Semi annual return	0.04159
		Effective annual Return=R
		1+R=(1+0.04159)^2=	1.084899734
		Effective annual Return=R	0.084899734
		Effective annual Return in Percentage	8.49%
	d)	Clean Price on April30, 2019
		Face Value of the Bond	$100,000
	Pmt	Semi annual Coupon payment	$3,000	(100000*(0.06/2)
	Rate	Semi annual yield to maturity=(3.5/2)%	1.75%
	Nper	Number of semi annual period	52
	Fv	Payment at maturity at end of 26 years	$100,000
	PV	Market price including accrued interest	$142,449.65	(Using PV function of excel with Rate=1.75%, Nper=52, Pmt=-3000,Fv=-100000)
		Accrued Interest =6000*(120/360)=	$2,000.00
		Clean Price of the Bond	$140,449.65	(142449.65-2000)