Question

1. Using Excel, compute the yield-to-maturity for the following bonds (report your Excel formulas and their inputs):
   1. Price = 90, Fixed Coupon Rate = 5%, Maturity = 5 years, Coupon Frequency = 1
   2. Price = 90, Fixed Coupon Rate = 5%, Maturity = 5 years, Coupon Frequency = 2
   3. Price = 90, Fixed Coupon Rate = 5%, Maturity = 5 years, Coupon Frequency = 4
   4. Do you see any differences in yield-to-maturities computed for bonds with different coupon frequencies? If so, investigate and explain the reason.

Answer 1

a) YTM is calculated below:

Information			Excel Terminology
Market Price	Given	-90	nper	No. of coupon periods	5
coupon rate	Given	5%	pmt	Coupon Payment	5
No. of years to maturity	Given	5	pv	Market Price	-90
Par Value	Given	100	fv	Par Value	100
Coupon frequency	Annual	1	type	End of the period	0
No. of coupon periods	5*1	5	Rate formula		7.470%
Coupon Payment	0.05*100/1	5	YTM	1x0.0746965511639514	7.470%

b) YTM is calculated below:

Information			Excel Terminology
Market Price	Given	-90	nper	No. of coupon periods	10
coupon rate	Given	5%	pmt	Coupon Payment	2.5
No. of years to maturity	Given	5	pv	Market Price	-90
Par Value	Given	100	fv	Par Value	100
Coupon frequency	Semi Annual	2	type	End of the period	0
No. of coupon periods	5*2	10	Rate formula		3.716%
Coupon Payment	0.05*100/2	2.5	YTM	2x0.0371551077731554	7.431%

c) YTM is calculated below:

Information			Excel Terminology
Market Price	Given	-90	nper	No. of coupon periods	20
coupon rate	Given	5%	pmt	Coupon Payment	1.25
No. of years to maturity	Given	5	pv	Market Price	-90
Par Value	Given	100	fv	Par Value	100
Coupon frequency	Quarterly	4	type	End of the period	0
No. of coupon periods	5*4	20	Rate formula		1.853%
Coupon Payment	0.05*100/4	1.25	YTM	4x0.0185292132232051	7.412%

d) So basically, the YTM keeps on decreasing as we go on increasing the compounding frequency that is because if we increase the compounding frequency, we compound the money faster than we do in lower frequency