turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- Amortization Schedule Using Array?

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

01-06-2014 08:44 PM

Help!

I am trying to calculate the individual principal and interest cash flows associated with a fixed rate loan amortization schedule. In the past, I’ve seen it done using the ARRAY function in SAS so each of the cash flows throughout the life of the loan can be displayed.

The problem I am running into is that in order to calculate the amortization schedule, you need to reference the each "previous period” value of the ARRAY to calculate the new value (e.g. you need the outstanding principal balance after payment from month 3 to calculate the financials for month 4, etc.). I am definitely an amateur coder, so bear with me. (note: this code does not run, but I figured it's my best start).

I've seen this done before, so I know there is a way. I can't remember how it went though, so not sure how far off I am.

Predefined variables:

-amf (amount borrowed)

term (term of the loan)

rate (interest rate on loan),

pmt (monthly principal and interest payment)

My stab at the coding:

Array loan_bal {72};

Do i = 1 to 72;

Where** loan_bal{i}**=0 then **prin_bal{i}**=amf; (in other words, at time zero, the outstanding loan balance is the amount borrowed).

** loan_bal{i}** = **loan_bal{i-1}** – ( pmt - (**loan_bal{i-1}** * (rate / 12))); (what i am trying to do here is calculate the next period's outstanding balance, say period 1 for example, by referencing the loan balance from period 0).

Again, not concerned with how to calculate the financials (I know that much), more of how to access the one period lagged value of the outstanding loan balance within the context of an ARRAY (bolded).

Thank you!!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to groovyou8

01-07-2014 01:02 PM

If you have the SAS ETS module available you might want to investigate Proc Loan as it will do amortization schedules.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to groovyou8

01-07-2014 01:25 PM

Using i-1 will access the period lagged value, assuming you're looking at a 72 month loan.

If your code isn't working for some reason, post that code.

EDIT: Also, SAS arrays go from 1 to n, not 0, so there is no 0 value, and you'll need to find a way to deal with that, either by using another variable or considering 1 as 0 and factoring that into your logic.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to groovyou8

01-07-2014 01:54 PM

The MORT function in BASE SAS might also be useful.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to groovyou8

01-07-2014 02:24 PM

data test;

array loan_bal{0:72};

loan_bal{0}=amf; /* initiate debt */

do i=1 to 72;

loan_bal{i}=loan_bal{i-1}-(pmt-(loan_bal{i-1}*(rate/12)));

end;

run;

by explicitely definining the array index starting with 0, you handle start

however, this implementation does not handle the last partial payment nor the possibility that loan_bal{i} goes below 0 if term < 72 months but the "lag to prev value" is there and working. The where statement is in a distinct category from the IF statement as far as implementing logic goes in SAS.

Vincent