Calculating yield with the FINANCE function

[Yuki1314]

I'm trying to calculate the bond yield with the FINANCE function, but it reminds me that "Invalid argument to function FINANCE('yield',12991,13180,0.0572,102.91127362,100,2,0) at line 14237 column 4."(See below)

And the original data is like this:

The problem is that r1 and r2 are both calculated with the FINANCE function while r1 is missing, the only difference is the coupon rate. For r1, it is 5.72; for r2, it is 0.0572. 

The code is like this(100 is redemption value):

data z ; set z;
  keep offering_date maturity coupon PV frequency basis r1 r2 a;
  a=coupon/100;
  r1=finance('yield', offering_date, maturity, a, PV, 100, frequency, basis);
  r2=finance('yield', offering_date, maturity, coupon, PV, 100, frequency, basis);
run;

Does anyone have any suggestions?

[Tom]

Since the function call you posted works fine:

1    data test;
2     x=FINANCE('yield',12991,13180,0.0572,102.91127362,100,2,0) ;
3     put x=;
4    run;

x=0.0002335037

One (or more) of the actual values are probably slightly different.  Perhaps by so small an amount that normal printing format does not display them. Which is why copying the code and re-running it works.

Make sure to check the values are in the proper ranges before calling the function.

For example if FREQUENCY is supposed to be an integer you can use ROUND() or INT() to make sure it is.

[Yuki1314]

Thank you for your suggestions!

I tried with the copied number, it does work. But It just does not work with my data(many observations and this obs is just one of them). And this function works for many other observations, except this one and some others. 

Just for checking, I wrote three more functions with specific coupon rates:

data z ; set z; 
  keep offering_date maturity coupon PV frequency basis r1 r2 r3 r4 a; 
  a=coupon/100;
  r1=finance('yield', offering_date, maturity, a, PV, 100, frequency, basis); 
  r2=finance('yield', offering_date, maturity, coupon, PV, 100, frequency, basis);
  r3=finance('yield', offering_date, maturity, 0.0572, PV, 100, frequency, basis); 
  r4=finance('yield', offering_date, maturity, 5.72, PV, 100, frequency, basis); 
  r5=finance('yield', offering_date, maturity, 0.0322, PV, 100, frequency, basis); 
run;

And the results is:

I checked the format of the coupon, as well as the var a. They are both best12.. 

I also checked the accuracy of the number(See r5). I used 0.0322, the same accuracy as 0.0572. It also works.

Given that there are many obs. in my data, it is impossible to copy all the numbers down.

Do you have any ideas about that?😃

[Tom]

The FINANCE() function (or any other function) does not care what DISPLAY FORMAT you have attached to variables.  It is the VALUE that the variable contains that matters.

Example, Adding a tiny amount to the FREQUENCY value will not change it appears when printed with the BEST12. format, but it does cause it be invalid for the FINANCE() function.

103  data test;
104   d1='27JUL1995'd;
105   d2='01FEB1996'd;
106   format d1 d2 yymmdd10.;
107   rate=0.0572;
108   pv=102.91127362;
109   freq=2;
110   basis=0;
111   e=1E-11;
112   x=FINANCE('yield',d1,d2,rate,pv,100,freq+e,basis) ;
113   put (_all_)(=);
114  run;

NOTE: Argument 7 to function FINANCE('yield',12991,13180,0.0572,102.91127362,100,2,0) at line 112 column 4 is invalid.
d1=1995-07-27 d2=1996-02-01 rate=0.0572 pv=102.91127362 freq=2 basis=0 e=1E-11 x=.
d1=1995-07-27 d2=1996-02-01 rate=0.0572 pv=102.91127362 freq=2 basis=0 e=1E-11 x=. _ERROR_=1 _N_=1
NOTE: Mathematical operations could not be performed at the following places. The results of the operations have been set to
      missing values.
      Each place is given by: (Number of times) at (Line):(Column).
      1 at 112:4

Read the documentation for the function and make sure the VALUES you are passing it are appropriate.

