Loan Pool Named Formulas, Without An Amortization Schedule

In a recent post I showed math formulas for a pool of mortgages that could calculate any amount, for any month, without an amortization schedule. This post and spreadsheet takes these formulas one step further and creates names for the formulas. After entering a description of the loan pool:

Enter the month, in the future, that you want to get the values for:

Then pick any cell on any sheet and type in the name of the value you want, and you get the value for the above month. For example, using the 9th month chosen above, entering “=Defaults” gives the amount of defaults for that month ($3,759.69). Here are the named formulas:

The formulas that make up these names looks like this:

The input names looks like this:

In addition there is a sheet called “Proof” that is an amortization schedule, made up from the previous description of the pool that was entered. A lookup of the month chosen from the schedule, along with the row that uses the named formulas, match. This shows the named formulas are correct.

Download “Names


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. Dear Pistulka

    I was searching for ABS techniques and found your website: It is really awesome and helps a lot.

    I am a analyst in China. ABS is booming here. I do a lot cash flow modeling work for mortgage, auto-loan and consumer loan. I’d like to know the standard/best practice of ABS in America. I am really interested in how default/prepayment rate is estimated from historical static or dynamic data and how default/prepayment rate is translated into adjusted cash flow.

    It would be nice to see some official document (not sure if exists) on ABS modeling. I searched for a lot but hardly found anything solid (except your website). Do you happen to have any?

    Thank you so much!


    Tom Si

    PS: I tried to sent you an email but is unreachable by email from China.

    1. Hi,
      Thank you for your comment.
      I am not sure if you are referring to ABS as “Asset-Backed-Securities”, or ABS as “Absolute Prepayment Speed”. I don’t have any real hands-on Absolute Prepayment Speed experience, other than what I have on my blog.
      If you enter CPR in the search box on my blog, you will find dozens of spreadsheets on the CPR, PSA, CDR, and SDA prepayment methods and how cash flows are translated into cash flow.
      You will not find much documentation on Absolute Prepayment Speed on the internet. I tried to find information myself to test the ABS spreadsheet example and found none. Sorry that I can’t be of more help on Absolute Prepayment Speed.

  2. I am from india, i regularly follow your blog. I have one query regarding amortisation formula. How to claculate no. Of payment ( NPER formula of excel) by using maths without using log?

Leave a Reply

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