Question

The dollar return for a semiannual bond with the following characteristics assuming that you can

earn the same yield on a 9-year certificate of deposit are:

Coupon rate 6.10%

Time to maturity 9 years

Price $109.79

YTM (BEY) 4.75%

a. Calculate the realized rate of return for the buy and hold investor if market interest rates increase

by 50 basis points after purchase and before the first coupon payment.

b. Calculate the realized rate of return of the investor who sells the bond after 5 years, but interest

rates decrease by 50 basis point after purchase and before the first coupon payment.

Can someone solve this in excel? Please show the variables. Thanks!!

Answer 1

The dollar return for a semiannual bond with the following characteristics assuming that you can earn the same yield on a 9-year certificate of deposit are:
Coupon rate 6.10%
Time to maturity 9 years
Price $109.79
YTM (BEY) 4.75%
a. Calculate the realized rate of return for the buy and hold investor if market interest rates increase by 50 basis points after purchase and before the first coupon payment.
Year	0.5	1	1.5	2	2.5	3	3.5	4	4.5	5	5.5	6	6.5	7	7.5	8	8.5	9	9.5	10
Coupon	3.05	3.05	3.05	3.05	3.05	3.05	3.05	3.05	3.05	3.05	3.05	3.05	3.05	3.05	3.05	3.05	3.05	3.05	3.05	103.05
Discounting Factor	0.974421437	0.949497	0.92521	0.901545	0.878485	0.856014	0.834119	0.812783	0.791993	0.771735	0.751995	0.73276	0.714017	0.695754	0.677957	0.660616	0.643719	0.627253	0.611209	0.595575
	2.971985384	2.895966	2.821892	2.749712	2.679378	2.610843	2.544062	2.478988	2.415579	2.353792	2.293586	2.234919	2.177753	2.122049	2.06777	2.01488	1.963342	1.913122	1.864188	61.37402
Value	106.5478303
Total Return	-5.18
Realised rate of return for the period	-4.72%
b. Calculate the realized rate of return of the investor who sells the bond after 5 years, but interest rates decrease by 50 basis point after purchase and before the first coupon payment.
Year	0.5	1	1.5	2	2.5	3	3.5	4	4.5	5	5.5	6	6.5	7	7.5	8	8.5	9	9.5	10
Coupon	3.05	3.05	3.05	3.05	3.05	3.05	3.05	3.05	3.05	3.05	3.05	3.05	3.05	3.05	3.05	3.05	3.05	3.05	3.05	103.05
Discounting Factor	0.979192166	0.958817	0.938866	0.919331	0.900201	0.88147	0.863129	0.845169	0.827583	0.810362	0.793501	0.77699	0.760822	0.744991	0.729489	0.71431	0.699447	0.684893	0.670642	0.656687
	2.986536108	2.924393	2.863542	2.803958	2.745614	2.688484	2.632542	2.577765	2.524127	2.471605	2.420177	2.369818	2.320507	2.272223	2.224943	2.178646	2.133313	2.088924	2.045458	67.67162
Value	114.9442004
Total Return	5.154200418
Realised rate of return for the period	4.69%