{"id":78104,"date":"2018-02-20T23:50:30","date_gmt":"2018-02-20T23:50:30","guid":{"rendered":"https:\/\/writemyessayfree.com\/finance-problems-assignment-2"},"modified":"2017-08-18T07:18:26","modified_gmt":"2017-08-18T07:18:26","slug":"finance-problems-assignment-2","status":"publish","type":"post","link":"https:\/\/www.benedictsol.com\/blogs\/finance-problems-assignment-2\/","title":{"rendered":"Finance Problems Assignment"},"content":{"rendered":"<p><a class=\"finance-problems-assignment-2\/\"><\/a> <\/p>\n<p>(QUESTION 1 INVOLVES GOAL SEEK FUNTION)<br \/> 1. Write (Type) down the formula that is used to calculate the yield to maturity on a&nbsp;20-year 10% coupon bond with $1,000 face value that sells for $2,000. Assume&nbsp;yearly coupons. Use the Goal Seek function in Excel to solve for the YTM in your&nbsp;formula.<br \/> Below is an example in RED! Please Make Sure Your Answer Follows Below.<\/p>\n<p>Question 1 Example Problem:<br \/> This example walks shows you how to use goal seek to find a solution. The example is&nbsp;based on finding the yield to maturity of a 10% coupon bond with 3 years to maturity,&nbsp;$1,000 face value, and currently selling for $990. Task is to find the yield to maturity.&nbsp;NOTE: Assuming semi-annual coupons, the bond pays $50 every 6 months.<br \/> Heres the equation set-up:<br \/> 50<br \/> 50<br \/> 50<br \/> 50<br \/> 50<br \/> 1050<br \/> 990<\/p>\n<p>YTM 1<br \/> YTM 2<br \/> YTM 3<br \/> YTM 4<br \/> YTM 5<br \/> YTM 6<br \/> (1<br \/> ) (1<br \/> )<br \/> (1<br \/> )<br \/> (1<br \/> )<br \/> (1<br \/> )<br \/> (1<br \/> )<br \/> 2<br \/> 2<br \/> 2<br \/> 2<br \/> 2<br \/> 2<br \/> Finding the yield requires solving for YTM and then multiply by 2. This is one equation&nbsp;with one unknown it is solvable. One way is to use trial and error. Plug in a guess, say&nbsp;12%. If the discounted CF equal 990, then that works.<br \/> Setting up the problem in Excel, heres a screen shot of the spreadsheet. Column 1 shows&nbsp;the timing of the cash flows in semiannual periods and column 2 shows the cash flows of&nbsp;the bond.<\/p>\n<p>Here, the original guess at the YTM was 6%. Note that if the YTM is 6% compounded&nbsp;semiannually, this means the semiannual discount rate is 3%. The column labeled&nbsp;PVCF discounts each cash flow by the appropriate number of semiannual periods. For&nbsp;example, the present value of the second coupon payment using a guess for YTM of 6%:<br \/> 50<br \/> 47.13<br \/> (1 .03) 2<br \/> Adding up the PVCF, if the YTM is 6%, the price is 1108.34. In order to lower the price&nbsp;to 990, we need to raise the YTM guess. You could increase YTM to 8% and try again,&nbsp;and so on. Or, to be more efficient, lets use Goal Seek.&nbsp;Under the Tools menu in Excel, select Goal Seek. A window pops up.<\/p>\n<p>You need to input three things.<br \/> 1) What cell to we want to set? Which cell to we want to change the value? We&nbsp;want the price of the bond (the sum of the PVCF) to be 990. So we want to set&nbsp;cell C10.<br \/> 2) What value do you want for C10? 990<br \/> 3) How do you intend to get there? How should we adjust the worksheet to get cell&nbsp;C10 to 990? We want to find the YTM, so change cell D2 until the price of the&nbsp;bond is 990.<br \/> Hit OK and Excel will let you know if it found a solution.<br \/> NOTE: Some of you may know that the RATE formula in Excel can also do this same&nbsp;task. But the reason Goal Seek is useful is that the RATE formula works only for regular&nbsp;(coupon) cash flows. In problems with irregular cash flows (that require the IRR function&nbsp;in your calculator), RATE wont work, and Goal Seek will.<\/p>\n<p>(QUESTION 2-7)<br \/> 2. Calculate the present value of a $1,000 zero-coupon bond with five years to&nbsp;maturity if the yield to maturity is 6%.<br \/> Use the PV function in Excel to calculate the answer.<\/p>\n<p>3. A lottery claims their grand price is $10 million, payable over 20 years at&nbsp;$500,000 per year. If the first payment is made immediately, what is this grand&nbsp;prize really worth? Use an interest rate of 6%.Use the PV function in Excel.&nbsp;Alternatively, you can show the 20 cash flows, discount them, etc.<br \/> 4. Consider a bond with a 7% annual coupon and a face value of $1,000. Complete&nbsp;the following table. What relationships do you observe between maturity and&nbsp;discount rate and the current price?<br \/> Years to Maturity<br \/> 3<br \/> 3<br \/> 3<br \/> 6<br \/> 9<br \/> 9<\/p>\n<p>Yield to Maturity<br \/> 5%<br \/> 7%<br \/> 9%<br \/> 7%<br \/> 5%<br \/> 9%<\/p>\n<p>Current Price<\/p>\n<p>5. Consider a coupon bond that has a $1,000 par, a coupon rate of 10%. The bond&nbsp;is currently selling for $1,150 and has 8 years to maturity? What is the bonds&nbsp;YTM?<br \/> Use the RATE function in Excel to compute.<\/p>\n<p>6. A 10-year, 7% coupon bond with a face value of $1,000 is currently selling for&nbsp;$871.65. Compute your rate of return if you sell the bond next year for $880.10.&nbsp;7. Calculate the duration of a $1,000, 6% coupon bond with three years to maturity.&nbsp;Assume that all market interest rates are 7%.<br \/> You may use the Excel function DURATION:<br \/> Hint: In Excel DURATION(TODAY(), TODAY()+3*365, 0.06, 0.07,1) = 2.831.<br \/> Alternatively you can compute the sum of the time weighted PV of the Cashflows etc.<i><\/i><\/p>\n","protected":false},"excerpt":{"rendered":"<p>(QUESTION 1 INVOLVES GOAL SEEK FUNTION) 1. Write (Type) down the formula that is used to calculate the yield to maturity on a&nbsp;20-year 10% coupon bond with $1,000 face value that sells for $2,000. Assume&nbsp;yearly coupons. Use the Goal Seek <a href=\"https:\/\/www.benedictsol.com\/blogs\/finance-problems-assignment-2\/\" class=\"read-more\">Read More &#8230;<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[],"tags":[],"class_list":["post-78104","post","type-post","status-publish","format-standard","hentry"],"_links":{"self":[{"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/posts\/78104","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/comments?post=78104"}],"version-history":[{"count":0,"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/posts\/78104\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/media?parent=78104"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/categories?post=78104"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.benedictsol.com\/blogs\/wp-json\/wp\/v2\/tags?post=78104"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}