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

Hello all,

I am trying to find the optimal interest rate to have neither a profit nor a loss. The interest rate that I will earn is 14.25% on the $2,350,000 loan that I will receive. The interest rate that I think is the optimal interest rate to be charged for the $2,350,000 loan is 7.43936167%. I have to pay every period, except of the first period, $391,667.I tried to write a code to find the amount of money I am left over for 7 periods. The first period is 4 months and the remaining six periods are 6 months.

This is the SAS code I wrote:

data;

a0=2350000;

r1=0.1425;

r2=0.0743936167;

r3=r1/12;

r4=r2/12;

r5=r3*6;

r6=r4*6;

r7=r3*4;

r8=r4*4;

p=391667;

do i=1 to 7;

if i=1 then a0=(r7+1)*a0-(r8)*a0;

if i>=1 then a0=(r5+1)*a0-(r6)*a0;

output;

end;

proc print;

var a0;

run;

I think there is somewhere a problem. However, the SAS code works, but I wanted to ask your opinion. Please give me some feedback or please help me out to fix this code.

Thanks,

Job

1 ACCEPTED SOLUTION

Accepted Solutions
TomKari
Onyx | Level 15

Hi, Job

This looks to me like a school exercise, so first of all congratulations on studying SAS. It is an excellent software tool, and will stand you in very good stead through the years.

I do see some coding problems. I'm not going to point them out at this stage, as learning to debug your own code is a very important skill. However, the first thing that I did when I copied your code was to make some format changes. Here's how I would have coded it:

data;
Principal = 2350000;
AnnualInterestReceived = 0.1425;
AnnualInterestPaid = 0.0743936167;
MonthlyInterestReceived = AnnualInterestReceived / 12;
MonthlyInterestPaid = AnnualInterestPaid / 12;
OtherPeriodInterestReceived = MonthlyInterestReceived * 6;
OtherPeriodInterestPaid = MonthlyInterestPaid * 6;
FirstPeriodInterestReceived = MonthlyInterestReceived * 4;
FirstPeriodInterestPaid = MonthlyInterestPaid * 4;
Payment = 391667;

do i = 1 to 7;
  if i = 1 then
   Principal = (FirstPeriodInterestReceived+1) * Principal-(FirstPeriodInterestPaid) * Principal;

  if i >= 1 then
   Principal = (OtherPeriodInterestReceived+1) * Principal-(OtherPeriodInterestPaid) * Principal;
  output;
end;

proc print;
var Principal;
run;

Good variable names, structure, and spacing are incredibly important to understanding code. Take a look at this, and run it, and see if you can find some of the problems. Report back, and we'll continue the discussion.

Tom

View solution in original post

9 REPLIES 9
TomKari
Onyx | Level 15

Hi, Job

This looks to me like a school exercise, so first of all congratulations on studying SAS. It is an excellent software tool, and will stand you in very good stead through the years.

I do see some coding problems. I'm not going to point them out at this stage, as learning to debug your own code is a very important skill. However, the first thing that I did when I copied your code was to make some format changes. Here's how I would have coded it:

data;
Principal = 2350000;
AnnualInterestReceived = 0.1425;
AnnualInterestPaid = 0.0743936167;
MonthlyInterestReceived = AnnualInterestReceived / 12;
MonthlyInterestPaid = AnnualInterestPaid / 12;
OtherPeriodInterestReceived = MonthlyInterestReceived * 6;
OtherPeriodInterestPaid = MonthlyInterestPaid * 6;
FirstPeriodInterestReceived = MonthlyInterestReceived * 4;
FirstPeriodInterestPaid = MonthlyInterestPaid * 4;
Payment = 391667;

do i = 1 to 7;
  if i = 1 then
   Principal = (FirstPeriodInterestReceived+1) * Principal-(FirstPeriodInterestPaid) * Principal;

  if i >= 1 then
   Principal = (OtherPeriodInterestReceived+1) * Principal-(OtherPeriodInterestPaid) * Principal;
  output;
end;

proc print;
var Principal;
run;

Good variable names, structure, and spacing are incredibly important to understanding code. Take a look at this, and run it, and see if you can find some of the problems. Report back, and we'll continue the discussion.

