Question

(1 point) Two bonds, each with a face value of $11000, are redeemable at par in t- years and priced to yield y2-696. Bond i has a coupon rate c2-11.1% and sells for $14523.39. Bond 2 has coupon rate c2-3.7% and sells for $ What is the value of P? Answer: $

Answer 1

Goal seek setting

Formula sheet

A	B	C	D	E	F	G	H	I	J	K
2
3		First time t needs to be calculated using the data given for bond 1
4		then t is to be used to calculated the value of bond 2.
5
6		For Bond 1:
7		Time t can be found using the goal seek function of excel such that the value of bond 1 is equal to the price given.
8		Par value (F)	11000
9		Coupon rate	0.111
10		Yield	0.06
11		Time to maturity	8.11652374782718	Years
12		Price of Bond	14523.39
13		Interest is paid once a year i.e. annual.
14		Annual coupon (C)	=D8*D9
15		Annual Period (n)	=D11
16		YTM (i)	=D10
17		Current Value of the bond can be calculated by finding the present value of cash flows of bonds.
18		Cash Flow of Bonds can be written as follows:
19		Period	0	1	2	3	4	…	=D15
20		Cash Flow of Bonds		=$D$14	=$D$14	=$D$14	=$D$14	=$D$14	=$D$14+D8
21
22		Current Value of Bond	=C*(P/A,i,n)+F*(P/F,i,n)
23		Where, C is coupon, F is par value of bond, i is market rate and n is total number of periods.
24
25		Current Value of Bond	=C*(P/A,i,n)+F*(P/F,i,n)
26			=D14*PV(D16,D15,-1,0)+D8*(1/((1+D16)^D15))	=D14*PV(D16,D15,-1,0)+D8*(1/((1+D16)^D15))
27
28		Value of t	=ROUND(D11,0)	Years
29
30		Calculation of price of bond 2:
31
32		Par value (F)	11000
33		Coupon rate	0.037
34		Yield	0.06
35		Time to maturity	=D28	Years
36
37		Interest is paid once a year i.e. annual.
38		Annual coupon (C)	=D32*D33
39		Annual Period (n)	=D35
40		YTM (i)	=D34
41		Current Value of the bond can be calculated by finding the present value of cash flows of bonds.
42		Cash Flow of Bonds can be written as follows:
43		Period	0	1	2	3	4	…	=D39
44		Cash Flow of Bonds		=$D$38	=$D$38	=$D$38	=$D$38	=$D$38	=$D$38+D32
45
46		Current Value of Bond	=C*(P/A,i,n)+F*(P/F,i,n)
47		Where, C is coupon, F is par value of bond, i is market rate and n is total number of periods.
48
49		Current Value of Bond	=C*(P/A,i,n)+F*(P/F,i,n)
50			=D38*PV(D40,D39,-1,0)+D32*(1/((1+D40)^D39))	=D38*PV(D40,D39,-1,0)+D32*(1/((1+D40)^D39))
51
52		Hence value of bond 2 is	=D50
53