Abstract
The =LAMBDA function within Excel provides a powerful new tool for investors and analysts. In this treatment, we demonstrate =LAMBDA functions that calculate an option’s intrinsic value, price, and Greeks based on the Black-Scholes model. We additionally construct an =ALLGREEKS function within Excel’s built-in =MAKEARRAY function that quickly produces option prices and Greeks given six parameters: the underlying spot price (S), strike price (X), annual return volatility (V), days to maturity (DTM), days in a year (DIY), and the annual risk-free rate (RF). In addition to writing this guide on how to create these useful functions, we provide a downloadable file.
KEY TAKEAWAYS:
- We create Excel =LAMBDA functions for Black-Scholes call and put pricing with associated Greeks with and without a cost of carry.
- Incorporating the =MAKEARRAY function allows for a single Excel function (=ALLGREEKS or =ALLGREEKSQ) to produce option prices and Greeks based on six parameters (seven parameters when including a cost of carry (Q)).
- The Excel file is downloadable. Simply copying the function from the downloaded spreadsheet to a new spreadsheet makes the function and all associated nested functions available in the new spreadsheet.
Document Type
Working Paper
Publication Date
10-23-2024
Recommended Citation
Arnold, Tom, C., Joseph Farizo, and Jonathan Godbey. "Excel Functions for Option Prices and Greeks without VBA," University of Richmond Robins School of Business, (2024): 1-16.