topic Re: DO loops with regular monthly deposits and interest compounded annually, monthly and daily in SAS Programming

DO loops with regular monthly deposits and interest compounded annually, monthly and daily

Magdalena56 — Mon, 04 Nov 2019 20:00:47 GMT

I have been searching for an answer to this common exercise but could not find an example with regular monthly deposits contributed over the investment period:

Savings account:

$1000 contributed monthly for 5 years

1) Calculate savings after 5 years with interest=4% compounded annually (use DO loop)

2) Calculate savings after 5 years with interest=3.9% compounded monthly (use DO loop)

3) Calculate savings after 5 years with interest=3.8% compounded daily (use DO loop)

Here are the codes I have tried (the third one seems to return unrealistic value):

data compound_annually;
principle=12000;
rate=0.04;
earned=0;
do year=1 to 5;
earned=principle+earned+(principle+earned)*rate;
output;
end;
run;

data compound_monthly;
principle=1000;
rate=0.039/12;
earned=0;
do month=1 to 60;
earned=principle+earned+(principle+earned)*rate;
output;
end;
run;

data compound_daily;
principle=32.87671233;
rate=0.038/4380;
earned=0;
do day=1 to 21900;
earned=principle+earned+(principle+earned)*rate;
output;
end;
run;

Can someone please review the code and provide a better way to resolve the questions using do loops?

Thank you!

Re: DO loops with regular monthly deposits and interest compounded annually, monthly and daily

PaigeMiller — Mon, 04 Nov 2019 20:09:27 GMT

I'm surprised you want a DO loop solution. SAS has already done the hard work of creating functions for virtually any financial problem, then they have tested them and debugged them, so you don't have to. For example the FINANCE('FV',...) function seems to do what you want, with much less effort.

https://documentation.sas.com/?cdcId=pgmmvacdc&cdcVersion=9.4&docsetId=lefunctionsref&docsetTarget=p0cv3zc7cq12jdn1mdvh9tzfzjdy.htm&locale=en

Or at the very least, you can compare your do loops to the results of that function.

Re: DO loops with regular monthly deposits and interest compounded annually, monthly and daily

Magdalena56 — Mon, 04 Nov 2019 20:15:10 GMT

Thank you PaigeMiller! I agree financial functions are there for a reason 🙂 but the exercise requires do loops.

Re: DO loops with regular monthly deposits and interest compounded annually, monthly and daily

PaigeMiller — Mon, 04 Nov 2019 20:16:16 GMT

But you could see if you are getting the right answers from your do loops comparing to the FINANCE functions.

Re: DO loops with regular monthly deposits and interest compounded annually, monthly and daily

Magdalena56 — Mon, 04 Nov 2019 20:22:00 GMT

Not getting the right answers except for #1.

Re: DO loops with regular monthly deposits and interest compounded annually, monthly and daily

hashman — Mon, 04 Nov 2019 20:58:48 GMT

@Magdalena56:

You're headed in the right direction, just need to apply the correct expression. The COMPOUND function is called below just to compare the results with those generated by the canned SAS function:

data annual ;                                                                                                                           
  retain principal 12000 rate 0.04 ;                                                                                                    
  do year = 1 to 5 ;                                                                                                                    
    earned = principal * (1 + rate) ** year ;                                                                                           
    comp = compound (principal, . , rate, year) ; *just for comparison ;                                                                
    output ;                                                                                                                            
  end ;                                                                                                                                 
run ;

Based on this template, you can surely generate the data sets for the monthly and daily periods yourself.

Kind regards

Paul D.

Re: DO loops with regular monthly deposits and interest compounded annually, monthly and daily

Magdalena56 — Sat, 16 Nov 2019 19:21:09 GMT

Many thanks hashman, this is not correct though because neither the expression

 earned = principal * (1 + rate) ** year ;

nor the compound function satisfy the condition that extra money is contributed every year (not just the initial amount compounded annually).

The expression "earned=principle+earned+(principle+earned)*rate;" yields a correct value, although not sure what financial function in SAS would be equivalent.

Many thanks again!