Invalid argument to function FINANCE 'Rate' with negative interest rate

[IgorR]

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.

[PaigeMiller]

You could make the payment 94, and then you should get a rate, and then you can multiply the rate by -1.

[FreelanceReinh · Accepted solution]

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.

[IgorR]

@ FreelanceReinh,

I am incredibly amazed by your brilliant solution.

I see how much I still have to learn.

Thank you!