Tom

Job
Calcite | Level 5 Job
Calcite | Level 5

Hi Tom,

Thank you for your answer. I was trying to find the optimal interest rate for a company/ bank where I am doing my internship. Thank you for the feedback about coding. I will begin to write my codes in the same way as you did.

However, I noticed I had to add the "-payment" on the second if then statement. The outputs are the same as mine. I did the same calculations in Excel, but I am getting in some periods different values.

Excel outputs:

4 months 2,403,350.00
6 months 2,093,524.74
6 months 1,773,148.94
6 months 1,568,200.18
6 months 1,229,935.40
6 months 880,151.63
6 months 518,456.60

I think there is still a problem with the if then statement. I am trying to find a way to connect the two if then statements. Since there is more than one equation, it is more challenging. I did the same problem in my Mathematical Modeling course but with only one equation. How can I make sure that the outstanding principal of the first equation is used in the second equation.

First equation:

   Principal1 = (FirstPeriodInterestReceived+1) * Principal0-(FirstPeriodInterestPaid) * Principal0;

Second equation:

   Principal2= (OtherPeriodInterestReceived+1) * Principal1-(OtherPeriodInterestPaid) * Principal1;


but then if i=3, 4, 5,6 7, it will still use the Principal 1 in the equation, and I think that is the problem.


Thank you again Tom for help!






TomKari
Onyx | Level 15

Hi again, Job

Okay, you're on the right track.

Subtracting the payment is definitely one of the changes that needs to be made.

A couple of other things look like problems:

In both IF statements, you add 1 (correctly in my opinion) to xPeriodInterestReceived before multiplying it by Principal, but you don't do the same with xPeriodInterestPaid.

A general comment about programming is to be very careful to use parentheses to ensure that your equations are resolved in the correct order. SAS will resolve mult/div before add/subt, but some languages won't. Assuming you wanted to add a 1 to your IntRec, I would have coded it like this:

Principal = ((FirstPeriodInterestReceived+1) * Principal)-((FirstPeriodInterestPaid+1) * Principal);

to ensure that the calculations are done in the correct order in any language.

The structure of your IF statements

  if i = 1 then
   ...;

  if i >= 1 then
   ...;

implies that the first then clause will be applied when i = 1, and the second when i is greater than or equal to 1. In other words, the second then clause will ALWAYS be executed, which I don't think is what you want.

You're very close!

  Tom

Job
Calcite | Level 5 Job
Calcite | Level 5

Hi again, Tom,

Thank you for your reply! I agree with your suggestion to add the parentheses. However, I would not add the +1 to the "(FirstPeriodInterestPaid+1) * Principal)" because the bank where I am doing an internship does not pay the full principal but only pays the percentage (i.e., (FirstPeriodInterestPaid) * Principal)).

Maybe, I should change the code's structure a little bit by adding new if then statements that will depend on the previous if then statements:


data;                                                                                                                                  

Principal = 2350000;                                                                                                                   

AnnualInterestReceived = 0.1425;                                                                                                       

AnnualInterestPaid = 0.0743936167;                                                                                                     

MonthlyInterestReceived = AnnualInterestReceived / 12;                                                                                 

MonthlyInterestPaid = AnnualInterestPaid / 12;                                                                                         

OtherPeriodInterestReceived = MonthlyInterestReceived * 6;                                                                             

OtherPeriodInterestPaid = MonthlyInterestPaid * 6;                                                                                     

FirstPeriodInterestReceived = MonthlyInterestReceived * 4;                                                                             

FirstPeriodInterestPaid = MonthlyInterestPaid * 4;                                                                                     

Payment = 391667;                                                                                                                      

do i=0 to 6;                                                                                                                           

if i=0 then Principal0 = ((FirstPeriodInterestReceived+1) * Principal)-((FirstPeriodInterestPaid) * Principal);                        

if i=1 then Principal1 = ((OtherPeriodInterestReceived+1) * Principal0)-((OtherPeriodInterestPaid) * Principal0);                      

if i=2 then Principal2 = ((OtherPeriodInterestReceived+1) * Principal1)-((OtherPeriodInterestPaid) * Principal1);                      

