You are a financial analyst at AirBnB. You need to raise 10 year money. At 10am, you attend a meeting with AirBnB’s investment banker, Morgan Stanley, in which they are encouraging Uber to issue a bond. They think you’ll be able to pay a 9% coupon. The bond will have semi-annual interest payments, with all principal repaid only at the end of 10 years.
1b. Loan with Mortgage Style Amortization
Wells Fargo is your lead commercial bank. That’s where you the company has its checking account. At 1pm, you attend a meeting with them, in which they say they would like to give Uber a loan for the same amount at an 8.9% interest rate, but the loan will amortize monthly on a mortgage style basis. They will syndicate the loan.
1c. Loan with Level Principal Amortization
At 3pm, you sit in on a call with the nice people at AIG, a large insurance company. They offer a slightly different debt structure. Their loan will carry an 8.8% interest rate, compounded quarterly. The loan will be amortized on a level principal basis, with quarterly payments.
1d. Zero Coupon Loan
Lastly, at 4:30pm, you sit in on another call, this time with Berkshire Hathaway. They offer a 10% interest rate on a zero coupon structure, over the same term.
Model out each structure using an initial drawdown date of July 1, 2015. What is the total interest in each case? What is the effective borrowing rate on each loan?
2. Average Life
One way to calculate the average life of a debt instrument is to divide the Total Interest accrued over the life of the loan by the “Annual Interest”, which is the simply the loan amount times the interest rate. So, for example, a 10 year $100,000 mortgage with annual payments over ten years will accrue $42,377 in interest. The average life would be 6.05 years, which is $42,377 divided by $7,000 (which is $100,000 * 7%).
Using the level principal loan above, calculate the average life in the following ways:
- Longhand, based on the example at: http://www.investopedia.com/terms/a/averagelife.asp (Average life = 400 / 200 = 2 years. This bond would have an average life of two years against its maturity of four years)
- Using the shorthand approach described above
- Using the shorthand approach as a Visual Basic Function, where =averagelife(loan amount, total interest, interest rate). This is a trivial step. The purpose is just to add it to your function list so that you can easily refer to it in the future.
- Sumproduct is a very useful excel function. Use this opportunity to calculate the average life a fourth way, using the =sumproduct function