Fast Formulas #3: The Spreadsheet

Fast Formulas #3: Pool Average Life with CPR Prepayments showed a shortcut for finding the weighted average life (“WAL”) of a homogenous pool of loans assuming a constant prepayment rate.  The shortcut, Formula 3.1, takes just a few steps to calculate and it eliminates the need for laborious cash flow calculations.  Some of the commenters on the post asked me to validate the formula and explain the derivation. I will answer those requests with a series of two or three articles.  The first of these should be ready soon.

Last week, Ken wrote that he has had some trouble following the formula and asked for a spreadsheet to show how it works. I do have such a spreadsheet. The model is available to the  subscribers of this blog by clicking on the image below and providing the password, which you can get by sending an email to inquiries@winanalytics.com (first be sure to subscribe at the upper right corner of the blog).  Please identify yourself and your organization, and let me know why you are interested in the spreadsheet.

This screenshot is from the first sheet of the model, which summarizes the inputs and the calculations. The loan terms are taken from the example in the original Fast Formulas #3. You can see how the shortcut formula works, and compare the results to a traditional WAL calculation, based on a cash flow projection from the second tab. In this example, the two calculations match to twelve decimal places (the error is less than 10^-12).

You can test the formula by playing with the loan terms and the prepayment rate.  You will see that the shortcut  formula matches the long calculation, except in some special cases such as zero interest rates or zero prepayment speeds.

The model’s second tab includes a standard amortization schedule and a prepayment projection.  I used an Excel table for these cash flows. Excel tables (not too be confused with data tables) make blocks of calculations more reliable. Tables can help you avoid some the problems with column totals that I described in Good Fences Make Good Spreadsheets, Part 1 and Part 2; Tables vs. Fences; and A Big, Avoidable Mistake.

Tables work best for calculations if the formulas in each column are completely consistent (you get a warning if they aren’t consistent). I made sure the formulas were generalized enough to handle both the cutoff date (Month 0) and the later cash flows.

Acknowledgment: I adapted a formula from the Excel and Access Blog to generate the month numbers for the cash flow table.

Disclaimer: This spreadsheet model is for illustrative and educational purposes only, and permission is not granted for commercial use of any kind.  There is no guarantee of the results of this spreadsheet model or its accuracy, completeness, suitability, or validity.

Copyright 2012.  All rights reserved.

Note: I revised the form of Formula 3.1 on February 16, 2013, but it is still equivalent to the formula used by the spreadsheet model.

Email requirement revised August 23, 2015.