if i=3 then Principal3 = ((OtherPeriodInterestReceived+1) * Principal2)-((OtherPeriodInterestPaid) * Principal2);                      

if i=4 then Principal4 = ((OtherPeriodInterestReceived+1) * Principal3)-((OtherPeriodInterestPaid) * Principal3);                      

if i=5 then Principal5 = ((OtherPeriodInterestReceived+1) * Principal4)-((OtherPeriodInterestPaid) * Principal4);                      

if i=6 then Principal6 = ((OtherPeriodInterestReceived+1) * Principal5)-((OtherPeriodInterestPaid) * Principal5);                      

output;                                                                                                                                

end;                                                                                                                                   

proc print;                                                                                                                            

var Principal Principal0 Principal1 Principal2 Principal3 Principal4 Principal5 Principal6;                                            

run;


The code works but the outputs are incorrect. I think the if then statements do not take the previous principals into account. I am not sure how I can take that into account. Is there any code that will do that for me?


Thank you so much Tom!

Job







TomKari
Onyx | Level 15

Oh, I see a problem.

In the IF statements, we're calculating the change to principal, but not adding it back to the principal!

Try this:

if i=0

then Principal = Principal + ((FirstPeriodInterestReceived+1) * Principal) - ((FirstPeriodInterestPaid) * Principal);

else Principal = Principal + ((OtherPeriodInterestReceived+1) * Principal) - ((OtherPeriodInterestPaid) * Principal);

Tom

Job
Calcite | Level 5 Job
Calcite | Level 5

Hi Tom,

This time I got closer results but the outputs after the third observation are different. I am wondering if the Principal of the first calculation (i.e.,  2,403,350.00) is used in the second equation's principal that is multiplied by the interest rates.

Thanks again for your effort and help!

Job

TomKari
Onyx | Level 15

Could you post your expected results? At this point, I don't know if there's a problem with the code, or if I don't understand clearly the algorithm that you're trying to implement.

Tom

Job
Calcite | Level 5 Job
Calcite | Level 5

These are the expected results:

First 4 months 2,403,350.00
Next 6 months 2,093,524.74
Next 6 months 1,773,148.94
Next 6 months 1,568,200.18
Next 6 months 1,229,935.40
Next 6 months 880,151.63
Next 6 months 518,456.60

This is the algorithm that I wanted to write:

Principal=2,350,000

i=0 -> 2,403,350.00 =((FirstPeriodInterestReceived+1) * 2,350,000) - ((FirstPeriodInterestPaid) * 2,350,000)

i=1 -> 2,093,524.74 =((OtherPeriodInterestReceived+1) * 2,403,350.00) - ((OtherPeriodInterestPaid) * 2,403,350.00)

i=2 -> 1,773,148.94 =((OtherPeriodInterestReceived+1) * 2,093,524.74) - ((OtherPeriodInterestPaid) * 2,093,524.74)

. . .

i=6 -> 518,456.60 =((OtherPeriodInterestReceived+1) * 880,151.63) - ((OtherPeriodInterestPaid) * 880,151.63)

Thanks,

Job

TomKari
Onyx | Level 15

Got it! (Partly, anyway).

Your explanation let me come out at the correct amount for the first period, but rest aren't right. Here's what I see:

AnnualInterestReceived is 0.1425; divide it by 12 (to create MontlyInterestReceived) is 0.011875; multiply it by 6 (to create OtherPeriodInterestReceived) is 0.07125 (in both Excel and SAS)

By the same logic, AnnualInterestPaid is 0.0743936167; divide it by 12 (to create MontlyInterestPaid) is 0.006199468; multiply it by 6 (to create OtherPeriodInterestPaid) is 0.037196808.

So i=1 should be

((1 + 0.07125) * 2403350) - (0.037196808 * 2403350)

which comes to 2485191.739 in Excel and SAS.

Tom

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 choose a machine learning algorithm

Use this tutorial as a handy guide to weigh the pros and cons of these commonly used machine learning algorithms.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 9 replies
  • 1795 views
  • 9 likes
  • 2 in conversation