Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Home
- /
- Programming
- /
- Programming
- /
- calculate amount (compounded annually & compounded monthly)

Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

🔒 This topic is **solved** and **locked**.
Need further help from the community? Please
sign in and ask a **new** question.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 02-19-2018 08:38 AM
(5316 views)

Hi all SAS experts,

I am new to base SAS programming,

I am calculating amount(compounded annually & compounded monthly), the detailed question is below:

by using DO loops.

the code for 1 part :

data earn_3;

amount=500000;

int_rate=0.7;

do year=1 to 25;

earned+(amount+earned)*int_rate;

output;

end;

run;

for second part :

data earn_4;

amount=500000;

int_rate=0.7;

do month=1 to 25*12;

earned+(amount+earned)*int_rate/12;

output;

end;

run;

please suggest.

thanks

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

I presume you are trying to compare differences arising from varying frequency of compounding using the same annual interest rate.

If you want yearly values (as opposed to just the final value), I would recommend a loop for Y=1 to 25. The annual "compounding" is trivial, because it is effectively a simple interest rate. The monthly compounding (within each year) just needs the compound function, applying a rate of .07/12 and number of intervals=12.

```
data want;
Y=0;
amounta=50000;
amountm=amounta;
```

rate=.07;
output;
do Y=1 to 25;
amounta=(1+rate)*amounta;
amountm=compound(amountm,.,rate/12,12);
output;
end;
format amount: comma12.2;
run;

Of course, in the real world each amount might be rounded to the nearest penny at the end of the year, so you would nest each calculation in the round function:

```
amounta=round((1+rate)*amounta,.01);
amountm=round(compound(amountm,.,rate/12,12),.01);
```

Finally, if rounding is required monthly, then there is no benefit to the COMPOUND function. You have to make a monthly loop for each year and apply a simple monthly interest:

```
do Y=1 to 25;
amounta=round((1+rate)*amounta,.01);
do m=1 to 12;
amountm=round(amountm*(1+rate/12),.01);
end;
output;
end;
```

--------------------------

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

--------------------------

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

--------------------------

12 REPLIES 12

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Take a look at the Compound Function

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

There are many posts on the subject on here. Please use the search function:

As for your code, why not do it in one step:

data earn_3; amount=500000; int_rate=0.7; do year=1 to 25; earned=earned+(amount+earned)*int_rate; do month=1 to 12; earned_month=earned_month+(amount+earned)*(int_rate/12); end; end; run;

Something like that. Always put the assignement in, i.e. the variable before the =, also good idea to check the brackets as order of precedence might spoil your equation (i.e. calculations in brackets are resovled first, then in order of operator).

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

thanks for suggesting.

but its not working as desired.

the code for the same is :

data earn_4;

amount=500000;

int_rate=0.07;

do year=1 to 25;

interest_y=amount*int_rate;

amount+interest_y;

do month=1 to 25*12;

interest_m=amount*(0.07/12);

amount+interest_m;

output;

end;

end;

run;

it giving numbers in exponential form.

please suggest.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

"but its not working as desired.

please suggest. "

I would suggest posting some information about what is "not working". Start by providing some test data in the form of a datastep, then show what the output you want is. Show the code you are using. There are a lot of posts under that link I have provided, have you read those and tried to implement the provided solutions?

"but I have to do it by using DO LOOPS only." - is this some sort of homework assignment? If not use the tools provided.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

One change you will certainly want to make ... an interest rate of 7% should be 0.07, not 0.7.

Other than that, why do you say that your programs are inadequate? What needs to be fixed?

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

yes please correct me on that.

data earn_4;

amount=500000;

int_rate=0.07;

do year=1 to 25;

interest_y=amount*int_rate;

amount+interest_y;

do month=1 to 25*12;

interest_m=amount*(0.07/12);

amount+interest_m;

output;

end;

end;

run;

data earn_4;

amount=500000;

int_rate=0.07;

do year=1 to 25;

interest_y=amount*int_rate;

amount+interest_y;

do month=1 to 25*12;

interest_m=amount*(0.07/12);

amount+interest_m;

output;

end;

end;

run;

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

1. Format your code (indents) - makes it easier to spot errors. You'll save the time back in quicker debugging.

2. Comment your code - so we understand what you're expecting to happen in each line and you can understand it later when studying for your exams.

Here's some starters that show what I mean.

```
data earn_4;
amount=500000; *starting amount;
int_rate=0.07; *annual interest rate;
do year=1 to 25;
interest_y=amount*int_rate; * This isn't correct you'll always start from the same amount each loop;
amount+interest_y;
do month=1 to 25*12; *this loop is 25*12 years, too many loops;
interest_m=amount*(0.07/12);
amount+interest_m;
output;
end;
end;
run;
```

@sumitbe139 wrote:

yes please correct me on that.

data earn_4;

amount=500000;

int_rate=0.07;

