topic Re: How to derive the interest rate in the mort function in New SAS User

How to derive the interest rate in the mort function

kelvin0602 — Tue, 22 Mar 2022 17:27:24 GMT

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.

Re: How to derive the interest rate in the mort function

ballardw — Tue, 22 Mar 2022 17:50:27 GMT

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.

Re: How to derive the interest rate in the mort function

Ksharp — Wed, 23 Mar 2022 12:42:57 GMT

Also could check FINANCE() function .

Re: How to derive the interest rate in the mort function

Rick_SAS — Wed, 23 Mar 2022 13:06:57 GMT

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.

Re: How to derive the interest rate in the mort function

Sajid01 — Thu, 24 Mar 2022 13:12:51 GMT

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