Implied Volatility Calculator in Excel

The Black-Scholes model can be used to estimate implied volatility. Implied Volatility can be estimated using  spot price, strike price, asset price, risk-free rate, time to maturity, and dividend yield. To achieve this, given an actual option value, you have to iterate to find the volatility solution. There are various techniques available; however we will use the Newton-Raphson bisection method.

Newton-Raphson Method and Implied Volatility

Implied Volatility is distinctively different from historical volatility measures. The term implied volatility comes from the fact that the volatility is removed from the market prices of options. Using Black-Scholes option pricing model, we can calculate Implied Volatility using trial and error. A great benefit of Newton-Raphson bisection method is that it gives fast convergences and the error approximation reduces rapidly with each additional iteration. The equation to calculate Implied Volatility of an option:

impliedvolatilitymain

where,

Pm is the market price of the option which we are trying to solve a fit for,

Pt is the option price given by Black-Scholes equation,

σ is the implied volatility

Once Black-Scholes is structured, we use an iterative technique to solve for σ.

An Example…

An itm option has 10 days for expiration. The strike price is 55 and the current stock price is 50. The stock has daily volatility of 0.03. The risk free interest rate is assumed to be 0.02.

blackscholesd1

blackscholesd2

Alternatively, d2 can be calculated as

blackscholesd3

Thus, the European Call value can be calculated as

blackscholesd4

where,

C = call value,

S = spot price of 50,

t = expiry period which is 10 days,

Norm = Normal probability distribution with mu=0 and sigma = 1,

K = option strike price of 55,

r = risk free rate of interest – we have assumed to be 0.02 in this example,

σ = daily stock volatility of 3% or 0.03

In excel, the formula can be written as

50*NORM.S.DIST(-0.95145,TRUE)-55*EXP((-0.02) * 10/365)*NORM.S.DIST(-1.04632,TRUE) .

Implied Volatility Calculator in Excel – VBA Function to Calculate Implied Volatility using Newton-Raphson method

The spreadsheet is quite straightforward. Just enter current stock price, strike price, risk free rate, days to maturity, dividend yield (if any) and the option price. The VBA computes implied volatility and back solves the option price which you have entered. As a verification, the option price calculated using Black-Scholes equation must equal to the option price specified in the input parameters.

implied-volatility-parameters

The following VBA function calculates the price of an European option using Black-Scholes equation.

implied-dividend-vba-black-scholes

As evident by function name, lines 19 through 47 calculate implied volatility for given input parameters as defined in name range on worksheet. The method iterates till it finds a solution. Precision is defined by constant DELTA_VOL, which is the acceptable error of the function’s result. Since the function iterates to find the correct volatility, setting a higher value can speed execution in a worksheet containing 100 and above instances of this function. Lower values increases the precision. Example:  If the exact implied volatility is 16%, setting “Precision” to .05 will cause the function to return a value between 15.95% and 16.05%.

implied-dividend-vba

 

Closing Notes:

  • “European” call options have the same theoretical value as “American” calls.  American puts are difficult to value than European puts, but experts agree the Black-Scholes formula provides a good approximation of volatility.
  • Since the Black-Scholes formula cannot be deconstructed to solve for volatility mathematically, this function uses an iterative process to find the implied volatility.  It is very similar to Excel’s “Goal-Seek” function, and other built-in Excel functions such as IRR.
  • Dividend Yield – The annualized dividend yield of the underlying stock expressed in continuous compounding terms.

Download the Implied Volatility Spreadsheet here

More free spreadsheets at investsolver.com

Advertisements

Calculate Exponential Moving Average in Excel

A primer on EMA:

The moving average method is a commonly used technical analysis indicator. All moving averages typically use a historical data series and the current price in the calculation. An Exponential Moving Average or EMA assigns a weighting factor to each value in the data series based on its age. The most recent data gets the greatest weight and each price gets a smaller weight as the series is traversed chronologically.

The weighting factors in an EMA is based on a smoothing factor generated from the length of the input. The common weighting method for EMA, is to add the difference between the previous average and the current price, multiplied by the smoothing factor, into the previous average. The exponential moving average places greater importance on more recent data.

