# 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.

• Definitely a task for R! – vonjd Jun 3 '15 at 13:01
• you should check out Intex – user25064 Aug 2 '15 at 13:26

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.

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.

• We already run analysis of loans with common characteristics as you suggested. For a new opportunity, we have been asked to run all loans individually. I can group them together after running the monthly cash flow on every loan. – jim Feb 4 '15 at 15:06
• Okay. If you have access to SAS I would suggest using it. It should be easy to code, and for basic operations on large datasets SAS is quick (relative to R or VBA). C++ is faster, but given the format of your data, it might be easier to just import the CSV/XLSX into SAS. – dmanuge Feb 4 '15 at 18:00
• I don't have access to SAS. I am currently running groups of loans using Excel. Seeking a method without Excel to calculate amortization for each individual loan. My budget does is not sufficient for SAS. – jim Feb 9 '15 at 14:28
• R or C++ do not require proprietary software to run. They might be your better options. – dmanuge Feb 9 '15 at 14:58