topic Re: Optimal Interest Rate in SAS Data Science

Optimal Interest Rate

Job — Fri, 22 Mar 2013 20:07:05 GMT

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

Re: Optimal Interest Rate

TomKari — Sat, 23 Mar 2013 14:30:04 GMT

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

Re: Optimal Interest Rate

Job — Sat, 23 Mar 2013 16:15:18 GMT

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!

Re: Optimal Interest Rate

TomKari — Sat, 23 Mar 2013 18:37:10 GMT

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

Re: Optimal Interest Rate

Job — Sat, 23 Mar 2013 19:15:19 GMT

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

Re: Optimal Interest Rate

TomKari — Sun, 24 Mar 2013 13:25:38 GMT

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

Re: Optimal Interest Rate

Job — Sun, 24 Mar 2013 20:15:37 GMT

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

Re: Optimal Interest Rate

TomKari — Sun, 24 Mar 2013 21:17:52 GMT

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

Re: Optimal Interest Rate

Job — Sun, 24 Mar 2013 21:46:22 GMT

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

Re: Optimal Interest Rate

TomKari — Mon, 25 Mar 2013 23:00:46 GMT

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