I MANAGED TO SOLVE THE PROBLEM - SEE THE CODE AT THE END OF THE POST
Hi,
Below is the code to prepare loan repayment schedule using proc loan with estimatedcase option, provided by @AMSAS in this topic: Proc loan + estimatedcase - add values from a dataset
From the procedure output, I can read the outstanding balance of the loan after 12 payments (in December 2015 in this example) which is: 933.28. But when I calculate this "manually", using finance function to obtain FV of the loan after 12 payments, I get a different value (940.1). Where is the difference comming from? What is the correct oustanding value of the loan? I trust more the result from the finance function, as I can get similar value using Excel.
data rates;
/* define temporary varible and retain */
/* Note if your periods and rates, exceed the length you will have problems */
length
tempEC $200 ;
retain
tempEC ""
start 2015 ; /* Set loan start year */
/* Read your data */
input year rate;
/* Convert the years into periods (month) from the start date */
months=(year-start)*12 ;
/* Handle case where 1st rate is at the start of the loan */
if months=0 then
months=1 ;
/* Add seperator if more than 1 period/rate */
if _n_ ne 1 then do ;
tempEC=cats(tempEC,", ") ;
end ;
/* Build code syntax */
tempEC=cats(tempEC,putn(months,"8."),"=",putn(rate,"8.2")) ;
/* Capture if the length of tempEC hits the maximum (200) and retutn an ERROR */
if length(tempEC)=200 then
put "ERROR: tempEC exceeeded length : " ;
put " " tempEC ;
/* Create macro variable for use in PROC LOAN */
call symput("EC",tempEC) ;
/* Put the syntax to the log (for debugging) */
put tempEC= ;
datalines;
2015 1.5
2016 2
2017 3
2018 2.5
2019 4
2020 5
;
run;
/* Put the macro variable to the log (for debugging) */
%put &=EC ;
proc loan start=2014:12;
/* use the macro variable EC in the ESTIMATEDCASE option */
arm amount=1000 rate=0 life=180 ESTIMATEDCASE=(&EC) schedule = 1
label='BANK3, Adjustable Rate';
run;
/* Cross-check using finance function to get FV of the loan*/
data finance;
monthly_pmt = pmt(1.5/100/12, 180, -1000);
outst_loan = -FINANCE('fv', 1.5/100/12, 12, monthly_pmt, 1000, 0);
run;
MY SOLUTION:
The correct code for the proc loan is as below: in the red part there should be "rate=1.5", not "rate=0" to correctly specify the rate for the first period. Now, the figures from different functions, procedures and Excel are the same.
proc loan start=2014:12;
/* use the macro variable EC in the ESTIMATEDCASE option */
arm amount=1000 rate=1.5 life=180 ESTIMATEDCASE=(&rates) schedule = 1
label='BANK3, Adjustable Rate';
run;
