Finance-FNCE 451- Fall 2015 Assignment 1

| January 30, 2017

FNCE 451- Fall 2015
Assignment 1

Due October 1, 2015

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
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
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:

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
(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.

Get a 30 % discount on an order above $ 50
Use the following coupon code:
Order your essay today and save 30% with the discount code: COCONUTOrder Now
Positive SSL