proc loan output vs. finance function SOLVED - see the end of the initial post

[chris2377]

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;

[mkeintz]

Why not just look at the FV's after one and/or two payments?  If they are different, it may be a lot easier to determine what the underlying algorithm is in each.

[chris2377]

Thanks @mkeintz I've managed to find the reason. But thanks for the suggestion!

Best