EMA is expressed by the following equation:

exponential-moving-average

where,

P = current price

α = Smoothing factor  =eq2

N = Number of Time periods

So, current EMA is the sum of yesterday’s EMA (times 1 – weight) and today’s price (times by a weight)

The EMA works by weighting the difference between the current period’s price and previous EMA, and adding the result to the previous EMA. The shorter the period, the more weight applied to the most recent price.

In this example, the chart gives the EMA of Yahoo between Jan-01-2012 and Dec-31-2012. Buy or sell signals are often generated using a cross over of two moving averages – short and long time scale.

Calculate EMA using simple worksheet techniques:

1. To begin with, let’s calculate the 15-day EMA of Yahoo’s stock. The first step is to import historic stock prices from a web service.

2. The simple average is calculated of the first 15 prices of the stock using AVERAGE() function. The cells B3 through B17 contains the first 15 closing prices.

3. Enter the EMA formula as shown in the screen shot.

4. Repeat the above step by copying the formula for the entire set of stock prices.

Congratulations. You’ve calculated the EMA using simple spreadsheet techniques.

Calculate Exponential Moving Average in Excel using VBA:

EMA calculation and plotting of chart can be automated with help of VBA.

Let us begin by importing historical stock prices from Yahoo web service in CSV format. The most relevant data columns are Date and Close. Your import should look very similar to this screen grab:

yhoohiststock

 Once the stock prices are imported, we can make use of R1C1 and R[1]c[1]  style of programming in VBA. Click here for a quick reference on R1C1 and FormulaR1C1 properties in Excel. The VBA program accepts the following parameters:

  1. Stock Symbol
  2. Date Start
  3. Date End
  4. EMA Days or Time Window

params

In this example, I have defaulted start date to be the first day of the year and end date to be as current date. You are free to modify the parameters. The time window for plotting Exponential Moving Average in this example is 13 days  and there are 184 trading days. Columns A through G are simply the data points from the web service sorted by trading date. Column H contains the interesting piece where we calculate the EMA.

emaformula1

The highlighted cell H14 calculates the arithmetic mean of first 12 historical prices of Yahoo, Cells E2 through E13. The cells H15 through H185 contains the formula for calculating EMA of the remaining trading days.Once the data sheet is constructed with EMA, VBA function does the job of plotting a chart on the closing price vs EMA.

1. Following code does the initialization

snippetinit

2. The following snippets of code imports the historical prices from Yahoo web service.

snippet1ws

3. Finally, the following function plots the chart

snippetvbachart

Download the Excel spreadsheet for calculating Exponential Moving Average

More Free Spreadsheets at investsolver.com

Modified Sharpe Ratio in Excel

How Sharp is the Sharpe-Ratio?

If popularity was the only measurement for risk metrics, the Sharpe ratio would tell us everything we need to know. Unfortunately, Sharpe ratio falls short of covering the full spectrum of risks in the field of investing. Any discussion on risk-adjusted performance is incomplete without touching on the topic of Sharpe ratio or Reward to Variability which divides the excess return of a portfolio above risk free rate by its standard deviation or volatility.

Sharpe Ratio = rp – rf / σP

where,

rP = portfolio rate of return annualized normally
rF = risk free rate of return
σF = portfolio risk or volatility (annualized if portfolio return is annualized)

Sharpe ratio works well for normal-distributed returns, where the entire distribution can be explained through mean and variance alone. It is not sensitive to extreme loses and underestimates risk, and in such cases Sharpe ratio should be avoided. Its leading shortcoming is the fact that financial market returns do not follow a normal statistical distribution. The non-random part of the returns is commonly measured by Skewness, a measure of probability asymmetry, and Kurtosis (with “fat tails”)  which measures how peaked the random variables are.

Take for example a portfolio with a 8% risk premium and 25% volatility. The VaR is then roughly equal to 33.25% using a 95% confidence interval (-8 + 1.65*25, Z-value = 1.65 for 95% CI). As per VaR, there is a 95% probability that the losses on the portfolio will be restricted to $332,500 or 33.25% of a $1 million portfolio. We can flip that around and say that there’s a 5% probability that the loss could exceed $332,500. Notice that VaR has limitations, starting with the assumption that the returns are normally distributed.

