Question;(QUESTION 1 INVOLVES GOAL SEEK FUNTION)1. Write (Type) down the formula that is used to calculate the yield to maturity on a 20-year 10% coupon bond with $1,000 face value that sells for $2,000. Assume yearly coupons. Use the Goal Seek function in Excel to solve for the YTM in your formula.Below is an example in RED! Please Make Sure Your Answer Follows Below.Question 1 Example Problem:This example walks shows you how to use goal seek to find a solution. The example is based on finding the yield to maturity of a 10% coupon bond with 3 years to maturity, $1,000 face value, and currently selling for $990. Task is to find the yield to maturity. NOTE: Assuming semi-annual coupons, the bond pays $50 every 6 months. Heres the equation set-up:50505050501050990YTM 1YTM 2YTM 3YTM 4YTM 5YTM 6(1) (1)(1)(1)(1)(1)222222Finding the yield requires solving for YTM and then multiply by 2. This is one equation with one unknown it is solvable. One way is to use trial and error. Plug in a guess, say 12%. If the discounted CF equal 990, then that works.Setting up the problem in Excel, heres a screen shot of the spreadsheet. Column 1 shows the timing of the cash flows in semiannual periods and column 2 shows the cash flows of the bond.Here, the original guess at the YTM was 6%. Note that if the YTM is 6% compounded semiannually, this means the semiannual discount rate is 3%. The column labeled PVCF discounts each cash flow by the appropriate number of semiannual periods. For example, the present value of the second coupon payment using a guess for YTM of 6%:5047.13(1.03) 2Adding up the PVCF, if the YTM is 6%, the price is 1108.34. In order to lower the price to 990, we need to raise the YTM guess. You could increase YTM to 8% and try again, and so on. Or, to be more efficient, lets use Goal Seek. Under the Tools menu in Excel, select Goal Seek. A window pops up.You need to input three things.1) What cell to we want to set? Which cell to we want to change the value? We want the price of the bond (the sum of the PVCF) to be 990. So we want to set cell C10.2) What value do you want for C10? 9903) How do you intend to get there? How should we adjust the worksheet to get cell C10 to 990? We want to find the YTM, so change cell D2 until the price of the bond is 990.Hit OK and Excel will let you know if it found a solution.NOTE: Some of you may know that the RATE formula in Excel can also do this same task. But the reason Goal Seek is useful is that the RATE formula works only for regular (coupon) cash flows. In problems with irregular cash flows (that require the IRR function in your calculator), RATE wont work, and Goal Seek will.(QUESTION 2-7)2. Calculate the present value of a $1,000 zero-coupon bond with five years to maturity if the yield to maturity is 6%.Use the PV function in Excel to calculate the answer.3. A lottery claims their grand price is $10 million, payable over 20 years at $500,000 per year. If the first payment is made immediately, what is this grand prize really worth? Use an interest rate of 6%.Use the PV function in Excel. Alternatively, you can show the 20 cash flows, discount them, etc.4. Consider a bond with a 7% annual coupon and a face value of $1,000. Complete the following table. What relationships do you observe between maturity and discount rate and the current price?Years to Maturity333699Yield to Maturity5%7%9%7%5%9%Current Price5. Consider a coupon bond that has a $1,000 par, a coupon rate of 10%. The bond is currently selling for $1,150 and has 8 years to maturity? What is the bonds YTM?Use the RATE function in Excel to compute.6. A 10-year, 7% coupon bond with a face value of $1,000 is currently selling for $871.65. Compute your rate of return if you sell the bond next year for $880.10. 7. Calculate the duration of a $1,000, 6% coupon bond with three years to maturity. Assume that all market interest rates are 7%.You may use the Excel function DURATION:Hint: In Excel DURATION(TODAY(), TODAY()+3*365, 0.06, 0.07,1) = 2.831.Alternatively you can compute the sum of the time weighted PV of the Cashflows etc.
Paper#48103 | Written in 18-Jul-2015Price : $26