Fast Formulas #2: Average Life of Mortgage (Amortizing with Balloon) presented a compact formula for calculating the average life of a mortgage that pays as scheduled until it is paid off with a “balloon” payment before the scheduled end of its term. It occurred to me that it might be possible to build on that formula to find something more significant: a concise formula for the average life of a pool of loans with ongoing prepayments. It turns out that there is such a formula.

Let me begin with some background for those unfamiliar with prepayment models. A simple but widely used model for loan prepayments is “CPR”, which stands for “constant prepayment rate” or “conditional prepayment rate.” A 10% CPR means that if there is a pool of 100 loans at the beginning of the year, by the end of the year 10 of those loans will have been paid off by voluntary prepayments and 90 of the loans will still be outstanding.

We assume that the prepayments occur every month. The monthly prepayment rate is also known as the Single Monthly Mortality (“SMM”). Suppose the monthly rate is 1%. Then 99% of the original loans are still outstanding after a month. After two months, 98.01% (99% of 99%) are still outstanding. After 12 months, 88.64% = .99^{12} of the loans are outstanding, and so the annual prepayment rate, or CPR, is 1 – 88.64% = 11.36%.

The CPR model assumes that the prepayments occur at a constant percentage of the declining population of loans. It ignores influences on borrower behavior from changing interest rates, the age of the loan, season of the year, or other economic or demographic factors. But the CPR model provides a consistent way to evaluate loan cash flows across a range of prepayment assumptions, and it has been used to analyze assets such as mortgages, home equity loans, and student loans.

A key summary statistic for a mortgage-backed or asset-backed security is the projected average life, which can be used to find a comparable U.S. treasury as a yield benchmark. The way to calculate the average life is to project the underlying loan cash flows under an assumed prepayment scenario, a task that demands careful modelling or special software.

But is there a concise formula for the average life of a loan pool that does not require detailed cash flow projections? I found that the answer is yes, under these idealized conditions: the prepayments must follow the CPR model, and the loans in the pool must have homogenous rates and terms.

Let’s assume our pool consists entirely of 30-year mortgages at a 6% interest rate. If there are no prepayments, we could calculate the pool’s average life with Formula 1.4 from Fast Formulas #1: Average Life of Mortgage (as Scheduled):

(Formula 1.4)

months, where

is the loan term, in months;

is the monthly interest rate;

is the monthly discount factor; and

.

But let’s assume that the pool will experience prepayments at a constant 1% monthly rate, which has the annualized equivalent of 11.36% CPR, as we saw above. Assuming perfect knowledge about the future performance of the pool, we can partition the mortgages into 360 classes: Class 1 loans will prepay in one month, Class 2 loans will prepay in two months, and so on. The final class, Class 360, contains the loans that will run to the 360th month without any prepayment.

Consider Class 12, the loans that will prepay one year from now. These are among the loans that will survive at least 11 months, but they are not among those that will survive beyond 12 months. So Class 12’s share of the entire pool is:

, where

is the monthly prepayment rate; and

is the monthly survivorship factor.

For our purposes, we can think of Class 12 as a single loan that amortizes according to a 360-year schedule for 12 months, and is then paid off with a balloon payment. We can use Formula 2.2 from Fast Formulas #2 to calculate the average life for such a loan. This formula was derived from Ashwani Singh’s elegant solution to Puzzle #3: Mortgage Average Life (see Puzzle #3: A Better Answer). Applying Formula 2.2 to Class 12, we have:

** *** *(

*Formula 2.2*)

months, where

is the number of months until the balloon payment; and

.

Class 12’s contribution to the weighted average life of the pool is the product of its share of the pool with its average life: . We could perform this calculation for each of the 360 classes and add the results to get the pool average life. These calculations would be more of a marathon than a sprint, but there is a short cut. With a couple of tricks and a few pages of algebra, I was able to condense the calculations down to this formula:

(Formula 3.1)

In our example, , , , , , and . So:

months.

This calculation matches the result from a detailed monthly cash flow projection.

If there are no prepayments then Formula 3.1 should match Formula 1.4 (the average life for a mortgage without prepayments). But Formula 3.1 blows up if , since divides . Then what about very slow prepayments? As approaches , approaches (to see how this works, look at the binomial expansion of ). Formula 3.1 does converge to Formula 1.4 as approaches .

