Puzzle #3 asked for the weighted average life (“WAL”) of the principal payments for a 30-year mortgage that is cut short by a balloon payment at 15 years. The following information was provided:

$100,000 Principal;

5% Interest Rate;

$536.82 Level Monthly Payment for 30-Year Level Amortization;

18.65 year WAL for 30-Year Level Amortization;

$790.79 Level Monthly Payment for 15-Year Level Amortization; and

8.47 year WAL for 15-Year Level Amortization.

The answer to Puzzle #3 was to be found with just a few basic calculations. My answer used seven operations (/ * + * – / *):

( (536.82/790.72)*100,000+180*536.82 – 100,000 )/(100,000*.05) = 12.90 years

The key to my solution was figuring out the sizeof the balloon payment after 15 years. Let’s think about the last 15 years of the original 30-year mortgage. Since we are given $790.72 as the level payment for a 15-year mortgage, we know that if the payment in the last 15 years were $790.72, we could amortize $100,000 over that period. But the monthly payment on our mortgage is only $536.82, so the amount we can amortize in the last 15 years must be in proportion to the smaller payment. We can find it using the ratio 536.82/790.72 = .6789002. Multiplying this factor by $100,000 gives $67,890.02 as the amount that could have amortized in the last 15 years. So $67,890.02 is the outstanding balance after 15 years, and so it the balloon payment.

We then add the first 15 years of scheduled payments (180*536.82) to get total payments. Then we subtract the total principal to get total interest. Dividing total interest by annual interest (100,000*.05), we get the correct average life.

My solution used seven basic operations and eliminated the need to build a big amortization schedule. I thought that was pretty good.

**A Better Solution**

As you can see in the comments to Puzzle #3, Ashwani Singh made several attempts to solve the puzzle, but while his solutions were clever they were more complicated than I wanted. I challenged him to find a solution in seven steps, but then I didn’t hear back from him. I assumed he had better things to do and was moving on. But after about a week, Ashwani beat my solution by using just three operations! His formula is

18.65 – (536.82/790.79)*8.47 = 12.90 years.

I was stunned. There is no trace of the balloon payment in Ashwani’s formula. How can he ignore the balloon payment and still get the correct average life?

It wasn’t hard to show that Ashwani’s solution is mathematically equivalent to my solution, but it took me more time to find an intuitive explanation.

We are seeking the average life of a stream of principal payments consisting of two parts: 15 years of schedule principal payments, and a balloon payment. We could also construct this stream out of three parts, which will be illustrated in the charts below (these are distorted since monthly payments are aggregated on an annual basis).

Part A is the stream of principal payments for the full 30-year mortgage. The total principal is $100,000, and its weighted average life is 18.65 years.

Part B is the stream of principal payments over the last 15 years of the 30-year mortgage. The total principal of Part B is $67,890.02. The average life of Part B would be the 8.47 years of a 15-year amortization, but Part B doesn’t begin until 15 years from now, so we must shift the WAL by 15 years: 15+8.47 = 23.47 years.

Finally, Part C is simply the balloon payment. The principal is $67,890.02 and the WAL of the single payment is 15 years.

To construct the required principal payment stream, we must remove the last 15 years of payments from the 30-year amortization, and then add in the balloon. So A – B + C gives the required stream of payments:

We can use A – B + C to calculate the required WAL, as long as we weight each part by its total principal. The WALs of A, B, and C are 18.65, 15+8.47=23.47, and 15, respectively. The respective principal totals are $100,000, $67,890.02 and $67,890.02. So the numerator for our WAL calculation is:

** num** = $100,000*18.65 – $67,890.02 (15+8.47) + $67,890.02*15;

and the denominator is:

** denom** = $100,000 – $67,890.02 + $67,890.02 = $100,000.

We could also write the numerator as:

** num** = $100,000*18.65 – $67,890.02*8.47 + ($67,890.02-$67,890.02)*15; so

**= $100,000*18.65 – $67,890.02*8.47.**

*num*This is the secret to Ashwani’s formula. The 15 year shift in the WAL of Part B equals the 15 year WAL of the balloon payment. Since Part B and Part C have the same total principal, they are weighted the same and cancel each other out in the numerator. So we don’t even need to represent the balloon payment in the formula.

To conclude, we divide ** num** by

**and get:**

*denom*WAL = 18.65 – .678902*8.47.

Recall that we originally found the factor .678902 by taking the ratio of the payments 536.82/790.72. So we have Ashwani’s formula:

WAL = 18.65 – 536.82/790.72*8.47.

**Generalizing Ashwani’s Formula**

In general, for any mortgage of a given size and interest rate, let

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

*be the monthly level payment of that n-year mortgage;*

**Ln***be the WAL for that mortgage;*

**Wn***be the number of years until the balloon payment.*

**m***=*

**q***–*

**n***be the number of years in the original mortgage term past the balooon payment;*

**m***be the monthly level payment of a q-year mortgage; and*

**Lq***be the WAL for a q-year mortgage.*

**Wq**Then:

* WAL* =

*– (*

**Wn***/*

**Ln***) **

**Lq***.*

**Wq**I have never seen this formula before. As far as I know, Ashwani has come up with something new.

**Other Puzzles:** Puzzle #1, Puzzle #2, Puzzle#4

Copyright 2011. All rights reserved.

Pingback: Puzzle #3 | The Well-Tempered Spreadsheet

Beautiful explanation Win, am sure even I couldn’t have proved my solution that good.

Apologies for not being regular to the blog. You guessed it right, I had been busy with my admission process in LBS for this fall.

I’ll try to be regular now on.

LikeLike

Thank you Ashawani! If you have time, would you share with us how you came up with your solution? Was your original reasoning as I described above, or did you get there another way?

And good luck with LBS!

Best,

Win

LikeLike

Pingback: Fast Formulas #2: Average Life of Mortgage (Amortizing with Balloon) | The Well-Tempered Spreadsheet

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