Calculate delta (pay-performance sensitivity), vega (risktaking incentives), and firm-specific wealth (inside equity) for executives on Execucomp

Several papers use measures of delta (pay-performance sensitivity), vega (risktaking incentives), and firm-specific wealth (inside equity) for executives on Execucomp. For example,

1.  Core, J., Guay, W., 2002. Estimate the value of employee stock option portfolios and their sensitivities to price and volatility. Journal of Accounting Research 40, 613-630.

  • develops a method to calculate delta and vega using information provided by Execucomp in the pre-2006 period

2.  Coles, J., Daniel, N., Naveen, L., 2006. Managerial incentives and risk-taking. Journal of Financial Economics 79, 431-468.

  • estimates delta and vega

3.  Daniel, N., Li, Y., and Naveen, L. 2013. No asymmetry in pay for luck. Working Paper.

  • calculates a measure of firm-specific wealth using executives’ stock and option portfolios

Naveen makes publicly available her SAS program used to calculate delta, vega, and firm-specific wealth. See her homepage here. However,

  • Her program is not self-executable because she uses three external datasets for which she does not provide SAS codes used to create them.
  • Her program is to be executed locally so not portable (does not support PC SAS or SSH connection).
  • Her program calculates these measures only up to fiscal year 2010.

I improve Naveen’s program to make it self-contained and executable on its own. Specifically, I recreate the three datasets within the new program and update dataset references to point to the sever end. Now you can run the program via PC SAS or SSH connection, and specify the start year and end year of the period of interest. So you can easily update the data up to the most recent date.

I write a little more details in the overview section in the new program. As evidenced in the overview, I believe that I successfully replicate Naveen’s data using the new program. However, if you decide to use the new program, the accuracy of the generated data is your own responsibility.

Lastly, please cite Naveen’s work if you use the new program. I would be appreciated if you are generous enough to acknowledge my work.

This entry was posted in SAS. Bookmark the permalink.

