topic Re: Q regarding amortization of 500,000 loans in SAS Procedures

Q regarding amortization of 500,000 loans

auto_pilot — Mon, 19 Jan 2015 15:49:15 GMT

I am new to the community and interested SAS capabilities. I would like to run multiple amortization schedules on approximately 500,000 loans. There are various loan types: commercial, residential, fixed rate, floating, balloons, auto, etc. Important output needs:

+ principal and interest payments monthly

+ Must be able to include borrower prepayments IE: 10% of each loan will prepay annually

+ Need present value of cash flow for each loan based on a discount rate that is different from the borrower loan rate

+ Need to run the analysis multiple times (based on different prepay speeds and discount rates).

+ Need to group monthly results into categories IE: Mortgage loan totals, car loan totals, etc

By my rough estimate, the project includes 5 to 9 billion calculations (principal, interest and prepayments monthly).

I am currently using Excel on a smaller scale, but it can't handle this large data set and number of calculations.

I have never used SAS and would like some advice:

+ is SAS a good tool to address this project?

+ can I run it on a desktop PC?

+ Anything else I should consider?

Thanks in advance for all replies.

Jim

Re: Q regarding amortization of 500,000 loans

RW9 — Mon, 19 Jan 2015 16:21:07 GMT

Well, finance is not my field, so the specifics there are for other people. Yes, pretty much anything is better than Excel.

SAS should be able to handle your data, it may take a fairly large amount of time as data scales up, so look at optimization, and ensure you have plenty of memory.

Yes, SAS can install locally.

Consider blocking your data, i.e. break it up into distinct groups, maybe on load type. So create smaller datasets. Also when starting work on a subset of data, i.e. maybe the first 100 records or something to get your code working, then remove the restriction. Don't try to work with large amount of data straight off. Also, maybe consider a relational approach, maybe have discount rates in one table for instance.

Re: Q regarding amortization of 500,000 loans

auto_pilot — Mon, 19 Jan 2015 18:18:06 GMT

Thank you for your prompt reply. Unfortunately, I am unable to block the data up (group) as you suggested. I do that now in excel. The whole purpose of looking for an alternative is to run each line item individually. Your comment "... it may take a farily large amount of time as data scales up..." has me concerned. Speed is critical.

The cost of SAS is holding me back... I have no idea if the capability and speed are sufficient and don't want to spend thousands of dollars plus many hours to figure it out!

Would appreciate any other comments. Thanks

Jim

Re: Q regarding amortization of 500,000 loans

Reeza — Mon, 19 Jan 2015 18:28:48 GMT

SAS can handle it, it will run on your desktop as well, with speed dependent on your processing power.

SAS processes data line by line so it's unlikely to run out of memory with your particular process but it may take longer.

If you don't want to do it, consider hiring a consultant to do it for you, if it's a one time consideration. Given the parameters here, its a simulation and should be less than the licensing cost of SAS.

You could create a Proof of Concept (PoC) using SAS University Edition (Free Statistical Software, SAS University Edition | SAS) or having a consultant generate a PoC for you as well. SAS UE is way too slow to actually process this for all your data but you could develop a Proof of Concept. The benefit is the program is likely to be the same for your full data.

Other things to consider are the SAS financial functions. Can any of them simplify the process for you?

Slightly outdated but here's a reference to the SAS Financial functions.

SAS(R) 9.2 Language Reference: Dictionary, Fourth Edition

When writing your programs you'll want to make sure you only keep the data you want at the end rather than every single table some times.

Hard to suggest more without exact details of your calculation.

Re: Q regarding amortization of 500,000 loans

ballardw — Tue, 20 Jan 2015 18:23:34 GMT

Proc LOAN a part of the SAS ETS module may be what you need.

I would take this question to the SAS Sales people, they'll want a client and can be very helpful with requirements. If you could provide an example dataset I suspect they would be very helpful.

I know that when I bought my house I ran quite a few what if situations on interest rates, prepayment and amortization schedules through Proc Loan. I don't know enough finance specific stuff but SAS/ETS(R) 9.3 User's Guide may give you more information about possibilities.