Being realistic with MVaR or Modified VaR and Modified Sharpe Ratio

Modified VaR or “MVaR” takes into account skewness and kurtosis of the returns distribution. It makes an attempt at finding a compromise between realism and computational simplicity. VaR can be modified using a Cornish-Fisher asymptotic expansion as follows:

mvar_eqn

where,

zc = -1.65 with 95% confidence

rp = expected portfolio rate of return

S and K are skewness and kurtosis respectively.

Modified Sharpe Ratio adjusted for skewness and kurtosis can be expressed as

Modified Sharpe Ratio = rp – rf / MVaR

The attached spreadsheet simplifies the calculation for you. It calculates MVaR and Modified Sharpe Ratio once you fill in the annualized portfolio returns, confidence level and portfolio amount. For calculating Z-value, use NORM.S.INV() for Excel 2010 and newer versions. Use NORMSINV() for Excel 2007 and earlier versions.

sharperatioexcelshot

Download Modified Sharpe Ratio Spreadsheet here

More free spreadhseets at investsolver.com

Implement Williams %R in Excel

Named after Larry Williams, %R is a technical analysis indicator that shows the current closing price in relation to the high and low for a given look-back period, N. N can be a given number of days, weeks, months, or an intraday time-frame. Williams %R is also referred to as the inverse of the Fast Stochastic Oscillator.

In 1987 Larry Williams won the world cup of futures trading turning $100k into over 1.1 million in a span of 12 months. Then in 1997, Michelle Williams, Larry Williams’ daughter won the world cup using the same strategy as her father.

The equation for calculating %R is given by

%R = (Highest High – Close) / (Highest High – Lowest Low) * -100

Williams %R oscillates between -100 and 0. Generally speaking, values from 0 to -20 indicate overbought condition and values from -80 to -100 indicates an oversold condition. The default setting for Williams %R is 14 trading periods.

Take for example that the highest high is 36, the lowest low equals 31 and the close equals 35. The high-low range is 5 (36 – 31), the highest high minus close becomes 1 (36 – 35). The fraction than becomes 1 / 5 equals 0.2. Multiply 0.2 by -100 to get -20 for %R.

Low readings (values < -80) means that the stock is trading near its low for the given look back period. High readings (values > -20) means that the stock is trading near its high for the given lookback period. The below screen capture is from the spreadsheet used in this example. Column H and Column I contains the highest high and lowest low of Yahoo for the previous 14 trading days respectively. Column J is where the %R is being calculated using the above equation. The data was imported from Yahoo Finance Web Service for symbol YHOO between the period of Oct-28-2013 and Oct-27-2014.

williams-%R-datasheet

The chart below plots Williams %R vs daily closing price of Yahoo (YHOO) from Oct-27-2013 to Oct-27-2014. The trading oscillator moves between -100 and 0. Notice the chart closely. The YHOO example shows that 14 days Williams %R hitting overbought and oversold levels on regular basis. The stock was overbought from late Nov-2013 to Dec-2013. It dipped below -80 in January, March, April, and late June 2014 giving you a buy signal. The closing price then shot up in August 2014.

Keep in mind that overbought prices are not necessarily bearish. Stocks can become overbought and stay overbought during a strong uptrend. Similarly, oversold prices are not necessarily bullish. Closing levels near the bottom of the %R range can signal sustained selling pressure.

Williams-%R-chart

The VBA behind this spreadsheet calculates Williams %R from historical stock prices and automatically imports from Yahoo Finance Web API. You simply need to plug-in the symbol, from and to dates, and lookback period (defaulted to 14 trading days in this example).

Download Williams %R Spreadsheet here

More free spreadsheets at investsolver.com

Calculate MACD in Excel

