Amortization Schedule Using Array?

[groovyou8]

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!!

[ballardw]

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

[Reeza]

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.

[SASKiwi]

The MORT function in BASE SAS might also be useful.

http://support.sas.com/documentation/cdl/en/lefunctionsref/63354/HTML/default/viewer.htm#p1lu2y2jwy3...

[Vince28_Statcan]

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