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 |
(1 point) Two bonds, each with a face value of $11000, are redeemable at par in...
(1 point) Two bonds, each with a face value of $18000, are redeemable at par in t-years and priced to yield y,-9%. Bond 1 has a coupon rate c4 = 11.4% and sells for $20225.65. Bond 2 has coupon rate c4-4.5% and sells for $ P. What is the value of P? Answer:$
(1 point) Two bonds, each with a face value of $16000, are redeemable at par in t-years and priced to yield 44 = 10%. Bond 1 has a coupon rate C4 = 12% and sells for $17884.50. Bond 2 has coupon rate C4 = 4.2% and sells for $ P. What is the value of P? Answer: $
Two bonds, each with a face value of $17000, are redeemable at par in t-years and priced to yield y4 = 10%. Bond 1 has a coupon rate c4 = 11.9% and sells for $18444.21. Bond 2 has coupon rate c4 = 4.1% and sells for $ P. What is the value of P?
Two bonds, each with a face value of $17000, are redeemable at par in n-years and priced to yield j12 = 14.4%. Bond 1 has a coupon rate j12 = 24% and sells for $22793.11. Bond 2 has coupon rate j12 = 7.2% and sells for $ P. What is the value of P?
(1 point) A 11-year bond with a face value of $1000 is redeemable at twice par and pays coupons semi-annually at C2 = 10.3 %. If the yield rate is y2 = 6.9 %, find the book value of the bond immediately after the payment of the 11th coupon. Answer: $
(1 point) A 10 year $11 000 par-valued bond pays monthly coupons. If the yield rate is y 12-9% and the purchase price is $7381.84, what is the coupon rate c12? Answer: (1 point) Two bonds, each with a face value of $13000, are redeemable at par in t-years and priced to yield y4-8%. Bond 1 of P? has a coupon rate c4-11.8% and sells for $15628.24. Bond 2 has coupon rate c-5% and sells for S R What is...
(1 point) Two 1000 dollar face value bonds are both redeemable at par, with the first having a redemption date 3 years prior to the redemption date of the second. Both are bought to yield 11.7 percent convertible semiannually. The first bond sells for 802.61 dollars and pays coupons at 8.3 precent convertible semiannually. The second bond pays coupons at 5.2 percent per half year. What is the price of the second bond?
(1 point) A 7-year bond with a face value of 5000 dollars is redeemable at par and earns interest at 9.1 percent convertible semiannually. If the yield rate is 7.2 percent convertible semiannually, how large is each coupon?
(1 point) An investor purchases two bonds with the following properties: Bond 1: Has a face value $1000 and is redeemable at par. Pays coupons annually at a rate of 8.4% annual and was purchased for $1276.13 Bond 2: Has a face value $1000 and is redeemable at par. Pays coupons annually at a rate of 5.6% annual and was purchased for $1058.34 If both bonds mature in the same number of years and the investor yields the same rate...
(1 point) An investor purchases two bonds with the following properties: Bond 1: Has a face value $1000 and is redeemable at par. Pays coupons annually at a rate of 8% annual and was purchased for $1084.58. Bond 2: Has a face value $1000 and is redeemable at par. Pays coupons annually at a rate of 6.4% annual and was purchased for $996.7. If both bonds mature in the same number of years and the investor yields the same rate...