Moving Average Convergence Divergence (MACD) is a popular trend-following momentum indicator. The MACD turns two trend-following indicators, moving averages, into a momentum oscillator by subtracting the longer moving average from the shorter moving average. MACD is calculated by taking difference between 12 day Exponential Moving Average (EMA) and 26 day EMA. A positive MACD means the 12-period EMA is above the 26-period EMA.

A trigger for buy or sell signals can be obtained when a 9 day EMA called the “signal line” is plotted on top of the MACD. Traders typically use the MACD as a simple crossover, so when the MACD crosses the signal line, they tend to buy or sell based on which way the cross appears.When MACD turns up and crosses over the signal line, bullish crossover occurs. Bearish crossover occurs when MACD turns down below the signal line. The difference between those two values can be plotted by a Histogram.

Calculation

MACD line = 12 day EMA Minus 26 day EMA

Signal line = 9 day EMA of MACD line

MACD Histogram = MACD line Minus Signal line

The following are the steps to calculate the MACD of Macys. We will explore it by using Excel VBA so you have all the required tools to get started. The spreadsheet with the accompanied VBA is available for download at the bottom of the page.

1) Get the historical closing stock prices. The accompanied VBA in the spreadsheet does it for you. Simply key in the Stock Symbol, Begin and End dates. In this example, the parameters are M (Stock Symbol for Macy’s), Start Date as Jul-01-2014, and End Date as Sep-30-2104.

2) EMA of the closing stock prices

There are two common setups for the MACD. The first is based on calculations using three-time periods : a 12-day, 26-day, and a 9-day time frames. The second is based on calculations using three different time frames: 8-day, 17-day, and a 9-day time frames. The time frames are basically trailing day averages. The MACD for the longer time frame is less volatile as compared to the MACD for shorter time frames. In this example, we will discuss computing MACD using 12-26-9 trailing day averages.

The equation for calculating a trailing 12-day average is

Macd-12day-EMA-equation

where the Period is 12. From the Excel screen capture, columns A and B contain date and closing stock prices. Column C contains 12 day EMA. Cell C13 contains trailing 12-day average. Cells C17 onward contains EMA values based on the above equation. Notice that the EMA is a function of previous day’s EMA and today’s closing price. The calculations are illustrated using this screen capture.

macd-sc-12day-ema

The same equation holds true for calculating 26 day trailing average, with period being 26 in this case. Column D illustrates the calculations behind 26-day EMA. The first value or cell D27 is average of the past 26 day’s closing prices, while cells D28 onward are given by the above formula.

macd-sc-26day-ema

4) MACD is the difference between 12-day EMA and 26-day EMA as depicted in column F.

A 9-day EMA of the MACD which is often called the “signal line”, is then plotted on top of the MACD. This serves as the trigger for buy and sell signals. The equation for calculating Signal Line is:

macd-equation

where the Period is 9.

macd-formula-sheet

The chart below is the plot of 12 and 26 day EMA for Macy’s. MACD is about convergence and divergence of two moving averages. As you can see below that the shorter moving average (12-day) is faster and drives the overall MACD movement. The longer moving average (26-day) is less reactive to stock price changes.

ema12-and-26-chart

 

macd-signal-chart

Download the MACD Calculator and Chart Spreadsheet here

More free spreadsheets at investsolver.com

Compute Sharpe Ratio using Excel

Sharpe ratio is a measure of risk and it is named after Nobel Laureate William F. Sharpe. The Sharpe ratio simply stated is a ratio of return vs risk. Given a set of investment choices, Sharpe ratio can help decide which investment makes the most of your money. It is defined as the ratio between effective return of an investment and its standard deviation.

RpRf / σp

where,

Rp = expected return on investor’s portfolio

Rf = risk free rate of return

σp = the portfolio’s measure of risk or standard deviation

Example…

Let’s assume the expected return from your stock portfolio is 14%. Now if the return on risk free T-notes are 3%, and your portfolio carries 0.05 standard deviation, then the Sharpe portfolio can be calculated as:

(0.14 – 0.03) / 0.05 = 2.2.

In other words, if portfolio A generates a 8% return with a 1.50 Sharpe ratio and portfolio B also generates a 8% return with 1.25 Sharpe ratio, then A is better portfolio because you are getting the same return with less risk.

