I need the following solution calculated in an excel worksheet. please see attached problems and solutions

Problem 4.8. The cash prices of six-month and one-year Treasury bills are 94.0 and 89.0. A 1.5-year bond that will pay coupons of $4 every six months currently sells for $94.84. A two-year bond that will pay coupons of $5 every six months currently sells for $97.12. Calculate the six-month, one-year, 1.5-year, and two-year zero rates. The 6-month Treasury bill provides a return of 2 6383 12766% 6 94 6383% per annum with semiannual compounding or annum with continuous compounding. The 12-month rate is compounding or For the 1 1 2 in six months. This is ln(11236) 1165% 2 ln(106383) 1238% 11 89 12360% with continuous compounding. year bond we must have 4e 0123805 4e011651 104e15 R 9484 where is the 1 year zero rate. It follows that 1 R 2 376 356 104e 15 R 9484 e 15 R 08415 R 0115 or 11.5%. 4e 0123805 4e 011651 104e 15 R 9484 with annual per 376 356 104e15 R 9484 e15 R 08415 R 0115 A little hint to solve for R = 0.115 The first 2 terms can be calculated in Excel, using EXP(...) to get 3.76 and 3.56. Those are added ( 7.32) and subtracted from 94.84 to get 87.52. Now you have 104exp(-1.5R) = 87.52 Divide both sides by 104 Now you get exp(01.5R) = .8415 Take the natural log (ln) of both sides. When you take the natural log of the exponential function, you get the value of the power of the exponent, namely -1.5R Hence, -1.5R = ln(.8415) Ln(.8415) = -0.17252 Solve for R R = -0.17252/-1.5 = 0.115016 For the 2-year bond we must have 5e 0123805 5e 011651 5e011515 105e 2 R 9712 where is the 2-year zero rate. It follows that R e 2 R 07977 R 0113 or 11.3%. Problem 4.24 The following table gives Treasury zero rates and cash flows on a Treasury bond: Maturity (years 0.5 1.0 1.5 2.0 Zero rate 2.0% 2.3% 2.7% 3.2% Coupon payment $20 $20 $20 $20 Principal $1000 Zero rates are continuously compounded (a) What is the bond's theoretical price? (b) What is the bond's yield? The bond's theoretical price is 20e-0.020.5+20e-0.0231+20e-0.0271.5+1020e-0.0322 = 1015.32 The bond's yield assuming that it sells for its theoretical price is obtained by solving 20e-y0.5+20e-y1+20e-y1.5+1020e-y2 = 1015.32 It is 3.18%. Chapter 5: Determination of Forwards and Futures Contracts Chapter 5: 4, 9, 17, 25, 29 and 32. Problem 4.32. The following table gives the prices of bonds Bond Principal ($) Time to Maturity (yrs) Annual Coupon ($)* Bond Price ($) 100 100 100 100 0.5 1.0 1.5 2.0 0.0 0.0 6.2 8.0 98 95 101 104 *Half the stated coupon is paid every six months a) Calculate zero rates for maturities of 6 months, 12 months, 18 months, and 24 months. b) What are the forward rates for the periods: 6 months to 12 months, 12 months to 18 months, 18 months to 24 months? c) What are the 6-month, 12-month, 18-month, and 24-month par yields for bonds that provide semiannual coupon payments? d) Estimate the price and yield of a two-year bond providing a semiannual coupon of 7% per annum. a) The zero rate for a maturity of six months, expressed with continuous compounding is . The zero rate for a maturity of one year, expressed with 2 ln(1 2 98) 40405% continuous compounding is ln(1 5 95) 51293 . The 1.5-year rate is R where 31e004040505 31e 00512931 1031e R15 101 The solution to this equation is R 0054429 . The 2.0-year rate is R where 4e004040505 4e00512931 4e005442915 104e R2 104 The solution to this equation is Maturity (yrs) Zero Rate (%) 0.5 1.0 1.5 2.0 4.0405 5.1293 5.4429 5.8085 R 0058085 . These results are shown in the table below Forward Rate (%) 4.0405 6.2181 6.0700 6.9054 Par Yield (s.a.%) Par yield (c.c %) 4.0816 5.1813 5.4986 5.8620 4.0405 5.1154 5.4244 5.7778 b) The continuously compounded forward rates calculated using equation (4.5) are shown in the third column of the table c) The par yield, expressed with semiannual compounding, can be calculated from the formula in Section 4.4. It is shown in the fourth column of the table. In the fifth column of the table it is converted to continuous compounding d) The price of the bond is 35e 004040505 35e00512931 35e005442915 1035e00580852 10213 e) The yield on the bond, y satisfies 35e y05 35e y10 35e y15 1035e y20 10213 f) The solution to this equation is y 0057723 . The bond yield is therefore 5.7723%. Problem 5.9. A one-year long forward contract on a non-dividend-paying stock is entered into when the stock price is $40 and the risk-free rate of interest is 10% per annum with continuous compounding. a) What are the forward price and the initial value of the forward contract? b) Six months later, the price of the stock is $45 and the risk-free interest rate is still 10%. What are the forward price and the value of the forward contract? a) The forward price, F0 , is given by equation (5.1) as: F0 40e011 4421 or $44.21. The initial value of the forward contract is zero. b) The delivery price K in the contract is $44.21. The value of the contract, f, after six months is given by equation (5.5) as: f 45 4421e 0105 295 i.e., it is $2.95. The forward price is: 45e0105 4731 or $47.31. Problem 5.25. In early 2012, the spot exchange rate between the Swiss Franc and U.S. dollar was 1.0404 ($ per franc). Interest rates in the U.S. and Switzerland were 0.25% and 0% per annum,respectively, with continuous compounding. The three-month forward exchange rate was1.0300 ($ per franc). What arbitrage strategy was possible? How does your answer change if the exchange rate is 1.0500 ($ per franc). The theoretical forward exchange rate is 1.0404e(0.00250)0.25 = 1.041. If the actual forward exchange rate is 1.03, an arbitrageur can a) borrow X Swiss francs, b) convert the Swiss francs to 1.0404X dollars and invest the dollars for three months at 0.25% and c) buy X Swiss francs at 1.03 in the three-month forward market. In three months, the arbitrageur has 1.0404Xe0.00250.25 = 1.041X dollars. A total of 1.3X dollars are used to buy the Swiss francs under the terms of the forward contract and a gain of 0.011X is made. If the actual forward exchange rate is 1.05, an arbitrageur can a) borrow X dollars, b) convert the dollars to X/1.0404 Swiss francs and invest the Swiss francs for three months at zero interest rate, and c) enter into a forward contract to sell X/1.0404 Swiss francs in three months. In three months the arbitrageur has X/1.0404 Swiss francs. The forward contract converts these to (1.05X)/1.0404=1.0092X dollars. A total of Xe0.00250.25 =1.0006X is needed to repay the dollar loan. A profit of 0.0086X dollars is therefore made. Problem 5.29. A stock is expected to pay a dividend of $1 per share in two months and in five months. The stock price is $50, and the risk-free rate of interest is 8% per annum with continuous compounding for all maturities. An investor has just taken a short position in a six-month forward contract on the stock. a b What are the forward price and the initial value of the forward contract? Three months later, the price of the stock is $48 and the risk-free rate of interest is still 8% per annum. What are the forward price and the value of the short position in the forward contract? a The present value, I , of the income from the security is given by: I 1 e0082 12 1 e 0085 12 19540 From equation (5.2) the forward price, F0 , is given by: F0 (50 19540)e 00805 5001 or $50.01. The initial value of the forward contract is (by design) zero. The fact that the forward price is very close to the spot price should come as no surprise. When the compounding frequency is ignored the dividend yield on the stock equals the risk-free rate of interest. b In three months: I e008212 09868 The delivery price, contract, f K , is 50.01. From equation (5.6) the value of the short forward , is given by f (48 09868 5001e 008312 ) 201 and the forward price is (48 09868)e008312 4796 Problem 5.32. A trader owns a commodity as part of a long-term investment portfolio. The trader can buy the commodity for $950 per ounce and sell it for $949 per ounce. The trader can borrow funds at 6% per year and invest funds at 5.5% per year. (Both interest rates are expressed with annual compounding.) For what range of one-year forward prices does the trader have no arbitrage opportunities? Assume there is no bid-offer spread for forward prices. Suppose that F0 is the one-year forward price of the commodity. If F0 is relatively high, the trader can borrow $950 at 6%, buy one ounce of the commodity and enter into a forward contract to sell the commodity in one year for . The profit made in one year is F0 F0 950 1.06 F0 1007 This is profitable if F0 >1007. If F0 is relatively low, the trader can sell one ounce of the commodity for $949, invest the proceeds at 5.5%, and enter into a forward contract to buy it back for . The profit (relative to the position the trader would be in if the commodity were held in F0 the portfolio during the year) is 949 1.055 F0 1001 .195 This shows that there is no arbitrage opportunity if the forward price is between $1001.195 and $1007 per ounce. Chapter 6: Interest Rate Chapter 6: 2, 10, 14, 19, 25, and 29. Futures Problem 6.10. Suppose that the Treasury bond futures price is 101-12. Which of the following four bonds is cheapest to deliver? Bond 1 2 3 4 Price 125-05 142-15 115-31 144-02 Conversion Factor 1.2131 1.3792 1.1149 1.4026 The cheapest-to-deliver bond is the one for which Quoted Price Futures Price Conversion Factor is least. Calculating this factor for each of the 4 bonds we get Bond 1 12515625 101375 12131 2178 Bond 2 14246875 101375 13792 2652 Bond 3 11596875 101375 11149 2946 Bond 4 14406250 101375 14026 1874 Bond 4 is therefore the cheapest to deliver. Problem 6.14. A five-year bond with a yield of 11% (continuously compounded) pays an 8% coupon at the end of each year. a) What is the bond's price? b) What is the bond's duration? c) Use the duration to calculate the effect on the bond's price of a 0.2% decrease in its yield. d) Recalculate the bond's price on the basis of a 10.8% per annum yield and verify that the result is in agreement with your answer to (c). a) The bond's price is 8e011 8e 0112 8e0113 8e0114 108e0115 8680 b) The bond's duration is 1 011 2 8e 0112 3 8e 0113 4 8e 0114 5 108e 0115 8e 8680 4256years c) Since, with the notation in the chapter B BDy the effect on the bond's price of a 0.2% decrease in its yield is 8680 4256 0002 074 The bond's price should increase from 86.80 to 87.54. d) With a 10.8% yield the bond's price is 8e 0108 8e 01082 8e 01083 8e01084 108e01085 8754 This is consistent with the answer in (c). Problem 6.25 The December Eurodollar futures contract is quoted as 98.40 and a company plans to borrow $8 million for three months starting in December at LIBOR plus 0.5%. (a) What rate can then company lock in by using the Eurodollar futures contract? (b) What position should the company take in the contracts? (c) If the actual three-month rate turns out to be 1.3%, what is the final settlement price on the futures contracts. Explain why timing mismatches reduce the effectiveness of the hedge.. (a) The company can lock in a 3-month rate of 100 98.4 =1.60%. The rate it pays is therefore locked in at 1.6 + 0.5 = 2.1%. (b) The company should sell (i.e., short) 8 contracts. If rates increase, the futures quote goes down and the company gains on the futures. Similarly, if rates decrease, the futures quote goes up and the company loses on the futures. (c) The final settlement price is 100 1.30 = 98.70. The futures contract is settled in December, but the interest rate on a loan starting in December is paid three months later. Problem 6.29. Portfolio A consists of a one-year zero-coupon bond with a face value of $2,000 and a 10-year zero-coupon bond with a face value of $6,000. Portfolio B consists of a 5.95-year zero-coupon bond with a face value of $5,000. The current yield on all bonds is 10% per annum. (a) Show that both portfolios have the same duration. (b) Show that the percentage changes in the values of the two portfolios for a 0.1% per annum increase in yields are the same. (c) What are the percentage changes in the values of the two portfolios for a 5% per annum increase in yields? a) The duration of Portfolio A is 1 2000e 011 10 6000e 0110 595 2000e 011 6000e 0110 Since this is also the duration of Portfolio B, the two portfolios do have the same duration. b) The value of Portfolio A is 2000e01 6000e 0110 401695 When yields increase by 10 basis points its value becomes 2000e0101 6000e010110 399318 The percentage decrease in value is 2377 100 059% 401695 The value of Portfolio B is 5000e 01595 275781 When yields increase by 10 basis points its value becomes 5000e0101595 274145 The percentage decrease in value is 1636 100 059% 275781 The percentage changes in the values of the two portfolios for a 10 basis point increase in yields are therefore the same. c) When yields increase by 5% the value of Portfolio A becomes 2000e 015 6000e 01510 306020 and the value of Portfolio B becomes 5000e 015595 204815 The percentage reduction in the values of the two portfolios are: 95675 Portfolio A 100 2382 401695 70966 Portfolio B 100 2573 275781 Since the percentage decline in value of Portfolio A is less than that of Portfolio B, Portfolio A has a greater convexity (see Figure 6.2 in text)