Scaling Loan Cash-Flow Models with Aggregation and Parallel Computing

# Q regarding amortization of 500,000 loans


# Q regarding amortization of 500,000 loans












I am brand new to this forum. I asked this question on the main StackOverflow site and it was suggested that I ask here.

My task is to find a method to quickly calculate the monthly cash flow on nearly 500,000 loans. However, this problem cannot be solved with simple amortization schedules. The loans have a variety of attributes like periodic reset dates, caps, floors and balloon dates. Some are variable, some fixed rate and nearly all of them are aged to some degree (months or years). After running the amortization schedules, I need to input different sets of prepayment speed assumptions, then run it all again… 20 more times!

I am currently using Excel on a smaller data-set, but Excel doesn't have the capacity to perform these tasks quickly. A recent test of only 10,000 loans took nearly 5 minutes.

For starters, I’d like to know: Is anyone aware of any existing companies that already do this? Alternatively, If I decide to build something from scratch, what programming language would be most appropriate given the size. My best estimate is between 5 and 10 billion calculations, maybe more.

Thanks in advance for any and all replies.

## Answer by Quantopik (score 2)

https://quant.stackexchange.com/a/18653

Yes you can do that by simply using the `PROC LOAN` already implemented in SAS.

It gives the possibility to take into account ballon dates, floating & fixed interest rates and all you mentioned in the question.

Here you can find the guide to use this procedure.

I suggest to use that instead of other statistical procedure, because SAS provides already implemented functions and in terms of time, you should be able to do that in less than one minute.

If you need some help to understand the procedure, please ping me below.

Hope this help.

## Answer by dmanuge (score 0)

https://quant.stackexchange.com/a/16434

I can't comment, so I'll post here.

Do you need the monthly cash flows at the account-level? Or can you group similar mortgages (age, vintage, rates, etc..) and then apply the amortization schedules?

My suggestion is to use a programming language that you're most comfortable with. If you don't need to apply the calculations at the account-level, most languages should be able to handle the size; it's more a question of your computing power.

Most banks (over $50 billion in assets) perform similar calculations as part of their stress testing programs. They do so for the same reason you are, to test the sensitivity of the mortgage portfolio with respect to changes in prepayment rates.

## Answer by Pythonista anonymous (score 0)

https://quant.stackexchange.com/a/75455

I know the question is old, but it can still be of interest.

##### Replines vs loan-by-loan modelling

One way to reduce the computation time is to create so-called replines to aggregate your portfolio into fewer, representative loans. E.g.

| item | balance | rate | maturity |
| loan 1 | 100 | 4.00% | 6 |
| loan 2 | 300 | 5.00% | 10 |
| repline (weighted avg) | 400 | 4.75% | 9 |

You can do this with clustering algorithms like K-Means, easily available for both R and Python. Remember to scale your data.

##### Loan-by-loan modelling

Even after creating replines, you will still need to amortise your loans. This is something which can be done relatively easily in R, Python or Julia. A few notes:

- Python's implementation of the `pmt` function (moved from numpy to numpy-financial) is very slow. If you rewrite your own, and set it up for scalar values only, it will be much faster. There are discussions on the github for numpy-financial

- This kind of code lends itself to parallelisation quite well; e.g. you have 2,000 loans, you divide them into chunks of 500 and send each to a separate processor