The higher the Sharpe ratio is, the higher the return an investor gets by bearing a unit of risk. The lower the Sharpe ratio is, the more risk the investor is bearing to earn additional returns. There are few assumptions which can be misleading to the investors. The main fallacy is that the assumption of investment returns are normally distributed. Returns can be skewed  or have other characteristics not described by normal distribution.

How to Compute Sharpe ratio using Excel

1. In the first column, enter annual returns of your portfolio such as ETF or Mutual Fund. You can get this information from your investment banker or online sources.

2. In the second column, enter risk-free return rate. This can typically be T-notes.

3. In the third column, take the difference between risk free return rate and actual return. This is the excess return. Name this column as Delta.

4.  Do the following sub-steps

a. Take the average of the Delta or excess return

b. Take the Standard Deviation of Delta use STDEV function

c. Lastly, take the ratio between average of Delta returns and Standard Deviation. This is your Sharpe Ratio.

Download Sharpe Ratio Spreadsheet here

More free spreadsheets at investsolver.com

Stochastic Oscillator – How to Calculate using Excel

Stochastic or Stoch. Oscillator is a powerful tool for technical analysis that compares a stock’s closing price to its price range over a period. Developed by George C. Lane in the 1950s, the Stochastic Oscillator is a momentum indicator that shows the location of the closing price relative to the high-low range over a given number of trading periods.

Stochastic Oscillators does banded oscillations that fluctuate above and below two bands signifying extreme price levels. They are helpful for traders in identifying overbought and oversold conditions as the range varies between 0 and 100. It’s sensitivity can be minimized by adjusting the time period or by taking a moving average.

 

stochastic-oscillator-chart-RUT

Calculation using Excel VBA

The VBA code behind does the heavy lifting for you. Worksheet formulas can be used, however they are less flexible than VBA. The spreadsheet is very simple to use. You simply need to key in the following parameters:

1. Symbol or a Major Index – In this example, I ran the oscillator chart for Russel 2000 Index

2. Start Date

3. End Date

4. %K or Stochastic Oscillator period – The default setting is 14 trading periods, which can be days, weeks, months  or intra-day ranges. This is often referred as “lookback” period.

5. SMA period or %D – The default is for 3 days. %D or SMA is simply a 3-day moving average of %K

stochastic-sheet-parameters

The indicators are calculated using the following formulas:

%K =(Closing Price – Lowest Low)  / (Highest High – Lowest Low) * 100

%D = Arithmetic mean of 3-day of %K often called as smoothing period

%K for 14 day trading period would use the most recent close. %D is a 3-day simple moving average of %K and it is plotted next to %K to identify as a signal line. Since the stochastic oscillator is a bound based, you can easily spot overbought and oversold levels. Historically, 80 is considered as overbought threshold and 20 being oversold threshold. Readings above 80 for 14-day Stochastic Oscillator means that the underlying stock was trading near the top of its 14-day high-low range. Readings below 20 means that a stock is trading at the low end of its high-low range.

You start off the importing open-high-low-close data for the stock symbol or a major index. VBA downloads historical stock prices using Yahoo Web Service. The worksheet may look as below. Note: few columns have been made hidden for brevity.

stochastic-sheet-formulas

The formulas in the worksheet are basically as follows:

1. Column H or Highest High = MAX(C2:C15)

2. Column I or Lowest Low = MIN(D2:15)

3. Column J or %K = (E15 – I15) / (H15 – I15) * 100, since we are applying 14-trading days lookback period

4. Column K or %D = AVERAGE(J15:J17) which is simple last 3-day moving average of %K

stochastic-vba-1

The above code snippets from line 97 to 118 formulates the calculation inVBA format.

stochastic-oscillator-vba-2

Lines 126 through 152 defines primary, secondary and data axes of the chart.

stochastic-oscillator-vba-4

Lines 153 through 169 does labeling of axes,  sets the scaling sizes, and title of the chart.

Click here to download the Stochastic Oscillator spreadsheet

More free spreadsheets at investsolver.com