do year=1 to 25;

interest_y=amount*int_rate;

amount+interest_y;

do month=1 to 25*12;

interest_m=amount*(0.07/12);

amount+interest_m;

output;

end;

end;

run;

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

I can see you’ve edited the question , but can’t see the original anymore. So I have no idea of what you’re looking for anymore. Is your question answered? If you still need help you’ll have to post way more details. Please don’t edit your question, rather post it as a new response.

@sumitbe139 wrote:

Hi all SAS experts,

I am new to base SAS programming,

I am calculating amount(compounded annually & compounded monthly), the detailed question is below:

by using DO loops.

the code for 1 part :

data earn_3;

amount=500000;

int_rate=0.7;

do year=1 to 25;

earned+(amount+earned)*int_rate;

output;

end;

run;

for second part :data earn_4;

amount=500000;

int_rate=0.7;

do month=1 to 25*12;

earned+(amount+earned)*int_rate/12;

output;

end;

run;

please suggest.

thanks

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Hi Reeza,

Thanks for your response.

I will definitely take care of that.

Yes, I am able to sort some the errors that I made by following the previous responses.

Yes, I need help in calculating the total amount at end of the term , compounded annually as well as compounded monthly.

the amount is 50000,

annual interest is 7%

term deposit is for 25 years.

for the same I have done this code :

it will give the amount at monthly levels.

but, I need to figure it out at yearly levels also.

data earn;

capital=500000; /** initial Amount **/

int_r=0.07/12; /*** for interest at monthly level **/

capital=500000; /** initial Amount **/

int_r=0.07/12; /*** for interest at monthly level **/

do year=1 to 25; /** for term deposit 25 years **/

int_m=capital*int_r; /** calculating the interest at monthly levels **/

capital+int_m; /** the interest that i am earning each month by accumulating at int_m variable **/

output;

end;

end;

run;

Please suggest me on that as well as improvements that I need to do.

thanks

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

If you want the annual values, either use a different calculation with only 25 years that would calculate the interest for each year. The Theory of Interest text has the formula (as does google).

Or you can just take the 12 value from your monthly table, use MOD() function to determine which records are the 12th month.

Spoiler

@sumitbe139 wrote:

Hi Reeza,Thanks for your response.I will definitely take care of that.Yes, I am able to sort some the errors that I made by following the previous responses.Yes, I need help in calculating the total amount at end of the term , compounded annually as well as compounded monthly.the amount is 50000,annual interest is 7%term deposit is for 25 years.for the same I have done this code :it will give the amount at monthly levels.but, I need to figure it out at yearly levels also.data earn;

capital=500000; /** initial Amount **/

int_r=0.07/12; /*** for interest at monthly level **/do month=1 to 25*12; /** for monthly levels years multiplied by 12 **/

do year=1 to 25; /** for term deposit 25 years **/

int_m=capital*int_r; /** calculating the interest at monthly levels **/

capital+int_m; /** the interest that i am earning each month by accumulating at int_m variable **/

output;

end;

end;

run;Please suggest me on that as well as improvements that I need to do.thanks

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Is there any reason you want to complicate the problem by computing year and monthly results in one step? It can be done, but you need to be careful. For example, looking at just the monthly program that you posted, you are not careful enough. The interior loop now looks like this:

do month = 1 to 25*12;

Since this is already inside a loop that has year going from 1 to 25, the interior loop should simply be:

do month = 1 to 12;

Moreover, you should actually run the program and inspect the results, to see if they look reasonable to you. You would have caught that change if you had actually checked the results.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

I presume you are trying to compare differences arising from varying frequency of compounding using the same annual interest rate.

If you want yearly values (as opposed to just the final value), I would recommend a loop for Y=1 to 25. The annual "compounding" is trivial, because it is effectively a simple interest rate. The monthly compounding (within each year) just needs the compound function, applying a rate of .07/12 and number of intervals=12.

```
data want;
Y=0;
amounta=50000;
amountm=amounta;
```

rate=.07;
output;
do Y=1 to 25;
amounta=(1+rate)*amounta;
amountm=compound(amountm,.,rate/12,12);
output;
end;
format amount: comma12.2;
run;

Of course, in the real world each amount might be rounded to the nearest penny at the end of the year, so you would nest each calculation in the round function:

```
amounta=round((1+rate)*amounta,.01);
amountm=round(compound(amountm,.,rate/12,12),.01);
```

Finally, if rounding is required monthly, then there is no benefit to the COMPOUND function. You have to make a monthly loop for each year and apply a simple monthly interest:

```
do Y=1 to 25;
amounta=round((1+rate)*amounta,.01);
do m=1 to 12;
amountm=round(amountm*(1+rate/12),.01);
end;
output;
end;
```

--------------------------

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

--------------------------

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

--------------------------

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. **Registration is now open through August 30th**. Visit the SAS Hackathon homepage.

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.