Fast Formulas #1: Average Life of Mortgage (as Scheduled)

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:

{W=\dfrac {nL-P}{aP} }                                                                                                             (Formula 1.1)

where

W is the weighted average life, in years;
P is the principal amount;
a is the annual interest rate;
n is the term of the mortgage, in months; and
L is the monthly payment.

This provides the WAL in years. We can find the WAL in months by using the monthly rate:

r=\dfrac {a}{12}  is the monthly interest rate.

Then

W=\dfrac {nL-P}{rP}                                                                                                             (Formula 1.2)

with W now representing the weighted average life in months.

But suppose we don’t yet know L, the level payment. The formula for the payment is:

L=rPD_{n}                                                                                                                   (Formula 1.3)

where

d=\dfrac {1} {1+r} , the one month discount factor; and

D_{n}=\dfrac {1} {1-d^{n}} .

You could work out Formula 1.3 by solving for L in

P=Ld+Ld^{2}+Ld^{3}+...+Ld^{n}

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:

W=nD_{n}-\dfrac{1}{r}                                                                                                            (Formula 1.4)

For example, if we had a 30-year mortgage at 6%, then n=360  and r=.005 . We calculate:

d=\dfrac {1}{1.005}=.99502 ;
d^{n}=.99502^{360}=.16604 ;
D_{n}=\dfrac {1}{1-.16604}=1.19910 ; and
W=360 \times 1.19910 - \dfrac {1}{.005}=231.68 months.

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

Advertisements
This entry was posted in Fast Formulas, Math and tagged , , , , , , . Bookmark the permalink.

9 Responses to Fast Formulas #1: Average Life of Mortgage (as Scheduled)

  1. chunlee says:

    Win, thanks.

    Like

  2. Win Smith says:

    You’re welcome Chun Lee. I hope this proves useful for you.

    Like

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

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

  5. Pingback: Fast Formulas #1: New Insight into an Old Formula | The Well-Tempered Spreadsheet

  6. Pingback: Fast Formulas #6: Finding the Time | The Well-Tempered Spreadsheet

  7. Jayanthi Sankaran says:

    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!

    Like

  8. Mohammed Mustafa says:

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

    Like

    • Win Smith says:

      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

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s