Fast Formulas #3: Pool Average Life with CPR Prepayments presented Formula 3.1., a compact formula for the weighted average life of a homogeneous loan pool assuming a constant prepayment rate. I wrote that the formula took a few pages and “a couple of tricks” to work out.

Fast Formulas #3: The Spreadsheet lets you test the shortcut formula against a standard cash flow projection. However, the spreadsheet does not explain why the formula works, nor does it prove that the formula is valid. This is why some readers have asked to see the derivation of Formula 3.1.

I have now written up the full derivation, which you can find here. If wading through a lot of algebra is not your idea of fun, please keep reading for a quick summary.

The key insight behind Formula 3.1 was that the loan pool can be divided into classes based on when the loans are assumed to prepay. All of the loans that prepay at the same time belong to the same class. Each class behaves like a single loan that amortizes on schedule, until a balloon prepayment pays off the balance on the prepayment date.

The average life for any class can be found with Formula 2.2 for an amortizing mortgage with a balloon payment:

The average life of the entire pool can be found by weighting each class’s average life by its share of the whole pool. The share for the th class is , which is based on a prepayment factor raised to the power of .

Adapting Formula 2.2, the average life for the entire pool is

.

I expect most readers are comfortable with notation, but if not, don’t let it bother you. is the capital **S*** in the Greek alphabet. If is some expression that depends on , then the **Sum** of its values for each from 1 to is .

The problem with the previous formula for is that it would require us to loop through 360 rounds of calculation for a 30-year mortgage. Fortunately, all of these calculations are unnecessary because there is a good shortcut.

To find the shortcut, we begin by reworking the formula a little and then breaking it into three parts:

where

,

,

and

.

Let’s look at first. With a little work, we can shape it into a geometric series of the form . From there, we can find a compact form by using Formula 4.4 from Fast Formulas #4: Geometric Series (One of the Tricks).

Next, consider . It is almost a “quasi-geometric” series of the form , so we can apply the shortcut Formula 5.1 from Fast Formulas #5: Quasi-Geometric Series (The Other Trick).

We also rework a little.

Now we can plug compact forms of , and into . The s are gone but the formula is not very elegant. With enough algebra and caffeine, however, we can boil it down to Formula 3.1:

.

*It’s annoying when “” is used as an “E”, as in “MY BIG FAT GRK WEDDING.” What is a “GRSSK WEDDING”?

Copyright 2013. All Rights Reserved.

*Revised July 2, 2013 with a correction to the second formula.*

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

Pingback: Fast Formulas #4: Geometric Series (One of the Tricks) | The Well-Tempered Spreadsheet