Average Life VBA Function For MBS

This is the fourth  in a series of VBA, User Defined Functions for Mortgage Backed Securities (MBS). The other three are, solve for price given yield , yield given price and Macaulay Duration . Actually the seventh UDF, if you include the functions solving for the market value of Servicing, IO, and PO). As was true of the previous three functions, it allows for a number of option. It also, as before, includes an amortization schedule as a proof of the function’s calculations.

Average Life is the average number of years that each dollar of unpaid principal remains outstanding, Computed at the average time to the receipt of all future principal payments due to be paid.

Neither Servicing Fee nor Loss Severity are asked for in this function. They do not affect average life.

Download: MortgageAvLife.xlsm

Don Pistulka
Don Pistulka

Retired Credit Union CFO - Finance
Background: over 40 years in investments, asset/Liability management, banking, securities trader.
Worked for: California Credit Union, WesCorp, CalFed S&L, Crocker Bank, Carroll McEntee, Federal Home Loan Bank Board (D.C.), Western Asset Management, Security Pacific National Bank.


  1. Hey Don –

    Do you think this would be possible to replicate this function using a PSA curve rather than static CPR? I imagine it would increase the complexity of the code by quite a bit, but thought I’d ask if you’ve given it any thought.


  2. Hi Don – I’m getting an error when I try to download your MortgageAvLife file.

Leave a Reply

Your email address will not be published. Required fields are marked *