2
$\begingroup$

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.

Improve this question
$\endgroup$
2
  • $\begingroup$ Definitely a task for R! $\endgroup$
    – vonjd
    Commented Jun 3, 2015 at 13:01
  • $\begingroup$ you should check out Intex $\endgroup$
    – user25064
    Commented Aug 2, 2015 at 13:26

3 Answers 3

Reset to default
2
$\begingroup$

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.

Improve this answer
$\endgroup$
0
$\begingroup$

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.

Improve this answer
$\endgroup$
4
  • $\begingroup$ 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. $\endgroup$
    – jim
    Commented Feb 4, 2015 at 15:06
  • $\begingroup$ 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. $\endgroup$
    – dmanuge
    Commented Feb 4, 2015 at 18:00
  • $\begingroup$ 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. $\endgroup$
    – jim
    Commented Feb 9, 2015 at 14:28
  • $\begingroup$ R or C++ do not require proprietary software to run. They might be your better options. $\endgroup$
    – dmanuge
    Commented Feb 9, 2015 at 14:58
0
$\begingroup$

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
Improve this answer
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.