Question

Consider a Merck 5.75% coupon bond, which has semiannual payments and matures on today’s date in 2036. The bond’s current price is 120.43.

a.  List the bond’s expected coupon and principal payments, and calculate the bond’s yield to maturity (YTM) using IRR in cell A1. Do not use the Excel “yield”-related functions, except if you wish to check your numbers.

b.  Calculate the Macaulay duration for the bond (in A2). Do not use the Excel “duration”-related functions, except if you wish to check your numbers.

c.  Assume that the investor holds the bond to maturity. In the designated cells, compute the horizon return for the bond, under the following reinvestment rates which are observed immediately after purchase.

                                                                                                                                                                                                                             

                                                                              Reinvestment Rate                          

                                                                              3%                   7%

Horizon return                                                       A3                   A4

                                                                                                                                                                                                                             

d. Now assume that the investor holds the bond to Macaulay duration. In the designated cells, compute horizon return for the bond, under the following reinvestment rates.

                                                                                                                                                                                                                             

                                                                              Reinvestment Rate                          

                                                                              3%                   7%

Horizon return                                                       A5                   A6

                                                                                                                                                                                                                             

e.  In no more than one paragraph inside a text box, describe what is happening to the investor’s horizon return in part (c). Be specific about why the numbers are as they are.

f.  In a second paragraph in the same text box, describe what is happening to the horizon returns in part (d). Be specific about why the numbers are as they are.

Answer 1

Coupon	5.75%
Price	120.43
period	32

A1

Cash flowsA)	-120.43	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	105.75
PVf @5		1/(1.05)	1/(1.05)^2	1/(1.05)^3	1/(1.05)^4	1/(1.05)^5	1/(1.05)^6	1/(1.05)^7	1/(1.05)^8	1/(1.05)^9	1/(1.05)^10	1/(1.05)^11	1/(1.05)^12	1/(1.05)^13	1/(1.05)^14	1/(1.05)^15	1/(1.05)^16	1/(1.05)^17	1/(1.05)^18	1/(1.05)^19	1/(1.05)^20	1/(1.05)^21	1/(1.05)^22	1/(1.05)^23	1/(1.05)^24	1/(1.05)^25	1/(1.05)^26	1/(1.05)^27	1/(1.05)^28	1/(1.05)^29	1/(1.05)^30	1/(1.05)^31	1/(1.05)^32
PVf @5		0.952381	0.907029	0.863838	0.822702	0.783526	0.746215	0.710681	0.676839	0.644609	0.613913	0.584679	0.556837	0.530321	0.505068	0.481017	0.458112	0.436297	0.415521	0.395734	0.376889	0.358942	0.34185	0.325571	0.310068	0.295303	0.281241	0.267848	0.255094	0.242946	0.231377	0.220359	0.209866
a	111.852	5.47619	5.21542	4.967066	4.730539	4.505275	4.290739	4.086418	3.891826	3.706501	3.530001	3.361906	3.201815	3.049348	2.904141	2.765848	2.634141	2.508706	2.389244	2.27547	2.167115	2.063919	1.965637	1.872035	1.78289	1.697991	1.617134	1.540128	1.466788	1.396941	1.33042	1.267067	22.19335
	-8.57799
PVf @4.5		1/(1.045)	1/(1.045)^2	1/(1.045)^3	1/(1.045)^4	1/(1.045)^5	1/(1.045)^6	1/(1.045)^7	1/(1.045)^8	1/(1.045)^9	1/(1.045)^10	1/(1.045)^11	1/(1.045)^12	1/(1.045)^13	1/(1.045)^14	1/(1.045)^15	1/(1.045)^16	1/(1.045)^17	1/(1.045)^18	1/(1.045)^19	1/(1.045)^20	1/(1.045)^21	1/(1.045)^22	1/(1.045)^23	1/(1.045)^24	1/(1.045)^25	1/(1.045)^26	1/(1.045)^27	1/(1.045)^28	1/(1.045)^29	1/(1.045)^30	1/(1.045)^31	1/(1.045)^32
		0.956938	0.91573	0.876297	0.838561	0.802451	0.767896	0.734828	0.703185	0.672904	0.643928	0.616199	0.589664	0.564272	0.539973	0.51672	0.494469	0.473176	0.4528	0.433302	0.414643	0.396787	0.379701	0.36335	0.347703	0.332731	0.318402	0.304691	0.291571	0.279015	0.267	0.255502	0.2445
b	120.9861	5.502392	5.265447	5.038705	4.821728	4.614094	4.4154	4.225264	4.043314	3.8692	3.702584	3.543143	3.390567	3.244562	3.104844	2.971143	2.843199	2.720764	2.603602	2.491485	2.384196	2.281528	2.18328	2.089263	1.999295	1.913201	1.830814	1.751975	1.676531	1.604336	1.53525	1.469139	25.85587

	0.556114
