Hello,
I am trying to convert Excel function "RATE" to SAS code.
But it turns out that the function FINANCE("RATE") cannot accept a negative value of interest.
I have a next parameters:
Nper = 1,
Payment = -94,
Pv = 0,
Fv = 76.96,
Type = 1.
In excel these parameters returns result -18.13% (minus 18.13%).
In SAS I get missing value.
In SAS I'm trying to calculate this by this:
Result = FINANCE('RATE', nper, payment, pv, fv, type);
Please, help me to find a way to make calculation that can handle both negative and positive parameters and zeroes as well.
Hello @IgorR,
Alternatively, if you don't want to rely on the FINANCE function (let alone on Excel), it's maybe best to use your own formulas. Here's what this might look like in the special case Pv=0 and Type=1, which you use in your example:
proc fcmp outlib=work.funcs.finance;
function f(nper, r);
return((r**(nper+1)-r)/(r-1));
endsub;
function rate(nper, payment, fv);
p=round(solve('f',{.},-fv/payment,nper,.)-1, 1e-10);
return(p);
endsub;
run;
options cmplib=work.funcs;
data test;
input nper payment fv;
p=rate(nper, payment, fv);
cards;
1 -94 76.96
2 -10 10
3 -50 150
4 -25 110
6 -30 9999
8 -12 97
8 12 -97
;
proc print data=test;
format payment 8.2 p percentn10.2;
run;
Result:
Obs nper payment fv p 1 1 -94.00 76.96 -18.13% 2 2 -10.00 10.00 -38.20% 3 3 -50.00 150.00 0.00% 4 4 -25.00 110.00 3.85% 5 6 -30.00 9999.00 141.02% 6 8 -12.00 97.00 0.23% 7 8 12.00 -97.00 0.23%
As far as I see, the results above match those obtained with Excel (up to rounding errors).
I leave it to you to generalize this approach to cases with Pv ne 0 or Type=0.
You could make the payment 94, and then you should get a rate, and then you can multiply the rate by -1.
Hello @IgorR,
Alternatively, if you don't want to rely on the FINANCE function (let alone on Excel), it's maybe best to use your own formulas. Here's what this might look like in the special case Pv=0 and Type=1, which you use in your example:
proc fcmp outlib=work.funcs.finance;
function f(nper, r);
return((r**(nper+1)-r)/(r-1));
endsub;
function rate(nper, payment, fv);
p=round(solve('f',{.},-fv/payment,nper,.)-1, 1e-10);
return(p);
endsub;
run;
options cmplib=work.funcs;
data test;
input nper payment fv;
p=rate(nper, payment, fv);
cards;
1 -94 76.96
2 -10 10
3 -50 150
4 -25 110
6 -30 9999
8 -12 97
8 12 -97
;
proc print data=test;
format payment 8.2 p percentn10.2;
run;
Result:
Obs nper payment fv p 1 1 -94.00 76.96 -18.13% 2 2 -10.00 10.00 -38.20% 3 3 -50.00 150.00 0.00% 4 4 -25.00 110.00 3.85% 5 6 -30.00 9999.00 141.02% 6 8 -12.00 97.00 0.23% 7 8 12.00 -97.00 0.23%
As far as I see, the results above match those obtained with Excel (up to rounding errors).
I leave it to you to generalize this approach to cases with Pv ne 0 or Type=0.
@ FreelanceReinh,
I am incredibly amazed by your brilliant solution.
I see how much I still have to learn.
Thank you!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.