Description of this paper

Discount rates and market maturity




Need assistance with seven questions. All must be solved in Excel. Excel Concepts: Goal Seek Function, FV Funtion, Rate Function, Duration Function Etc. I need the answer sheet and the excel file when complete.;Attachments Preview;answers.pdf Download Attachment;ANSWER SHEET;*Use One Excel File and Create Separate Tabs for Each Problem;*Answer in Bold Below;1.;2;3.;4.;5.;6.;7..;View Full Attachment;problems.pdf Download Attachment;(QUESTION 1);1. Write 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. Must Use the Goal Seek function in Excel to solve for the YTM in your;formula. Must explicitly follow the example problem below for any credit.;Question 1 Example Problem;Below is an example in RED! Please Make Sure Your Answer Follows Below.;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;50;50;50;50;50;1050;990;YTM 1;YTM 2;YTM 3;YTM 4;YTM 5;YTM 6;(1;(1;(1;(1;(1;(1;2;2;2;2;2;2;Finding 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%;50;47.13;(1.03) 2;Adding 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? 990;3) 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 Maturity;3;3;3;6;9;9;Yield to Maturity;5%;7%;9%;7%;5%;9%;Current Price;5. 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#17915 | Written in 18-Jul-2015

Price : $57