a-b	-9.13411	0.50%
Price-b	0.556114	B14*C15/C14*100
		So IRR is 4.529%

A2

Macaulay Duration
Year	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31	32	Total
Cash flows semi annually	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	105.75
PVf @4	1/(1.045)	1/(1.045)^2	1/(1.045)^3	1/(1.045)^4	1/(1.045)^5	1/(1.045)^6	1/(1.045)^7	1/(1.045)^8	1/(1.045)^9	1/(1.045)^10	1/(1.045)^11	1/(1.045)^12	1/(1.045)^13	1/(1.045)^14	1/(1.045)^15	1/(1.045)^16	1/(1.045)^17	1/(1.045)^18	1/(1.045)^19	1/(1.045)^20	1/(1.045)^21	1/(1.045)^22	1/(1.045)^23	1/(1.045)^24	1/(1.045)^25	1/(1.045)^26	1/(1.045)^27	1/(1.045)^28	1/(1.045)^29	1/(1.045)^30	1/(1.045)^31	1/(1.045)^32
b	5.502392	5.265447	5.038705	4.821728	4.614094	4.4154	4.225264	4.043314	3.8692	3.702584	3.543143	3.390567	3.244562	3.104844	2.971143	2.843199	2.720764	2.603602	2.491485	2.384196	2.281528	2.18328	2.089263	1.999295	1.913201	1.830814	1.751975	1.676531	1.604336	1.53525	1.469139	25.85587	120.9861
	5.502392	10.53089	15.11612	19.28691	23.07047	26.4924	29.57685	32.34652	34.8228	37.02584	38.97457	40.68681	42.17931	43.46782	44.56714	45.49118	46.25299	46.86484	47.33822	47.68393	47.91208	48.03216	48.05305	47.98308	47.83002	47.60117	47.30334	46.94288	46.52575	46.0575	45.5433	827.3877	2024.45
	Macaulay Duration	16.73291	16.7329/2	8.37 years

A3

Horizon return of the bond
Bond to maturity	Reinvestment rate	3% assumed that 3 % is the semi year interest
Cash flows semi annually	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	105.75	Total
Interest on the coupon	(1.03)^31	(1.03)^30	(1.03)^29	(1.03)^28	(1.03)^27	(1.03)^26	(1.03)^25	(1.03)^24	(1.03)^23	(1.03)^22	(1.03)^21	(1.03)^20	(1.03)^19	(1.03)^18	(1.03)^17	(1.03)^16	(1.03)^15	(1.03)^14	(1.03)^13	(1.03)^12	(1.03)^11	(1.03)^10	(1.03)^9	(1.03)^8	(1.03)^7	(1.03)^6	(1.03)^5	(1.03)^4	(1.03)^3	(1.03)^2	(1.03)^1	(1.03)^0	0
	8.25008	8.177262	8.106566	8.037928	7.971289	7.906591	7.843778	7.782794	7.723587	7.666103	7.610295	7.556111	7.503506	7.452433	7.402848	7.354706	7.307967	7.26259	7.218534	7.175761	7.134234	7.093916	7.054773	7.01677	6.979874	6.944052	6.909274	6.875509	6.842727	6.8109	6.78	105.75	335.5028
	(AI33/100)^(1/32)	1.038551	0.038551	3.85%
Assumed that 7% is half year interest
Horizon return of the bond
Bond to maturity	Reinvestment rate	7%
Cash flows semi annually	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	5.75	105.75	Total
Interest on the coupon	(1.07)^31	(1.07)^30	(1.07)^29	(1.07)^28	(1.07)^27	(1.07)^26	(1.07)^25	(1.07)^24	(1.07)^23	(1.07)^22	(1.07)^21	(1.07)^20	(1.07)^19	(1.07)^18	(1.07)^17	(1.07)^16	(1.07)^15	(1.07)^14	(1.07)^13	(1.07)^12	(1.07)^11	(1.07)^10	(1.07)^9	(1.07)^8	(1.07)^7	(1.07)^6	(1.07)^5	(1.07)^4	(1.07)^3	(1.07)^2	(1.07)^1	(1.07)^0	SUM(C41:AH41)
	13.89511	13.36226	12.86426	12.39884	11.96387	11.55735	11.17743	10.82237	10.49053	10.1804	9.890562	9.619684	9.366528	9.129932	8.908815	8.702164	8.509032	8.328534	8.159845	8.002192	7.854852	7.717151	7.588459	7.468186	7.355781	7.25073	7.152552	7.060796	6.975043	6.8949	6.82	106.75	394.2182