FNCE 451- Fall 2015Assignment 1Due October 1, 2015Each problem is worth 3 points for a total of 15. Please don’t hand in your Excel models, just use them. Slight differences in implementation might give different acceptable answers, that’s not the focus here. 1. With interest rates so low, some investors have considered replacing bond investments withstable dividend paying stocks. First we’ll look at a bond. a. ONT 3. 5 June/2/2043 is a bond issued by the Province of Ontario. The 3. 5% coupon ispaid semi-annually on June 2 and December 2 (i. e. $1. 75 per $100). Set up a column ofdates and cash flows in Excel and manually discount them by a rate that you can adjust. (Pretend it is June 2 right now so we don’t have to deal with accrued)b. What discount rate gets you a Present Value close to the trading price of $106? (Guessand check)c. Check your answer with the XNPV function. (Put a 0 cash flow for June 2, 2015 to hackthe formula). d. What is the present value of the final $101. 75 cash flow (that is 28 years from now)?2. Choice Properties REIT equity is a candidate for a “bond surrogate” because it pays a stabledividend supported by collecting rent on grocery stores. a. What is the current dividend (per share) of Choice Properties REIT? How often do theypay?b. Assuming the dividend never changes, set up a cash flow table for the perpetualdividend and discount the cash flows (back to June 2 again). How do you deal withinfinity here? Find a discount rate to get the present value close to the current tradingprice. Compare to the “dividend yield”: annual dividend divided by price. c. Reprogram your model to include a growth rate. Assume 3% growth (rent increases anddevelopment). What’s the discount rate to get close to the current trading price?Compare to the constant case. d. Compare these exercises to the annuity, perpetuity and growing perpetuity in thetextbook. Do the textbook formulas work?e. Interest rate risk. Suppose the Fed raises rates more than expected and all discountrates jump by 1%. What’s the percentage price loss oni. The Ontario long bondii. The constant-dividend Choice REIT (g=0%)iii. The growing Choice REIT (g=3%)3. IRR vs NPV. a. Read this paper for the main ideahttp: //papers. ssrn. com/sol3/papers. cfm?abstract_id=522722b. Verify the numerical results in Table 2. Repeat for a 10% discount rate. c. In your own words, compare the usefulness of the IRR rule and NPV rule for capitalbudgeting decisions. Explain how they are connected and outline how to give meaningto the imaginary roots of the IRR equation. 4. Replacement of equipment. I own an old car that is worth about $3,000. As long as I keep fixingit, that salvage value is constant. It costs $5,000/year to maintain and fuel the car. Elon Muskjust tweeted this: https: //twitter. com/elonmusk/status/639171519197777920The Tesla 3 will cost $35,000, but has much lower fuel and maintenance costs of only $500/year. All these figures are real dollars. Suppose the resale value of the Tesla after 7 years is $10,000. Use a 10% real discount rate. Spreadsheet: a. What is the 7-year present value of costs for my old car? (include final salvage)b. What is the 7-year present value of costs for the Tesla 3 replacement? (include initialsalvage and final resale)c. On a 7-year horizon, what is the annual lease equivalent for each car? In other words,convert the cash flows to a 7-year annuity. On a spreadsheet you can use guess-andcheck (or goal seek, or PMT function), on paper use the A(7,10%)=4. 868 annuity factor. Try all of these methods. d. Perhaps the Tesla also approaches a constant resale value. Since the Tesla generates$4,500/year of operating savings versus the old car (not including initial price), we couldestimate a terminal valuation using a “multiple” of annual savings. Use a 4X multiple,plus the old-car base salvage, to re-estimate the constant resale. What is the 7-yearannual lease equivalent now?5. After-tax yield on bonds. Most bonds right now trade at a lower market yield than their coupon. Therefore, the price is more than par (why?). Real examples of bonds with live prices herehttp: //www. ftse. com/products/FTSETMX/Home/LiveFixed(I will post a spreadsheet to help with this problem)a. Build a spreadsheet with the semi-annual coupon cash flows. Given a purchase price youcan compute the yield using an IRR (and check it with the YIELD function). UseCOUPNCD to get the first date in the cash flow table. Convention is yields are expressedas 2 times the semiannual rate. So use 2*[sqrt(1+IRR)-1]b. The new Alberta tax rates for incomes over $300k are 44% on income and 22% oncapital gains (check this). Assume your client is in that bracket and make a new columnof after tax cash flows. On the first coupon, you don’t pay tax on the accrued that youpaid. And you get a tax bonus at maturity based on the capital loss. c. Find a bond that has a negative after-tax yield! (Recall yield is IRR). Recommend analternative with similar risk, but better tax treatment.