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.
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)) ;
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.
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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.