Home » Uncategorized » 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

 

5 thoughts on “Loan Pool Named Formulas, Without An Amortization Schedule

  1. Tom Si says:

    Dear Pistulka

    I was searching for ABS techniques and found your website:http://pistulka.com/. 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!

    Best

    Tom Si

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

    1. Don Pistulka Don Pistulka says:

      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. ViP says:

    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?

    1. Don Pistulka Don Pistulka says:

      I don’t know of any accurate formula to calculate NPER directly without using logs. You could use an iteration, but that is not a direct formula. You could estimate the value using the Rule of 72 (see https://en.wikipedia.org/wiki/Rule_of_72).

Leave a Reply

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

*
*