BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
JT99
Obsidian | Level 7

Hello!

 

I have a code for a 24-month installment plan. I need to get the principal, balance, interest and amortization of each account for each month. Amort is fixed in all 24 months.

 

/*code for 1st month*/

data data1;set Datanew;

where amort_period =24;

Month1=1;

Amort1=mort(bal_0,.,.2241502/12,24);

Interest1= ((bal_0)*(.2241502)/12);

Prin1= Amort1-Interest1;

Bal1=bal_0-Prin1;

run;

 

/*code for 2nd month */

 

data data2;set data1;

Month2=2;

Amort2 =amort1;

Interest2= ((bal1)*(.2241502)/12);

Prin2= Amort2-Interest2;

Bal2=bal1-Prin2;

run;

 

Can you guys help me simplify the code so I won't have to make a seperate code for each month? I tried using array but I get errors. I'm also not sure if array is applicable in this problem.

 

I will greatly appreciate any help or advice.

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

I guess you could build from this

 

%let n=24;

data amort;
amount = 1000;
rate = 0.2241502;
payment = mort(amount, ., rate/12, &n);
array balance{0:&n} balance0-balance&n;  
array capital{0:&n} capital0-capital&n;
array interest{&n}; 
balance0 = amount;
capital0 = 0;
do month = 1 to &n;
    interest{month} = rate * balance{month-1} / 12;
    capital{month} = capital{month-1} + payment - interest{month};
    balance{month} = amount - capital{month};
    end;
drop month;
run;
PG

View solution in original post

9 REPLIES 9
Reeza
Super User

Post the array code you tried. 

art297
Opal | Level 21

It is definitely a do-loop-driven array problem but, I agree with @Reeza, post the code that you tried.

 

Art, CEO, AnalystFinder.com

 

ArtC
Rhodochrosite | Level 12

If you do not need the arrays, the DO loop can do the stepping through the payment cycle.

data amortization;
int_rate=.2241502; * annual interest rate;
balance=100000;    * loan amount;
amort_period =24;  * number of monthly payments;
* Calculate hte monthly payment (AMORT);
Amort=mort(balance,.,int_rate/12,amort_period);
do month = 0 to amort_period;
   Interest= ((balance)*(int_rate/12));
   Principal= Amort-Interest;
   output amortization;
   Balance = balance-principal;
end;
run;
novinosrin
Tourmaline | Level 20

It would help if you could post your sample source data and the output sample data that you want. Forgive me, personally, I prefer to think through the solution on looking at data samples rather than code. Thank you

Reeza
Super User

@novinosrin wrote:

It would help if you could post your sample source data and the output sample data that you want. Forgive me, personally, I prefer to think through the solution on looking at data samples rather than code. Thank you


Code is because I don't feel like doing her homework. If she posts what she tried I'm happy to help, but an attempt is nice. And this is definitely homework since there are three or four such questions in the last two days from multiple users. Searching on here would produce an answer as well. 

 

I mostly agree, it's easier to get logic from data. But I have an actuarial degree so mortgage/loan tables are like second nature to me 🙂

novinosrin
Tourmaline | Level 20

@Reeza I totally agree, an OP's effort is needed and makes it more of a true discussion. Perhaps, i should have added "include data samples too along with your effort". Your rightly pointed 

PGStats
Opal | Level 21

I guess you could build from this

 

%let n=24;

data amort;
amount = 1000;
rate = 0.2241502;
payment = mort(amount, ., rate/12, &n);
array balance{0:&n} balance0-balance&n;  
array capital{0:&n} capital0-capital&n;
array interest{&n}; 
balance0 = amount;
capital0 = 0;
do month = 1 to &n;
    interest{month} = rate * balance{month-1} / 12;
    capital{month} = capital{month-1} + payment - interest{month};
    balance{month} = amount - capital{month};
    end;
drop month;
run;
PG
JT99
Obsidian | Level 7

Thank you all for the replies! You guys have been very helpful.

This is what my data looks like:

CARDNUMBER AMORT_PERIOD PRINCIPAL_AMT EffRate Bal0
296 24 430431.69 0.2241502 430431.69
356 24 53766.79 0.2241502 53766.79
976 24 75818.01 0.2241502 75818.01
610 24 13308.06 0.2241502 13308.06
966 6 34327.47 0 34327.47

 

So basically, I have a list of accounts that are enrolled in 24,18,15,12,9,6,3 months installment payment plan. I have decided to make a code for each payment plan since I don't know how to make one code for all of them.

For the 24-month payment plan, I want the result to look like this:

 

the result includes all principal,interest,balance for the whole 24 mos.

CARDNUMBER AMORT_PERIOD PRINCIPAL_AMT EffRate Bal0 Month1 Amort1 Interest1 Prin1 Bal1 Month2 Amort2 Interest2 Prin2 Bal2
296 24 430431.69 0.2241502 430431.69 1 22418.31636 8040.11245 14378.20391 416053.4861 2 22418.31636 7771.539343 14646.77702 401406.7091
356 24 53766.79 0.2241502 53766.79 1 2800.353543 1004.31973 1796.033815 51970.75618 2 2800.353543 970.7712827 1829.58226 50141.17392
Reeza
Super User

You should take a look at PROC LOAN. 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 1251 views
  • 2 likes
  • 6 in conversation