Invest Excel

Calculate Implied Volatility with VBA

This article offers VBA code and an Excel spreadsheet to calculate the implied volatility of an option. This parameter is often compared to the historical volatility of the underlying asset to determine if the price of an option represents good value.

The Complete Guide to Option Pricing Formulas

Espen Gaarder Haug is an option pricing god. He’s passionate about derivatives, and in this book he’s documented almost every single option developed in the last 30 years.

Calculate Historical Volatility in Excel

This spreadsheet calculates the historical volatility of a stock. It uses returns data automatically downloaded from Yahoo.

Historical volatility is the standard deviation of an asset’s historical returns. The standard deviation is calculated over a moving time window.

Shout Options – Introduction and Spreadsheet

This article introduces shout options and offers a pricing spreadsheet. A shout option let investors lock in profits before maturity, but leaves room for future gains.

Pricing Extendible Options in Excel

This article introduces extendible options, and provides pricing free spreadsheets. Extendible options are a type of compound option and allow …

Compound Options – Introduction and Pricing Spreadsheet

This article introduces compound options, and provides a pricing spreadsheet. Companies often use compound options when tendering for a contract; the option hedges against the risk of winning the contract.

Value at Risk of a Bond

This Excel spreadsheet calculates the value at risk for a bond.

Pricing LIBOR Options with Excel

This Excel spreadsheet calculates the price of a LIBOR option using the Black-76 model.

American Options – Pricing Methods and Spreadsheets

Barone-Adesi Whaley Bjerksund Stensland Ju Zhong

This article summarizes several methods for pricing American options, and provides free spreadsheets for each.

Forward Start Options – Introduction and Spreadsheet

Forward start options are purchased in the present, but have a strike that is determined afterwards (i.e. at the forward date) but before maturity. At the forward date, the option becomes a standard European option.

Pricing Simple and Complex Chooser Options with Excel

This article introduces Chooser Options, and provides a pricing spreadsheet. Chooser options give the investor the privilege of choosing whether the option is a put or a call at some predetermined date.

Exchange Options – Introduction and Pricing Spreadsheet

This article introduces European Exchange Options, developed by Margrabe in 1978, and offers a free a pricing spreadsheet.

Quanto Options – Guide and Spreadsheet

This guide introduces quanto options, and provides a pricing spreadsheet. Quantos are options that hedge against exchange rate risk. Typical examples are Nikkei index warrants.

Value at Risk – Methods and Free Spreadsheets

This article summarizes several methods of calculating Value at Risk, and provides pricing spreadsheets. Value at Risk is an important tool for estimating capital requirements, and is now a standard risk-management tool.

Bond Options – Introduction and Pricing Spreadsheets

This article introduces Bond Options, and provides pricing spreadsheets which use the the Black (1976) and Schaefer & Schwartz (1987) models.