Your client, Calpers, will be buying a $40 million asset that pays a annual yield (i.e. coupon rate, coupon yield, cap rate, unleveraged yield) that is 8% of the purchase price. Your friendly banker has agreed to put up 80% of the purchase price and charge you a 6% interest rate, compounded annually, on a 20 year loan that is non-amortizing (i.e. interest only) and carries no other fees. Assume that the asset is sold after 20 years for the same price that it was purchased for. Since Calpers is tax exempt, you don’t have to consider the impact of taxes.
What is the unleveraged project IRR? (Hint: make sure your model shows that it’s 8% using XIRR)
What is the IRR of the debt? (Hint: make sure your model shows that it’s 6% using XIRR)
Before calculating anything, what would you think would be the IRR of the equity?
Now, calculate the IRR of the equity.
b. Sensitivity Table with multiple factors
Using a single data table and the model from question 1, show the sensitivity of the three IRRs to the impact of various:
- Coupons at 5%, 6%, 7%, 8%, and 9%
- Borrowing rates of 0%, 2%, 4%, 6%, 8%, and 10%
- Percent Borrowed of 0%, 20%, 40%, 60%, 80%, 90%, 95%, and 98%%
What other information should you show in the sensitivity table to make it useful?
What’s really going on in your sensitivity table? Draw a picture or write a paragraph or make a chart but try to explain how the equity IRR moves, in response to the various factors that you looked at. Don’t just turn in a set of numbers. Do you see any underlying formula or relationship? If so, please explain.