topic Re: Loan amortization problem in SAS Programming

Loan amortization problem

greg6363 — Sat, 17 Aug 2019 00:21:53 GMT

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.

Re: Loan amortization problem

Shmuel — Sat, 17 Aug 2019 01:45:47 GMT

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)) ;

Re: Loan amortization problem

Reeza — Sat, 17 Aug 2019 19:13:47 GMT

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.

Re: Loan amortization problem

hashman — Sat, 17 Aug 2019 19:41:23 GMT

@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.

Re: Loan amortization problem

greg6363 — Wed, 18 Sep 2019 16:32:48 GMT

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

Re: Loan amortization problem

Reeza — Wed, 18 Sep 2019 17:04:15 GMT

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