35 Responses to Calculate delta (pay-performance sensitivity), vega (risktaking incentives), and firm-specific wealth (inside equity) for executives on Execucomp

  1. Thomas says:

    Hey Kai,

    First of all, thanks for sharing your SAS-script, it’s very useful!

    As I aim to gather the Delta’s and Vega’s of the CEOs of companies in the S&P 1500 during 2007 – 2014, I hope you can help me solving a question that is related to the output of the script (I want to match the variables Delta and Vega to my dataset of which the GVKEY is the primary identifier);

    – The output shows multiple rows of data per company per year. I assume this is because it also includes data on other executives, besides the CEO. I wish to collect only the data of CEOs. Do you think this is possible?

    Best regards,
    Thomas

    • Kai Chen says:

      You are right – the output includes other executives than CEO. You can modify the program to only include CEO. Another way (may be easier) is to SQL query if CO_PER_ROL is linked to a CEO flag in Execucomp.

      • JAY Gao says:

        By CEO flag, do you mean by ‘ceoann’ or ‘pceo’? I know this is so basic, but I never used this data set before and the variable explanation of these two provided by WRDS is not very clear to me, it would be so great if you can help to clarify this.

  2. Bo says:

    Hi, Kai,

    In the following part:

    if fyr=6 then assumed_grantyear=year-1;
    else assumed_grantyear=year;
    assumed_grantdate=mdy(7,1,assumed_grantyear);

    should it be “fyr<=6" instead of "fyr=6", since options granted in the first 5 months should also be assigned the previous year as the grant year?

    • Kai Chen says:

      Bo has informed me that Naveen’s program is correct—Naveen uses the code because of the way Compustat defines data year. Naveen shows the example in details in the program. Thank you Bo for letting me know this.

  3. Paul says:

    Hi Kai,

    Excellent code. If you can I think it might be valuable to add in ‘moneyness’ calculations too:

    Campbell, T.C., Gallmeyer, M., Johnson, S.A., Rutherford, J., Stanley, B.W., 2011. CEO
    optimism and forced turnover. J. Financ. Econ. 101, 695-712.

  4. Socrates says:

    Hi Kai,
    Thank you very much for this excellent code.
    I am not able to find these two variables: fybegdt fyenddt. I am using Stata so I need to download them.
    Are they supposed to be available on CRSP/Compustat Merged Database – Security Monthly?
    Many thanks for your help.

    • Kai Chen says:

      The first several lines in the program generates fybegdt and fyenddt. They are not something that can be directly downloaded from WRDS. You need SAS to run the program. Stata will not do the work.

  5. Li says:

    Hi Kai:
    Thank you very much for the codes. However, the coperol from the download data, I assume, is modified and ranked by the order of firms in the sample, instead of the true co_per_rol? How could I solve it? I checked the codes but couldn’t figure out where the definition of co_per_rol has been changed.

    • Kai Chen says:

      I don’t quite understand your question. If I remember correctly, co_per_rol is the unique id of an executive, no matter which company he/she works for. The code won’t change co_per_rol throughout.

  6. Li says:

    Nevermind. I figured it out. It’s due to the sorting in the SAS data.

  7. Emily says:

    Hi Kai,
    Thanks for sharing the codes! I’m a bit confused about the following codes at the end of the program.

    data deltavega;
    set deltavega;
    if optiondelta=0 then optiondelta=.;
    if delta=0 then delta=.;
    run;

    You corrected Naveen’s codes by using sum function. Sum function itself returns missing value if all variables summed up are missing. If we set zero optiondelta and delta to missing, aren’t we dropping some observations with legitimate value zero?

    • Kai Chen says:

      Hi Emily, thanks for letting me know. I think you’re correct. I cannot recall exactly why I added these codes. Probably because I misunderstood the sum function. I commented them out. Glad I had that disclaimer from day 1 🙂

  8. Karen Kong says:

    Hi Chen:
    I am wondering if you could upload the codes for total annual compensation (Execucomp variable TDC1)adjustment, as suggested by Coles, Daniel, and Naveen (2014, RFS). They adjust the total compensation for the changes in reporting following FAS 123R and new SECdisclosure requirements. I think Naveen already mentioned something about the adjustment in her codes on calculating delta and vega, but the information is very limited.

  9. Emma says:

    Hi Kai,

    Thanks for your excellent code! I am a bit confused by the following parts:

    A. line 1030
    data excomp13;
    set excomp12;
    if sumnumsecur=0 or sumnumsecur=. then do;
    Sopts_grants_yearend=0;
    Vopts_grantdate=0;
    Ropts_grants_yearend=0;
    sumrealizable_value=0;
    Vopts_yearend=0;
    FVopts_yearend=0;
    end;

    where does this FVopts_yearend come from?

    B. line 1289
    if opts_unvested_val_excl_curryear=. or opts_unvested_val_excl_curryear<0
    or opts_unvested_num_excl_curryear1) then rfunvest=tenyr;

    It should be if (matunvest^=. and round(matunvest,1)>10) then rfunvest=tenyr; right?

    D. line 1330
    if (matvest^=. and round(matvest,10)>10) then rfvest=tenyr;

    It should be if (matvest^=. and round(matvest,1)>10) then rfvest=tenyr;.

    Thanks for your reply!

  10. Emma says:

    Hi Kai,
    Hi Kai,

    Thanks for your excellent code!
    Sorry for the typos in the previous comment, so I re-send it again.
    I am a bit confused by the following parts:

    A. line 1030
    data excomp13;
    set excomp12;
    if sumnumsecur=0 or sumnumsecur=. then do;
    Sopts_grants_yearend=0;
    Vopts_grantdate=0;
    Ropts_grants_yearend=0;
    sumrealizable_value=0;
    Vopts_yearend=0;
    FVopts_yearend=0;
    end;

    where does this FVopts_yearend come from?

    B. line 1288
    if opts_unvested_val_excl_curryear=. or opts_unvested_val_excl_curryear<0
    or opts_unvested_num_excl_curryear1) then rfunvest=tenyr;

    It should be if (matunvest^=. and round(matunvest,1)>10) then rfunvest=tenyr; right?

    D. line 1330
    if (matvest^=. and round(matvest,10)>10) then rfvest=tenyr;

    It should be if (matvest^=. and round(matvest,1)>10) then rfvest=tenyr;.

    Thanks for your reply!

    • Karen Kong says:

      Hi Emma:
      Do you mean line 1274
      if (matunvest^=. and round(matunvest,1)>1) then rfunvest=tenyr;
      should be
      if (matunvest^=. and round(matunvest,1)>10) then rfunvest=tenyr;

      I think Naveen mess up with the round function, all of these two round functions should be round(matvest,1)>10.
      FVopts_yearend=0, I have no idea about this because this variable only shows up one time.

  11. Sewon says:

    Hi Kai,
    Thank you very much for this great code 🙂
    I am wondering if I can get delta for the current year’s option grants after 2006. In your code, delta for new options is “Sopts_grants_yearend”, however, I cannot find the similar variable for “newdelta” part (after 2006).

    Thank you!

    • hao says:

      Could you run his code? I couldn’t find the risk free rates dataset in wrds. The library name frb.rates_daily.

  12. hao says:

    I have problem with the library name. Libraries like crsp.ccmxpf_lnkhist and crsp.msf turn out to be error in my sas program. When I try to assign library name, I found that crsp in crsp.ccmxpf_lnkhist is /wrds/crsp/sasdata/a_ccm. But crsp in crsp.msf is /wrds/crsp/sasdata/a_stock. Hence, do I need to rename every library manually. However, it seems that your script could be run directly. So how should i do it?

  13. Anastasia says:

    Dear Kai! Thank you very much for such a helpful website! The code is great! I was trying to use the data of vega/delta to replicate simultaneous equations with R&D and CAPEX in Coles, J., Daniel, N., Naveen, L., 2006, but I get different results. Any chance that you tried to do it as well based on your data? Kind regards, Anastasia

    • Kai Chen says:

      No. I didn’t try to replicate the results of this paper. Generally, it is extremely hard to replicate the results of other research. It sounds not right but it is truth. Different results can occur at many data processing steps, e.g., how and when the researcher winsorizes variables? how and when the researcher drops certain observations. This is a black box. That’s why I think our community should increase the transparency of coding.

      • Anastasia says:

        yes, I absolutely agree with you! Thank you again, Kai! and I’ll be always happy to see any new posts from you.

  14. Akram says:

    Dear Kai, thank you for posting the code! I wanted to kindly ask you for a clarification. Vvest – stands for value of vested options, while “Vunvest” – as a value for unvested options. Can you, please explain the meaning of “Vopts_yearend” (that is the year-coperol sum of “Vc_yearend”)

    + +

    • Arthur Morris says:

      This is actually less of a question for Kai than it is for Lalitha Naveen and her coauthors, they note that this is a variable that they create for their Pay-for-luck paper (see line 910), which is forthcoming in RFS (Symmetry in Pay for Luck).

      However, the answer is reasonably clear from the code (see lines 882-913):

      – ‘Vc_yearend’ is the year-end-Black-Scholes value for the securities in each option grant.
      – ‘Vopts_yearend’ is the year-end-Black-Scholes value for all the options granted in the year. Line 952 simply sums the year-end BS values across all option grants in the year. This is an input to the BS value of the executive’s firm related equity portfolio on line 1395.

  15. Lucas says:

    I know it’s kind of stupid to ask ,

    but do you need a remote user name and remote password to access at the very beginning.

    I am a PHD student I can only access to Compustat via the link our school provide so which means I can not use the way you provide right?

    If i could able to download the data from Compustat, could you please tell me where should I start??

    Many many thanks.

  16. Uli says:

    Hey Kai,

    thanks for this really great code, that made work much easier!! However, when I want to continue working with data on option, and I even want to compare options before (option_awards_blk_value) and after 2006….which variable to use for the post period (option_awards_fv option_or awards?) or do I even need further calculations?

    Many thanks again.
    Best, Uli

  17. Uli says:

    Hi Kai,

    thanks for your great code, that really made work easier! However, when I want to continue to work with option data and need to compare data from 2003 to 2008, which variables do I have to use? For the pre period “option_awards_blk_value” but for the post period (one of these “option_awards” or “fv option_awards”) or do I even need further calculations?

    Many thanks again.
    Best,
    Uli

    • Arthur Morris says:

      Uli,

      The answer to your question has two parts.

      First, Lalitha Naveen’s original code (and Kai’s excellent update) both address the changes in the structure of option data on Execucomp around 2006 to come as close as is probably practical to a consistent option granting/holdings/delta/vega time series. For more details on exactly how Kai and Lalitha address these changes it’s worth reading the annotations in the code in detail, as well as the working paper that Jeff Coles, Naveen Daniel, and Lalitha Naveen have on SSRN: https://papers.ssrn.com/sol3/papers.cfm?abstract_id=2296381.

      Second, comparing option data from 2003 to option data from 2008 should be done with great care, even if your data is perfect. Remember that in the pre-2006 (pre-SFAS 123R) options were expensed at their intrinsic value (most often zero) rather than their fair value (greater than zero). Now options are expensed at their fair values, this accounting change seems to have had a huge effect on the use of option grants in compensation.

      Some papers that offer a good discussion of these changes are:

      Mary Ellen Carter, Luann J. Lynch, and I˙rem Tuna (2007) The Role of Accounting in the Design of CEO Equity Compensation. The Accounting Review: March 2007, Vol. 82, No. 2, pp. 327-357.

      Bettis, J.C., Bizjak, J., Coles, J.L. and Kalpathy, S., 2018. Performance-vesting provisions in executive compensation. Journal of Accounting and Economics, 66(1), pp.194-221.

      Core, John E. and Packard, Heidi, Non-price and Price Performance Vesting Provisions and Executive Incentives (July 28, 2017). Available at SSRN: https://ssrn.com/abstract=2547590 or http://dx.doi.org/10.2139/ssrn.2547590

      Best,

      Arthur

  18. Jules says:

    Hi Kai and everyone,

    Did you manage to calculate the Delta and Vega for the years 2015-2016-2017?
    Naveen and Coles do not report them on their website, therefore we cannot make sure that they are correct without calculating the previous years.
    Thank you!
    Jules

    • Arthur Morris says:

      Jules,

      Kai’s excellent code does this easily.
      You need to change line 67 from:

      %let end_year=2014;

      to:

      %let end_year=2018;

      or whatever the relevant year is.

      I believe Kai set line 67 to 2014 for comparability to Lalitha’s code.

      Best,

      Arthur

  19. Han says:

    Hello Kai, Arther, and Everyone,

    Thank you so much for this great website. Could you kindly answer the following three questions:

    (Q.1) Alike total delta incentives and total vega incentives, is it possible to get annual delta (i.e., delta grant) and annual vega (i.e., vega grant) incentives?

    I am sure you are familiar with this, but I would like to get these variables that appear in Core and Guay 1999.

    (Q.2) In addition to the interest rates that appear in the following link,

    https://wrds-www.wharton.upenn.edu/pages/support/manuals-and-overviews/compustat/execucomp/modified-black-scholes-option-valuation-methodology/

    where can I find the rates for post 2003 (to 2012)?

    (Q.3) To compute PPS of each executive following Edmans, Gabaix, and Landier (2009), the formula seems to be: PPS = [# of shares + # of options * option delta) * (price / 100) / annual compensation

    Thanks to you codes, I believe I got the ‘option delta’. Would it be possible to get the option vega?
    Also, I wish I knew the variable name for “price”, but perhaps this is not something that should be asked here.

Leave a Reply

Your email address will not be published. Required fields are marked *