topic Re: Invalid argument to function FINANCE 'Rate' with negative interest rate in SAS Programming

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

IgorR — Wed, 23 Aug 2023 12:36:06 GMT

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.

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

PaigeMiller — Wed, 23 Aug 2023 12:44:33 GMT

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

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

FreelanceReinh — Wed, 23 Aug 2023 17:51:06 GMT

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.

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

IgorR — Thu, 24 Aug 2023 07:10:38 GMT

@ FreelanceReinh,

I am incredibly amazed by your brilliant solution.

I see how much I still have to learn.

Thank you!