How to derive the interest rate in the mort function

[kelvin0602]

Hi all,

  I am working in a task to derive the interest rate using excel spreadsheet. Normally the interest rate (r) is derived using the SAS mort function - MORT( a, p, r, n), where a, p and n are already given. (a = amount, p = monthly payment, n = number of periods). 

Reference: https://documentation.sas.com/doc/en/vdmmlcdc/8.1/ds2ref/n15243fav7jqr6n1amghnmw1vb8i.htm

 Are there any methods to derive the interest rate (r) if it is unknown factor in excel? Or are there any alternative formula can be used?   

 Thanks. 

[ballardw]

Are you asking how to do this without using SAS at all?

If so, likely it is doable but might be a question for an Excel forum.

My approach would be import data into SAS and use the Mort function, then export results.

[Ksharp]

Also could check FINANCE() function .

[Rick_SAS · Accepted solution]

Yes, you can do that. Solving for the payment as a function of the other variable requires solving for the root of a nonlinear equation. There are many ways to solve for roots in SAS, but I am going to suggest a way that is easily implemented in a spreadsheet.

Basically, you loop over a sequence of possible payment values and find the value for which the formula that the MORT function uses is satisfied. Your post includes a link to the doc, which shows the amortization formula.  The following DATA step loops over possible payments and finds the value for which the formula is approximately satisfied:

data Want;
a = 50000; 
r = 0.1 / 12;
n = 30 * 12;
/* use the formula from the doc:
   p = a*r*(1+r)**n / ( (1+r)**n - 1 ); */
do p = 300 to 600 by 5;  /* find payment to nearest $5 */
   diff = p - a*r*(1+r)**n / ( (1+r)**n - 1 );
   output;
end;
run;

title "Find p such that Diff=0";
proc sgplot data=Want;
   series x=p y=diff;
   refline 0 / axis=y;
run;

proc print data=Want;
where abs(diff)<10;
run;

From the graph and the printed output, you know that the true value for payment is in the interval [435, 440], since that is where the DIFF variable is close to 0. You can now repeat the computation to find the payment to the nearest cent:

data Want;
a = 50000; 
r = 0.1 / 12;
n = 30 * 12;
/* p = a*r*(1+r)**n / ( (1+r)**n - 1 ); */
do p = 435 to 440 by 0.01; /* find payment to nearest cent */
   diff = p - a*r*(1+r)**n / ( (1+r)**n - 1 );
   output;
end;
run;

proc print data=Want;
where abs(diff)<=0.01;
run;

I hope you see how this method can be used in a spreadsheet.

[Sajid01]

Hello @kelvin0602 
Thanks for trusting SAS Forums for your excel issues.
The excel RATE function helps us calculate interest rate  when the initial amount or principal, the monthly payment and payment periods are known. You can have the details here.
https://support.microsoft.com/en-us/office/rate-function-9f665657-4a7e-4bb7-a030-83fc59e748ce