Question

A bond portfolio named VEX comprises four bonds (face value=$1000):

1) 100 semi-annual bond, 5-year maturity, a coupon rate of 4%

2) 200 annual bonds, 30-year maturity, 8% coupon bond.

3) 300 zero-coupon bonds, 10-year maturity.

4) 400 zero-coupon bonds, 20-year maturity.

2. Given the 6% initial yield, what is the VEX’s duration (use Macaulay’s duration)?

Answer 1

A	B	C	D	E	F
2	Particular	Bond 1	Bond 2	Bond 3	Bond 4
3	PAR Value	1000	1000	1000	1000
4	Settlement Date	43941	43941	43941	43941
5	Maturity Date	45767	54898	47593	51246
6	Price	=PRICE(C4,C5,C7,C8,C3,C9,C10)	=PRICE(D4,D5,D7,D8,D3,D9,D10)	=PRICE(E4,E5,E7,E8,E3,E9,E10)	=PRICE(F4,F5,F7,F8,F3,F9,F10)
7	Coupons	0.04	0.08	0	0
8	Yield	0.06	0.06	0.06	0.06
9	Frequency	2	1	1	1
10	Basis	3	3	3	3
11	Duration	=DURATION(C4,C5,C7,C8,C9,C10)	=DURATION(D4,D5,D7,D8,D9,D10)	=DURATION(E4,E5,E7,E8,E9,E10)	=DURATION(F4,F5,F7,F8,F9,F10)
12	Number of Unit	100	200	300	400
13	Invested Amount	=C12*C6	=D12*D6	=E12*E6	=F12*F6
14	Market Value of PF	=SUM(C13:F13)
15	VEX Duration	=SUMPRODUCT(C11:F11,C13:F13)/C14

Final Answer

A	B	C	D	E	F
2	Particular	Bond 1	Bond 2	Bond 3	Bond 4
3	PAR Value	1000	1000	1000	1000
4	Settlement Date	20-04-2020	20-04-2020	20-04-2020	20-04-2020
5	Maturity Date	20-04-2025	20-04-2050	20-04-2030	20-04-2040
6	Price	761.15	284.23	558.39	311.80
7	Coupons	4%	8%	0	0
8	Yield	6%	6%	6%	6%
9	Frequency	2	1	1	1
10	Basis	3	3	3	3
11	Duration	4.56	13.89	10.00	20.00
12	Number of Unit	100	200	300	400
13	Invested Amount	76115.43	56845.76	167518.43	124721.89
14	Market Value of Portfolio	425201.51
15	VEX Duration	12.48

Feel free to ask any Query in Comment Section

Please provide feedback.

Cheers