Financial Analysis and Modeling Using Excel and VBA

Chapter 30. Options and Option Portfolios

In this chapter, we will develop some of the same models in VBA that we developed in Excel for options and option portfolios. Doing so will allow us to see that even though we could develop these models in Excel, creating them in VBA offers several advantages. For example, we will develop a worksheet function for calculating option prices based on the Black-Scholes-Merton (BSM) model that can then be used in other worksheet or VBA models. We will also develop a function that automatically calculates implied volatility. In the Excel model, the user had to do so using the Goal Seek function. Most importantly, we will develop more useful option portfolio models for evaluating various option-ttading strategies.

This chapter needs no theory or concepts beyond what I covered in Chapter 19: Options and Option Portfolios. If you have not studied that chapter yet, I recommend that you do so and work through the models there before working on the models here.

Modeling Examples

MODEL 1: PROFIT OF OPTIONS PORTFOLIO AT EXPIRATION

The Problem

Develop a model to calculate the projected profits of a portfolio of positions in stocks, calls, and puts at the time of expiration of the options for a range of terminal stock prices (that is, stock prices when the options expire). All the positions are on the same stock and all the options expire at the same time. For each position, the user should be able to enter the position type (stock, put, or call), exercise price ...