Perhaps Formula 3.1 could be useful as a way to:

- Estimate the average life of a loan pool;
- Test the reasonability of analysis generated by third-party software; or
- Understand the relationship between CPR and average life.

I think that Formula 3.1 may be original, but please let me know if you have seen it before.

I am very pleased that this formula grew out of the exchanges we’ve had on this blog. I am grateful to Ashwani Singh for his crucial contribution.

Copyright 2011, 2013. All rights reserved.

*Revised July 25, 2011 with the correction of an exponent from 248 to 348, and with a slightly simpler version of Formula 3.1. Further revised on February 16, 2013 with LaTeX math formatting and minor changes to the text.*

*Revised August 31, 2016 with corrected value for . My thanks to Andy Cahill for catching the error.*

**Update**: A spreadsheet implementation of this formula is now available at Fast Formulas #3: The Spreadsheet.

**More Updates:** Fast Formulas #4: Geometric Series (One of the Tricks) and Fast Formulas #5: Quasi-Geometric Series (The Other Trick) explain the tools I used to derive Formula 3.1. The derivation of this formula is given by Fast Formulas #3: The Derivation.

Win,

I haven’t read much on Mortgages but whatever I have read, I haven’t seen this formula anywhere else. Your method looks convincing in its simplicity but I still validated. Will be interesting to see what others have to add.

Cheers!

Ashwani

LikeLike

Thank you Ashwani. I’ve been checking around and so far there has been no sign of this formula, but we’ll see.

Best,

Win

LikeLike

Hi Win,

I would like to apply the Formula 3.1 to calculate the weighted average life of a pool of variable-rate loans. It seems to give plausible answers and matches a cashflow schedule. But it is always nice to see a formula validated! Can I just check there has not been any progress on this? Would it be possible to (briefly) explain the ‘couple of tricks’ used to arrive at Formula 3.1?

Thanks,

Cameron

LikeLike

Thank you Cameron. I will explain the tricks in one or more upcoming posts. Stay tuned!

LikeLike

Cameron, I’ve explained one of the tricks in a new post:

https://welltemperedspreadsheet.wordpress.com/2012/10/18/fast-formulas-4-geometric-series/

Thanks – Win

LikeLike

I am having a hard time following this formula. Is there a way you can put this in an excel spreadsheet so I can see how the formula is working? Thanks.

LikeLike

Ken, I’ve posted a spreadsheet here:

https://welltemperedspreadsheet.wordpress.com/2012/08/21/fast-formulas-3-the-spreadsheet/

Please let me know if you have any questions. Thanks, Win

LikeLike

Yes Ken, I will put up a spreadsheet to show how this works. Please check back later in the week. Thank for your interest.

LikeLike

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

Pingback: Fast Formulas #4: Geometric Series | The Well-Tempered Spreadsheet

Pingback: Fast Formulas #5: Quasi-Geometric Series (The Other Trick) | The Well-Tempered Spreadsheet

I find the above information extremely helpful and it clearly illustrates the impact prepayment speeds have on the WAL. In short term loan markets (auto), rating agencies use ABS in lieu of CPR and wondered if there was a translation formula from CPR to ABS. The ABS definition is somewhat confusing as it is “actual prepayments each month as a percent of original outstanding balance”. I thought ABS was derived from SMM (ABS = SMM/(1*(SMM*(Month -1))); therefore, how is the original outstanding balance even a factor?

LikeLike

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

Thank you Mike. That is a good question. I will look into this when I get a chance.

LikeLike

Ton of thanx! i was preparing for cfa level 2 nd was totally messed up with the formula… but u explained in very simple way

Regards

Pankaj Thakur

LikeLike

Pankaj,

You’re welcome. I’m glad that helped. I didn’t know it would be relevant to CFA 2.

Best,

Win

LikeLike

Win,

I did not see your spreadsheet (no password), but I did register. I was able to put together the formula from the spreadsheet image you showed. Then I validated it on one of my spreadsheet called “MacaulyDuration” and it worked perfectly. I am going to use this formula in an upcoming blog and I will credit you on both the spreadsheet and my blog.

Thank You

LikeLike

Pingback: Mortgage Pool Price and Average Life One Cell Formulas | Excel@CFO

Pingback: Conforming the “Mega” MBS Formula to Street Conventions | Excel@CFO