Amortization of 500,000 loans

[autoPilot]

I am brand new to this forum and to C++. My task is to find a quick method to calculate the cash flow on nearly 500,000 loans. However, this problem cannot be solved with a simple amortization schedule. 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). Let’s ramp it up a bit, 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 very familiar with Excel/VBA and have created a code ‘loop’ which works flawlessly. Trouble is that Excel doesn't have the capacity to perform these tasks quickly. A recent test of 10,000 loans took nearly 5 minutes.

For starters, I’d like to know:
Is C++ generally used for financial analysis?
Where do I start? (Basics: books, training class, etc.)

Thanks in advance for any and all replies.

[Stewbond]

C++ is good for financial analysis and it can be very fast.

Quick question:
In excel, did you store all of your data in cells? Did you "recalculate" each iteration? Recalculating will cause EVERY cell to be executed and that takes a lot of time, however a nice way to reduce this is to turn off the screen refresh.

I've run monte-carlo simulations in VBA and with 500,000 iterations of a lot of data, I could reduce the time required from 30 min to 2 min by turning off the screen refresh.

[AIa43]

Are some of the rates all the same?
Probably start with C++ for Dummies, Programming for Dummies, etc.
Maybe Google about structs and arrays.

Also, I'm not great at economics or coding, so don't take all of my coding for exactly what to use.

try

#include <iostream.h>
using namespace std;
//I'm a beginner, and I know using namespace std adds a whole lot of size
//to the file, but I think it's easier to use than putting std:: for every output.
class loan {
public: //this makes it available to the rest of the program
int SIZE = 500000;
int resetdate[SIZE]; /* 500000 is the same as the variable SIZE*/
int value[SIZE};
int cap[SIZE];
int floor[SIZE];
int balloondate[500000];
int age[500000];
int interest[500000];
int paidoff[500000];
}

loan copypaste;
//this makes copypaste have every value that loan has
//main program below

void main()
{
for (int i = 0; i < SIZE; i++) //i++ means i = i + 1
//arrays always start from 0; int array[5] would have 5 values: array[0], //array[1], array[2], etc.
{
int data;
char answer;
cout << "Do you want to add to the data? y or n" << endl;
cin >> answer;
if (answer == "y")
{
cout << "Value in Dollars of loan number " << i << " is: " << endl;
cin >> data;
if (loan.value[i] > 0)
{
copypaste.value[i] = loan.value[i];
loan.value[i] = data;
}
else
loan.value[i] = data;
cout << "Cap in Dollars of loan number " << i << "is: " << endl;
cin >> data;
if (loan.cap[i] > 0) {
copypaste.cap[i] = loan.cap[i];
loan.cap[i] = data;
}
else
loan.cap[i] = data;
//See the pattern?
/* Basically,
cout << "(Next thing to enter in) in (percentage, euros, dollars, etc.) of loan number " << i << "is : " << endl;
cin >> data;
if (loan.(Next thing to enter in, or BLAH)[i] > 0)
{
copypaste.BLAH[i] = loan.BLAH[i]
loan.BLAH[i] = data;
}
else
loan.BLAH[i] = data;
and so on.
If you don't need to save the previous data for later, then put
cout << "(Next thing to enter in) in (percentage, euros, dollars, etc.) of loan number " << i << "is : " << endl;
cin >> data;
if (loan.(Next thing to enter in, or BLAH)[i] > 0)
{
copypaste.BLAH[i] = loan.BLAH[i]
loan.BLAH[i] = data;
}
else
loan.BLAH[i] = data;
*/

}
}

{
if ( loan.paidoff[i] > 0)

{
loan.value[i] = loan.value[i] - loan.paidoff[i];
loan.paidoff[i] = 0;

}




}
}

[autoPilot]

thank you for the replies.

Stewbond: my Excel workbook has a code loop. basically it calculates the full 30 years of monthly amortization then pastes the results in another part of my workbook. there are many many calculations to make this happen. the code is set to calculate only within the loop. it is faster when i turn off screen updating. however, there are several hundred cell formulas and excell simply cannot handle this volume quickly.

Aia43: some of the rates are the same, but other loan charactetistics are different. unfortunately your code means nothing to me at the moment. I will hopefully understand it soon!

thanks

[autoPilot]

My best estimate is 18 billion calculations.

500,000 loans, up to 360 month, several calculations for each month due to caps, floors, prepaid princiole, regular principle, interest, etc. Repeat 20 times for every loan.

[JLBorges]

> My best estimate is 18 billion calculations.

If native floating point accuracy is all that is required, 18 billion calculations wouldn't take too much time. (under a minute on the coliru hardware).
http://coliru.stacked-crooked.com/a/bc8e12d9eead6b66

[Stewbond]

With so many different variations in the calculation, I would suggest something where you read a config file containing all of the caps, floor, prepaid principle, regular principle, interest, etc. for each case. You could continue to use excel to generate this file (in CSV perhaps?).

Your C++ program would go through each case in the config file and output a result file (perhaps also in CSV) which you could import and analyze in excel.

CSV is nice because it's very readable by excel and is very easy to parse. But INI would be easy too.

[autoPilot]

Thanks for all the excellent information.

To get me started, can I use Visual Studio Express to run this sort of analysis on my desktop PC? It's free from Microsoft...

I am also reading through the excellent C++ tutorial on this site.

Thanks

[Stewbond]

Yes, you can use Visual Studio Express. Visual Studio will simply compile your .cpp files into a .exe which you can execute to read your config and output your result.

[Avilius]

@AIa43

That code isn't efficient. It'll probably crash due to that structure/class requiring all that memory to be allocated at runtime. You should *stream* data instead of loading it all at once.