BookmarkSubscribeRSS Feed
chris2377
Quartz | Level 8

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;

 

 

 

2 REPLIES 2
mkeintz
PROC Star

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.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
chris2377
Quartz | Level 8

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

 

Best

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 1014 views
  • 1 like
  • 2 in conversation