Loan amortization problem

[greg6363]

Unfortunately, my SAS license does not include PROC LOAN which would solve my dilemma so I have to create a loan amortization schedule manually.  The following code attempts to utilize the loan information (APR, Payment Frequency, Loan Amount) from the sample data set to calculate the payment for each period.

data loan_amort;
set loan_acct_sample;
rate = APR/PmtFreq;
nper = PmtFreq;
pv = LoanAmount;
fv = 0;
calc_pmt = finance('pmt',rate,nper,pv,fv);
run;

When I run this code, the calculated value in the payment field is way too high.

Then I wrote the code to create the amortization schedule table for each account:

data amort_sched;
set loan_amort;
do term = 1 to nper;
int_paid = pv * rate;
prin_paid = abs(calc_pmt) - int_paid;
pv = pv - prin_paid;

tot_int_paid + int_paid;
tot_prin_paid + prin_paid;

output;
end;
run;

While creating the schedule is helpful, all I really need is the last record of the table which displays the total amortized interest paid and the total principal paid for each account. I was trying to suppress the previous observations so I would just have the last record and use the keep command to declare the needed variables.  I'm clearing missing something in my code.

I tried to write the standard amortization formula in the following form:

A = P (r(1+r)^n/(1+r)^n -1)   

A = payment amount per period

P = Loan Amount (Principal)

r = interest rate per period

n = total number of payment periods 

When I run this formula in my code, I get an inflated amount so I have the feeling there is a problem with how SAS is reading

the order of operations.

Any suggestions would be greatly appreciated.  Thanks.

[Shmuel]

The formula A = P (r(1+r)^n/(1+r)^n -1)   should be written as:

A = P * ((r * (1+r)**n) / ((1+r)**n -1)) ;  

[Reeza]

data amort_sched;
set loan_amort;

do term = 1 to nper;
        int_paid = pv * rate;
        prin_paid = abs(calc_pmt) - int_paid;
        pv = pv - prin_paid;
 
        tot_int_paid + int_paid;
        tot_prin_paid + prin_paid;
 
output; *creates an output for each line;
end;

OUTPUT; *creates an output at the end only;

run;

---

@greg6363 wrote:

Unfortunately, my SAS license does not include PROC LOAN which would solve my dilemma so I have to create a loan amortization schedule manually.  The following code attempts to utilize the loan information (APR, Payment Frequency, Loan Amount) from the sample data set to calculate the payment for each period.

data loan_amort;
set loan_acct_sample;
rate = APR/PmtFreq;
nper = PmtFreq;
pv = LoanAmount;
fv = 0;
calc_pmt = finance('pmt',rate,nper,pv,fv);
run;

When I run this code, the calculated value in the payment field is way too high.

Then I wrote the code to create the amortization schedule table for each account:

data amort_sched;
set loan_amort;
do term = 1 to nper;
int_paid = pv * rate;
prin_paid = abs(calc_pmt) - int_paid;
pv = pv - prin_paid;

tot_int_paid + int_paid;
tot_prin_paid + prin_paid;

output;
end;
run;

While creating the schedule is helpful, all I really need is the last record of the table which displays the total amortized interest paid and the total principal paid for each account. I was trying to suppress the previous observations so I would just have the last record and use the keep command to declare the needed variables.  I'm clearing missing something in my code.

I tried to write the standard amortization formula in the following form:

A = P (r(1+r)^n/(1+r)^n -1)   

A = payment amount per period

P = Loan Amount (Principal)

r = interest rate per period

n = total number of payment periods 

When I run this formula in my code, I get an inflated amount so I have the feeling there is a problem with how SAS is reading

the order of operations.

Any suggestions would be greatly appreciated.  Thanks.

---

[hashman]

@greg6363 :

Though you don't have PROC LOAN licensed, Base SAS sports no fewer than 32 various financial functions, among which I'm sure you'll find what you need without having to reinvent the wheel. In your particular case, for example, you may want to take a look at the MORT (i.e. "mortgage") function.

Kind regards

Paul D.

[greg6363]

Our SAS license doesn’t even have the PPMT, IPMT, CUMPRINC or CUMIPMT functions which is why the manual processes.

[Reeza]

What version do you have? Also, the FINANCE function itself does some of those.