turn on suggestions

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

Showing results for

Find a Community

- Home
- /
- Analytics
- /
- Text Analytics
- /
- Optimal Interest Rate

Topic Options

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-22-2013 04:07 PM

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

Accepted Solutions

Solution

03-23-2013
10:30 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-23-2013 10:30 AM

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

All Replies

Solution

03-23-2013
10:30 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-23-2013 10:30 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-23-2013 12:15 PM

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!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-23-2013 02:37 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-23-2013 03:15 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-24-2013 09:25 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-24-2013 04:15 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-24-2013 05:17 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-24-2013 05:46 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-25-2013 07:00 PM

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