DATA Step, Macro, Functions and more

Help with do loop

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 13
Accepted Solution

Help with do loop

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!


Accepted Solutions
Solution
‎04-19-2017 02:57 AM
Respected Advisor
Posts: 4,640

Re: Help with do loop

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


All Replies
Super User
Posts: 17,724

Re: Help with do loop

Post the array code you tried. 

PROC Star
Posts: 7,356

Re: Help with do loop

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

 

Art, CEO, AnalystFinder.com

 

Valued Guide
Posts: 632

Re: Help with do loop

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;
PROC Star
Posts: 161

Re: Help with do loop

[ Edited ]

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

Super User
Posts: 17,724

Re: Help with do loop


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 Smiley Happy

PROC Star
Posts: 161

Re: Help with do loop

@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 

Solution
‎04-19-2017 02:57 AM
Respected Advisor
Posts: 4,640

Re: Help with do loop

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
Occasional Contributor
Posts: 13

Re: Help with do loop

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
Super User
Posts: 17,724

Re: Help with do loop

You should take a look at PROC LOAN. 

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

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