# Finance-FNCE 451- Fall 2015 Assignment 1

Question

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

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

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

Model 3, our smaller and lower cost sedan will start production in about 2 years. Fully operational Gigafactory needed.

— Elon Musk (@elonmusk) September 2, 2015

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

http://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.

**30 %**discount on an order above

**$ 50**

Use the following coupon code:

COCONUT