On page 69 included below, there is a section introducing EXCEL spreadsheet functions for solving bond valuation problems. Following the discussion, there are several spreadsheet questions, which allow you to practice using those EXCEL functions. Please complete the following:
1.Use EXCEL to solve spreadsheet questions 1, 2, 3, and 4 (all on page 69).
2.For question 2, you should use the YIELD function instead of the YLD function suggested by the book. Only use EXCEL functions to solve assigned problems.
USEFUL SPREADSHEET FUNCTIONS Valuing Bonds at any Spreadsheet programs such as Excel provide built-in functions to solve for a variety of bond valuation prob- . You must enter the yield and coupon as decimal ed by a values, for example, for 3% you would enter .03. ting of lems. You can find these functions by pressing fx on the Excel toolbar. If you then click on the function that you . Settlement is the date that payment for the security servers default wish to use, Excel will ask you for the inputs that it needs. is made. Maturity is the maturity date. You can nthink At the bottom left of the function box there is a Help enter these dates directly using the Excel date func- I to the facility with an example of how the function is used. tion; for example, you would enter 15 Feb 2009 as is fore- Here is a list of useful functions for valuing bonds. DATE(2009,02,15). Alternatively, you can enter Obama together with some points to remember when entering these dates in a cell and then enter the cell address data: in the function. sed the dard & . In the functions for PRICE and YLD you need to of the . PRICE: The price of a bond given its yield to maturity. scroll down in the function box to enter the fre- quency of coupon payments. Enter 1 for annual YLD: The yield to maturity of a bond given its price. payments or 2 for semiannual. -partment DURATION: The duration of a bond. . The functions for PRICE and YLD ask for an entry rect/blog/ . MDURATION: The modified duration (or volatil- for "basis." We suggest you leave this blank. (See ity) of a bond. the Help facility for an explanation.) Note: 3 more . You can enter all the inputs in these functions Spreadsheet Questions directly as numbers or as the addresses of cells that The following questions provide an opportunity to f com- s have contain the numbers. practice each of these functions. X 1 -PRICE DATE 2009.2.15).DATE (2014.7.15..05.04.160) 1. (PRICE) In February 2009, Treasury 8.5s of 2020 04.100) yielded 3.2976%. What was their price? If the yield :lihood rose to 4%, what would happen to the price? bonds. 2. (YLD) On the same day Treasury 3.5s of 2018 an cre- Saturty DATE(2084 2,15) were priced at 107.46875%. What was their yield to olitical maturity? Suppose that the price was 110.0%. What ressure on 100 would happen to the yield? and it 3. (DURATION) What was the duration of the Trea- le gov- sury 8.5s? How would duration change if the yield rose to 4%? Can you explain why? 4. (MDURATION) What was the modified duration rinting of the Treasury 8.5s? How would modified duration ey sup- differ if the coupon were only 7.5%? govern- y 2010 reece's rember largest ever sovereign default. The difficulties for Greece were not over. The rescue package member required it to adopt an austerity policy that resulted in a sharp fall in national income and Bailout considerable hardship. By early 2015 popular discontent led to the election of a left-wing s were government committed to ending austerity and once again renegotiating its debts. By July the vas the negotiations had gone nowhere and Greece defaulted on a payment due to the IMF, the first developed country ever to do so. The sovereign debt crisis was not confined to Greece. Cyprus also delayed repayment of its bonds, and the Irish and Portuguese government debt was down-rated to junk level. Inves- anounced enders, he tors joked that, instead of offering a risk-free return, eurozone government bonds just offered a return-free risk. 69
