Each 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 with stable 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 is paid semi-annually on June 2 and December 2 (i.e. $1.75 per $100). Set up a column of dates 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? (Guess and check)
c. Check your answer with the XNPV function. (Put a 0 cash flow for June 2, 2015 to hack the 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 stable dividend supported by collecting rent on grocery stores.
a. What is the current dividend (per share) of Choice Properties REIT? How often do they pay?
b. Assuming the dividend never changes, set up a cash flow table for the perpetual dividend and discount the cash flows (back to June 2 again). How do you deal with infinity here? Find a discount rate to get the present value close to the current trading price. Compare to the "dividend yield": annual dividend divided by price.
c. Reprogram your model to include a growth rate. Assume 3% growth (rent increases and development). 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 the textbook. Do the textbook formulas work?
e. Interest rate risk. Suppose the Fed raises rates more than expected and all discount rates jump by 1%. What's the percentage price loss on
i. The Ontario long bond
ii. 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 idea https://papers.ssrn.com/sol3/papers.cfm?abstract_id=522722
b. 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 capital budgeting decisions. Explain how they are connected and outline how to give meaning to 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 fixing it, that salvage value is constant. It costs $5,000/year to maintain and fuel the car. Elon Musk just tweeted this: https://twitter.com/elonmusk/status/639171519197777920 The 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 initial salvage 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 could estimate 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-year annual 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 here https://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 you can compute the yield using an IRR (and check it with the YIELD function). Use COUPNCD to get the first date in the cash flow table. Convention is yields are expressed as 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% on capital gains (check this). Assume your client is in that bracket and make a new column of after tax cash flows. On the first coupon, you don't pay tax on the accrued that you paid. 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 an alternative with similar risk, but better tax treatment.