[Yuki1314]

Thank you! I think I get your point.

You mean that 0.0572 could be recognized by SAS but 0.0572000000001 may not. But it seems not the problem of this.

See r3 and r5 in the last result, the only difference is the specific value of the coupon rate. And I didn't plus any tiny amount to these two parameters. However, the function works for r5 but not r3. If it is the problem you mentioned, then it should not work for r5 either.

BTW, I also tried to round the coupon rate from 0.0572 to 0.057 and 0.06. The function does not work with 0.057 but works with 0.06. And it also works with 0.032200000001

data z ; set z; 
  keep offering_date maturity coupon PV frequency basis r1 r2 r3 r4 r5 r6 r7 a; 
  a=round(coupon/100,0.01);
  r1=finance('yield', offering_date, maturity, a, PV, 100, frequency, basis); 
  a2=round(coupon/100,0.001);
  r2=finance('yield', offering_date, maturity, a2, PV, 100, frequency, basis); 

  r3=finance('yield', offering_date, maturity, coupon, PV, 100, frequency, basis);
  r4=finance('yield', offering_date, maturity, 0.0572, PV, 100, frequency, basis); 
  r5=finance('yield', offering_date, maturity, 5.72, PV, 100, frequency, basis); 
  r6=finance('yield', offering_date, maturity, 0.0322, PV, 100, frequency, basis); 
  r7=finance('yield', offering_date, maturity, 0.032200000001, PV, 100, frequency, basis); 
run;

It's a little bit weird...🤔

[Patrick]

@Yuki1314 Just be aware that if your source data comes from another environment like a database then such bits can get added to floating point numbers as a result of differences of data representation between environments. 

The safe way is to round the values in the target environment to the significant portion - like: round(myval,.000000001);

[Yuki1314]

And I tried your case:

data z ; set test2a; 
  keep offering_date maturity coupon PV frequency basis r1 r2 r8 r9; 
  b=1E-11;
  r1=finance('yield', offering_date, maturity, coupon/100, PV, 100, frequency, basis); 
  r2=finance('yield', offering_date, maturity, 0.0572, PV, 100, frequency, basis); 
  r8=finance('yield', offering_date, maturity, 0.0572+b, PV, 100, frequency, basis); 
  r9=finance('yield', offering_date, maturity, coupon/100+b, PV, 100, frequency, basis); 
run;

Surprisingly, it works well even with a tiny number, except 0.0572.😂

I'm confused...

[Tom · Accepted solution]

READ THE DOCUMENTATION on the function.

Understand what the different parameters are and what type of values they require.

It looks like you make a minor change the PERCENTAGE value.  That should not have any impact on mathematics involved (and unless you are projecting returns for a thousand year no real impact on the result).

What my code demonstrated is that some of the parameters, like the FREQUENCY value, cannot handle it.  I assume because the code is expecting an INTEGER value.

So READ THE DOCUMENTATION and check what types of values you should be passing.  Then add code to test (or fix) the values you read from the source dataset before using them in the function call.

Perhaps it is as simple as

r1=finance('yield', offering_date, maturity, coupon/100, PV, 100, int(frequency), basis); 

But you are the one that knows what data you have. What calculation you are asking SAS to perform.  Has access to the observations that actually cause trouble so you can examine the values.

How many observations are talking about anyway?

Why not start by making a subset of just the observations that cause trouble?

data bad_cases;
  set test2a(keep=offering_date maturity coupon PV frequency basis);
  x = finance('yield', offering_date, maturity, coupon/100, PV, 100, frequency, basis);
  if missing(X);
  _error_=0;
run; 

[Yuki1314]

Thank you very much!

I tried to find the commonality among the missing values but it seems that there is no regular pattern...

I think it should be the problem that you mentioned about the passing value. Because when I plus a tiny amount to the coupon parameter, my code runs well. (I don't know why but it did make my code work. And I checked the results, they are right because the modified parameters are extremely close to the original value). 

Now my problem has been solved, thank you so much for your help!