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.

49 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.

  20. Jason says:

    Hi Kai,

    Thank you for sharing your code!

    I do have a question though: by comparing your delta with Naveen’s version, I find the difference becomes significantly larger after 2007. This makes me think whether whatever causes the increasing difference would induce more issues when I extend the sample period to recent years.

    Could you help me out on this? Thank you!

    Best regards,
    Jason

  21. Dave says:

    Hi Kai,

    Thank you for the great code!!

    Just to be sure I understand: the units on delta, vega, and firm-related wealth are thousands of dollars in contemporaneous prices, correct?

    Thanks again!

  22. David says:

    Dear Kai,

    I’m using your wonderful code, and would like to appropriately cite you.

    You write that we should cite Naveen’s work when using your code (and her website makes clear which papers she thinks should be cited), but only write “I would be appreciated if you are generous enough to acknowledge my work.” 

    Is there a paper of yours that would be most appropriate for me to cite in reference to this code? At the moment, I have a “thank you” to you, but please let me know if you’d like a different citation.

    Thanks again for doing this,
    Dave

  23. Kaou says:

    Hi Kai,

    Thank you for sharing your code!

    I appreciate your work but i need to up date my data till 2019. I will be gratuful if you can send the new data upadated because i don’t have now access to Execucomp and CRSP.
    Many thanks
    Kaou

  24. joey says:

    Dear Dr Chen
    May I ask
    For line 417
    I received the following warning
    WARNING: Variable GVKEY already exists on file WORK.EXCOMP1
    Is this normal?
    For line 1628-1631 I received the following:
    ERROR: Neither the PRINT option nor a valid output statement has been given.
    What is wrong?

    • Bo Li, Finance Ph.D. Student from Arizona State U. says:

      Hi Joey,

      I might be helpful.

      (1) Line 417. This is a typical warning when you use proc SQL to join two datasets when each of them has the same variable, say GVKEY here. Here, SAS reads in the first GVKEY from the first dataset and then the second GVKEY in the second dataset. Since the resulting dataset cannot have two identical variables so the SAS does not keep the second GVKEY and gives a warning. You will see “already exists” in multiple similar cases in this code elsewhere. No worry.

      (2) Line 1628-1631. This is just an error recognized by the program. But actually, this is not an error in the calculation since line 1628-1631 is designed to do a basic checking based on quantiles. Prof. Kai Chen inserts “noprint” to Prof. Naveen’s code to prevent printing results in SAS since printing results takes time. This waste of time is not necessary for executing the right calculation. No worry.

  25. Hakjoon Song says:

    Dear Dr. Chun,
    Thank you so much for your your code to calculate delta and vega
    ExecComp and Compustat fundamental data both have the same libname (comp) and this makes problem, which stops running the SAS program. Do you know how to distinguish two?
    libname comp /*remote*/ ‘/wrds/comp/sasdata/d_na’ /*server=wrds*/;
    libname comp /*remote*/ ‘/wrds/comp/sasdata/execcomp’ /*server=wrds*/;

    Also I see execcomp.person, execcomp.ex_black in the program, but I do not see them in ExecComp database. Could you please explain this?

    Thank you so much for your help

  26. Dear Prof. Chen,

    I spot a tiny typo in line 1274. The 1 should be 10 since the condition is for var matunvest to be bigger than 10.

    Old and typoed code:
    if (matunvest^=. and round(matunvest,1)>1) then rfunvest=tenyr;
    New and correct code:
    if (matunvest^=. and round(matunvest,1)>10) then rfunvest=tenyr;

  27. Hi Prof. Chen

    I just see a code error in line 1274, where the code shall assign frunvest=tenyr when the matunvest is greater than 10.

    The old and errored code is:
    if (matunvest^=. and round(matunvest,1)>1) then rfunvest=tenyr;

    The new and corrected code shall be:
    if (matunvest^=. and round(matunvest,1)>10 then rfunvest=tenyr;

    I also go to Prof. Naveen’s website to check. Her code is correct in the corresponding line (line 1135) at https://sites.temple.edu/lnaveen/files/2020/11/deltavega_2013.txt.

    Best Regards,
    Bo Li

  28. Bo Li says:

    Hi Prof. Chen

    I just see a code error in line 1339, where the code shall assign frunvest=tenyr when the matunvest is greater than 10. I also check with Prof. Naveen. She said this error is corrected in her updated code, though her online version does not update this change when I communicated with her.

    in Line 1339, the old and errored code is:
    if (matvest^=. and round(matvest,10)>10) then rfvest=tenyr;

    The new and corrected code shall be:
    if (matvest^=. and round(matvest,1)>10) then rfvest=tenyr;

    The reason is actually quite simple. The old code can leave rfvest unassigned (missing value assigned by SAS) for observations that have matvest between 10.50 and 14.99. Such observations will then have missing value for Zvest, Svest, Rvest, and Vvest.

    I further write a short piece of code and find out that there are 172 observations in excomp14 that have rfvest as missing value due to above reason. After above correction, out of these 172 observations, 152 observations have non-missing value for all four variables Zvest, Svest, Rvest, and Vvest. This is not a big issue at all given the huge number of total observations. But it is good to correct it.

    Thank you again for making this code available. I definitely learn a lot from such code.

    Best Regards,
    Bo Li

  29. Sanne Middelhoek says:

    Hi Kai,

    This guideline is very helpful, thank you so much!

    Though I do not understand a slight thing. In the READ DATA ON OUTSTANDING AWARDS section, opts_unearned_num = opts_unex_unearn is mentioned, but in the end it is not included in the ‘newoptiondelta’. It seems as if the current year’s granted options are being neglected. Why is that?

    I look forward to your response.

    Best regards,
    Sanne

  30. jianchen shi says:

    Dear Kai,

    Thanks so much for your codes. I am writing to seek your advice on an issue I am facing with my dataset. Despite my efforts, I have come across some observations that are negative in the maturity_grantdate field. I am uncertain how to approach this problem, and I was wondering if I should follow the same approach I used to deal with the maturity_yearend field.

    I would appreciate any insights or suggestions you might have regarding this matter. Thank you for your time and consideration.

    Looking forward to hearing from you soon.

    Best wishes,
    jianchen

Leave a Reply to Bo Li Cancel reply

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