Fast Formulas #2: Average Life of Mortgage (Amortizing with Balloon)

Fast Formulas #1: Average Life of Mortgage (as Scheduled) showed a quick way to calculate the weighted average life (“WAL”) of a fixed-rate mortgage that pays as originally scheduled.  I found this formula (which is probably not original) by substituting the standard  level payment formula into the general solution to Puzzle #1: Mortgage Average Life.

Puzzle #3: Mortgage with Balloon is similar to Puzzle #1 but asks for a quick way to find the average life of a mortgage that amortizes on schedule for some period of time, but is then paid off early with a balloon payment.  I thought I had a good compact solution, but Ashwani Singh found a better one, which is discussed in Puzzle #3: A Better Answer.

Here’s the general formula for an amortizing mortgage with a balloon, based on Ashwani’s solution.  Let:

n be the original term of the fully amortizing mortgage, in months;

L _{n} be the monthly level payment of that n-month mortgage;

W _{n} be the WAL for that mortgage, in months;

m be the number of months until the balloon payment;

q=n-m be the number of months in the original term past the balloon payment;

L _{q} be the monthly level payment of a q-month mortgage;

W _{q} be the WAL for a q-month mortgage; and

W_{balloon} be the WAL with the balloon payment.

Then:

W_{balloon}=W_{n}-\dfrac {L_{n}}{L_{q}}W_{q}.                                                                                         (Formula 2.1)

(Note that here I measure time in months instead of in years, as I did in Puzzle #3: A Better Answer.)

But suppose we don’t already know the average lives and level payments needed for Formula 2.1.  Let’s see what happens if we substitute the respective formulas into Formula 2.1.

First, we need to define:

r, the monthly interest rate (1/12 of the annual rate);

d=\dfrac {1}{1+r}, the one month discount factor; and

D_{n}=\dfrac{1}{1-d^{n}}.

We can use two formulas from Fast Formulas #1.  The formula for a level monthly payment is:

L=rPD_{n};                                                                                                               (Formula 1.3)

and the average life for a mortgage without an early payoff is:

W=nD_{n}-\dfrac{1}{r}.                                                                                                        (Formula 1.4)

Now substitute Formulas 1.4 and 1.3 into Formula 2.1.  With a little work you will find that:

W_{balloon}=D_{n} \left( m+\dfrac {1}{rD_{q}} \right)-\dfrac {1}{r}                                                                            (Formula 2.2)

with

D_{q}=\dfrac{1}{1-d^{q}}.

Note that Formula 2.2, like Formula 1.4, does not require the principal amount to calculate an average life.

Let’s use Formula 2.2 in an example.  Suppose we had a 30-year mortgage at 6%, with a balloon payoff at ten years.  Then n=360, m=120, q=240, and r=0.005. We calculate:

d=\dfrac {1}{1.005}=.99502;

d^{n}=.99502^{360}=0.16604;

D_{n}=\dfrac {1}{1-0.16604}=1.1991;

d^{q}=.99502^{240}=0.3021;

D_{q}=\dfrac {1}{1-0.3021}=1.4328;

and

W_{balloon}=1.1991 \left( 120+\dfrac {1}{0.005 \times 1.4328} \right)-\dfrac {1}{0.005}=111.26 months.  This is only 9 months before the balloon payment, which dominates the average.

I think that Formula 2.2 might be an original way to find the average life of an amortizing mortgage with a balloon.  I want to thank Ashwani again for the elegant contribution which led to this formula.

Copyright 2011, 2013.  All rights reserved.

Revised February 15, 2013 with LaTeX math formatting and minor changes to the text.

Advertisements
This entry was posted in Fast Formulas, Math and tagged , , , , , , . Bookmark the permalink.

12 Responses to Fast Formulas #2: Average Life of Mortgage (Amortizing with Balloon)

  1. Pingback: Fast Formulas #3: Pool Average Life with CPR Prepayments | The Well-Tempered Spreadsheet

  2. Daniel says:

    This was extremely helpful. However, While this helps me to determine the WAL, Balloon at the outset of the mortgatge, how do I apply monthly seasoning, so that I can determine the WAL, Balloon mid-term (I am trying to apply this for a Yield Maintenance calculation).

    Do I simply subtract seasoning from both the term and amortization?

    Thanks in advance,

    Daniel

    Like

    • Win Smith says:

      Daniel,

      I’m glad the post was helpful. Yes, you can subtract the seasoning from both the term and the amortization, but be sure to use the current outstanding balance as the beginning principal amount. This assumes that all payments have been made as scheduled. It would be good if you have a way to check your work with another method.

      Best,

      Win

      Like

  3. Daniel says:

    Thanks Win, I was actually using a principal and payment method to apply the seasoning. At first it looked like it worked. But when I tested the model to reflect different terms (longer and shorter amortizations relative to longer or shorter “term”). I am finding that after calculating the WAL to include the greater “hangout,” (240 month equivalent hangout vs 120 month) the Yield Maintenance premium associated with a longer hangout is smaller because of a greater discount rate…the longer the maturation of the correlated treasury the greater the yield/reinvestment rate in a “normal” market. Is it a mathematical anomaly that a longer hangout/balloon should have a smaller premium, or am I calculating the WAL incorrectly?

    Like

  4. Pingback: Fast Formulas #3: The Derivation | The Well-Tempered Spreadsheet

  5. Mohammed says:

    Please cite an excel example

    Like

  6. Tianhang says:

    What if you you have a 12-month interest only period before the amortization?

    Like

    • Win Smith says:

      Tianhang,

      Thank you for your question. All you have to do is measure n and m from the end of the interest-only period, and then add 12 to the calculation.

      Hope that helps. Please let me know if you have any other questions.

      – Win

      Like

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