The weighted average life (“WAL”) of a mortgage is the average time until principal is repaid. It is also the average amount of time that the principal is outstanding. Recall that for a standard fixed-rate mortgage with level payments, the principal component of each monthly payment is different. We assume for now that all payments will be made as scheduled.
The usual way to find the WAL of a mortgage is to build an amortization schedule, multiply each principal payment by the corresponding month number, take the total of these products, and then divide by the total principal amount. For a 30-year mortgage, this would involve 360 rows of monthly calculations. But there is a much easier way. As we saw in the solution to Puzzle #1 (see Puzzle #1: Epilogue), the WAL for a standard mortgage can be found with the simple formula:
where
This provides the WAL in years. We can find the WAL in months by using the monthly rate:
Then
with
But suppose we don’t yet know
where
You could work out Formula 1.3 by solving for
with the shortcut formula for the sum of a geometric series (Formula 4.4 from Fast Formulas #4).
Now substitute Formula 1.3 into Formula 1.2 to find that:
For example, if we had a 30-year mortgage at 6%, then 
(Warning: I limited the number of digits displayed above but you should not limit the precision of your calculations).
Formula 1.4 gives a fast way to find the average life without setting up an amortization schedule or using special software. The formula doesn’t use the principal amount of the loan. It is a function of only the term and the interest rate, and it can help you understand how these two variables affect the average life.
Formula 1.1 is fairly well-known (see Wikipedia), Formula 1.2 is just a variation on Formula 1.1, and Formula 1.3 is a variation on a standard formula. But I haven’t seen Formula 1.4 anywhere. Still, it is so simple and easy to derive that I doubt that it’s original. Please let me know if you’ve seen it before.
Copyright 2011, 2013. All rights reserved.
Revised February 15, 2013 with LaTeX math formatting and minor changes to the text.
The Well-Tempered Spreadsheet 
Win, thanks.
LikeLike
You’re welcome Chun Lee. I hope this proves useful for you.
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
Pingback: Fast Formulas #1: New Insight into an Old Formula | The Well-Tempered Spreadsheet
Pingback: Fast Formulas #6: Finding the Time | The Well-Tempered Spreadsheet
Thank you so much for helping me calculate the weighted average life of a mortgage bond. I was struggling trying to build my own spreadsheet in Excel 7.0. I will most definitely try building my spreadsheet in Excel 16. You have a great site combining financial modelling with programming…and math…great help!
LikeLike
Gents,
how to calculate the weighted average life if we have a pool of loans with diversified terms
– some of the loans on monthly basis without balloon payments (24, 36, 48, 60 month).
– some of the loans on monthly basis with balloon payments (24, 36, 48, 60 month).
– some of the loans the customers will pay on yearly basis (1, 2 or 3 years).
LikeLike
Hi Mohammed, with your pool I would probably concentrate on modelling and aggregating all the cash flows, and then calculating weighted average life. You could calculate average life with the formula for each kind of loan, and then weight the averages by par amount. However, I suspect any errors in implementing this approach would be harder to catch than if you ran out the cash flows. Please let me know if this is helpful or if you have other questions. Thanks, Win
LikeLike