Excel can save an investor a ton of money. The idea here is to use excel to analyze potential hedge positions to add to an investors portfolio in order to protect the portfolio’s returns from drops in the stock market. InvestorWords.com defines a hedge as, “an investment made in order to reduce the risk of adverse price movements in a security, such as an option or a short sale.”

Downloading the DataUsing excel an investor is able to pick out an ETF that will best protect the portfolio from “adverse movements in the stock market”. The first step towards figuring this all out is to download the historical data on closing prices on all the stocks in an investors portfolio. Servers such as Yahoofinance.com have links to historical data.

Calculating the portfolio’s returnThe next step in the process is to calculate the portfolios “daily” returns. To do this simply take ((X2-X1)/(X2)). For example, suppose X1 represents the stocks closing price today, X2 would represent what the stock closed at yesterday. Below is an example. The investor would calculate the return for each stock included in their portfolio.

The user would then create a column in excel labeld “portfolio return”. In this column use the “sum” function in excel and add the returns from each column. Now the investor is ready to analyze the best position to hedge their portfolio and hopefully save alot of money in the process.

Finding the Best ETF to Hedge the PortfolioUsing Yahoo Finance the investor will now investigate potential candidates. Using “inverse ETF’s” is usually the best place to start searching. InvestorWords.com defines an ETF as an “exchange traded fund that tracks an index and can be traded like a stock.” Notice the word “inverse” this means the ETF will move opposite to the index that it tracks. This will protect the investors portfolio from “adverse price movements” in the stock market.

Suppose the investor has a portfolio made up of two stocks, JNJ and PG for simplicity. Now suppose the investor decides to use the ETF under the ticker symbol SDS because the investor thinks the market is about to crash. The investor will calculate the returns of SDS and put them in a column next to their column named Portfolio Returns. Below is what the set up should look like. Notice as the portfolio returns go down the returns for SDS go up.

Now the investor need to figure out how much weight to allocate to SDS to maximize their return and minimize the drawdown their portfolio will experience. To do this add the portfolio returns to the hedge or SDS returns. This gives the investor their return assuming they have invested in SDS.

The goal of the hedge is to minimize the variance of returns around 0. The investor therefor wants the return of the portfolio to be 0. This sounds weird but if the investor is expecting the market to crash a return of 0 is alot better than a negative return.

To do this the investor is going to want to set up the following equation:

((WP* BP)+(WH*BH))

Where WP is the weight in the portfolio

BP is the beta of the portfolio against the hedge

WH is the weight of the hedge

BH is the beta of the hedge