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:

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

be the monthly level payment of that -month mortgage;

be the WAL for that mortgage, in months;

be the number of months until the balloon payment;

be the number of months in the original term past the balloon payment;

be the monthly level payment of a -month mortgage;

be the WAL for a -month mortgage; and

be the WAL with the balloon payment.

Then:

. (*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:

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

, the one month discount factor; and

.

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

; (*Formula 1.3*)

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

. (*Formula 1.4*)

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

** *** *(

*Formula 2.2*)

with

.

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 , , , and . We calculate:

;

;

;

;

;

and

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.*

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

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

LikeLike

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

LikeLike

Please ignore the reference to the principal amount. The formula does not require a principal amount.

LikeLike

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?

LikeLike

Daniel, that does sound strange. If you’d like to send me a sanitized copy of the analysis, I would be happy to take a look. Thanks, Win

LikeLike

Thanks, How do I send it over?

LikeLike

I sent you an email with my contact information. I hope I can help figure out what’s going on.

LikeLike

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

Please cite an excel example

LikeLike

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

LikeLike

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

LikeLike