In: Finance
On Feb 1, 2019, the price of a T-bill maturing on July 31, 2019 is $96.98, the price of a T-bill with maturity on Jan 31, 2020 is $97.11, and the price of a stripe with maturity on July 31 of 2020 is $97.76. A semiannual coupon treasury note that has the coupon rate 4.75% and will mature on July 31, 2020. What is the price of this T-note?
A | B | C | D | E | F | G | H | I | J |
2 | |||||||||
3 | Price of bond with face value F, years to maturity of n and yield of y can be written as follows: | ||||||||
4 | Price of bond, P | = F/ (1+y)n | |||||||
5 | |||||||||
6 | For semi-annual bond, | ||||||||
7 | Price of bond, P | = F/ (1+y/2)2n | |||||||
8 | |||||||||
9 | Using the above formula price of the bond P can be calculated as follows: | ||||||||
10 | |||||||||
11 | Year to maturity (n) | Price | Face Value | Yield | |||||
12 | 0.5 | $96.98 | $100 | 6.23% | =2*(((E12/D12)^(1/(2*C12)))-1) | ||||
13 | 1 | $97.11 | $100 | 2.95% | |||||
14 | 1.5 | $97.76 | $100 | 1.52% | |||||
15 | |||||||||
16 | Calculation of price of the coupon bond: | ||||||||
17 | |||||||||
18 | Face Value | $1,000 | |||||||
19 | Coupond rate | 4.75% | |||||||
20 | |||||||||
21 | Semi-annual coupon | $47.50 | |||||||
22 | |||||||||
23 | Price of the T-note will be the present value of the future cash flows. | ||||||||
24 | |||||||||
25 | Cash Flow of the bond will be as follows: | ||||||||
26 | Semi-annual period | 0 | 1 | 2 | 3 | ||||
27 | Cash flow of the bond | $47.50 | $47.50 | $1,047.50 | |||||
28 | Semi-annual yield | 3.11% | 1.48% | 0.76% | |||||
29 | Present Value of cash flows | $46.07 | $46.13 | $1,024.04 | =G27/((1+G28)^G26) | ||||
30 | Price of the Bond | $1,116.23 | =SUM(E29:G29) | ||||||
31 | |||||||||
32 | Hence the price of the bill is | $1,116.23 | |||||||
33 |
Formula sheet
A | B | C | D | E | F | G | H | I | J |
2 | |||||||||
3 | Price of bond with face value F, years to maturity of n and yield of y can be written as follows: | ||||||||
4 | Price of bond, P | = F/ (1+y)n | |||||||
5 | |||||||||
6 | For semi-annual bond, | ||||||||
7 | Price of bond, P | = F/ (1+y/2)2n | |||||||
8 | |||||||||
9 | Using the above formula price of the bond P can be calculated as follows: | ||||||||
10 | |||||||||
11 | Year to maturity (n) | Price | Face Value | Yield | |||||
12 | 0.5 | 96.98 | 100 | =2*(((E12/D12)^(1/(2*C12)))-1) | =getformula(F12) | ||||
13 | 1 | 97.11 | 100 | =2*(((E13/D13)^(1/(2*C13)))-1) | |||||
14 | 1.5 | 97.76 | 100 | =2*(((E14/D14)^(1/(2*C14)))-1) | |||||
15 | |||||||||
16 | Calculation of price of the coupon bond: | ||||||||
17 | |||||||||
18 | Face Value | 1000 | |||||||
19 | Coupond rate | 0.0475 | |||||||
20 | |||||||||
21 | Semi-annual coupon | =D18*D19 | |||||||
22 | |||||||||
23 | Price of the T-note will be the present value of the future cash flows. | ||||||||
24 | |||||||||
25 | Cash Flow of the bond will be as follows: | ||||||||
26 | Semi-annual period | 0 | 1 | 2 | 3 | ||||
27 | Cash flow of the bond | =$D$21 | =$D$21 | =$D$21+D18 | |||||
28 | Semi-annual yield | =F12/2 | =F13/2 | =F14/2 | |||||
29 | Present Value of cash flows | =E27/((1+E28)^E26) | =F27/((1+F28)^F26) | =G27/((1+G28)^G26) | =getformula(G29) | ||||
30 | Price of the Bond | =SUM(E29:G29) | =getformula(D30) | ||||||
31 | |||||||||
32 | Hence the price of the bill is | =D30 | |||||||
33 |