[HW 2.1a] About Loans

1a. Bond

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:

  1. 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)avglife
  2. Using the shorthand approach described above
  3. 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.
  4. Sumproduct is a very useful excel function.  Use this opportunity to calculate the average life a fourth way, using the =sumproduct function



  1. 1.
    Just to clarify about interest rate payments:
    1a. 9% annually
    1b. 8.9% annually
    1c. 8.8% annually
    1d. 10% annually

    Then i have to divide them by # of payments per year (12 or 4 or 2)

    2. For zero coupon – do we assume we pay 2 payments annually?
    Thank you!


  2. correct – For the zero coupon, assume two compounds per year


  3. for the zero coupon, when you assume two compounds per year, how is that different from say 1a where you have semi annual interest payments.

    investopedia says that it is form of commercial financing in which regular interest and principal payments are deferred until maturity, rather than paid over the course of the loan. While the coupon rate on such a mortgage is technically zero because there are no regular coupon or interest payments, interest accrues and is rolled into the principal amount at maturity.

    so with that in mind, how does the two compounds per year come into play?


    1. In one case the interest is paid in the current period. In the other case, the interest is not paid, but it accrues and is only paid with all the other interest at the very end.


  4. I was reading that a zero-coupon loan (or strip-loan) is basically like a government bond. Is that correct?


  5. The government has many different types of bonds outstanding.

    In a zero coupon, the bond does not pays interest at all until the very end.
    If you ever received a US savings bond, which often look something like this:

    these are typically zero coupon. When I was a kid, I had a relative who would give me these for each birthday.


  6. bsauer2015 · · Reply

    Would you like us to calculate the Average Lives of 1.a; 1.b; 1.c; and 1.d using all different ways in each example ?


    1. “Using the ***level principal loan*** above, calculate the average life in the following ways:”


  7. When writing a VBA for the Average Life, how do we express interest rate? There is no “As Percentage”. I have tried “As Decimal” however, for some reason my computer does not have “as decimal”.


    1. The answer will be a number of years, not a percentage


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: