Physical Real estate investment risks (even in New Zealand!)

A lot of old-timers I've met believe in brick-and-mortar real estate investments, mainly because of something along the lines of "(ignoring maintenance, tax costs, inflation) my house's value has grown so much since I bought it in 1982... " This has led some to blindly take on heavy mortgages under the assumption that it's an easy, risk-free, leveraged profit, i.e. an indirect bet that hurt guys like Lehman Brothers.

NZ real estate historical returns

NZ Reserve Bank

We can see that of the past 23 years, about 7 (30.4%) had average house prices move negatively. On a leveraged upside bet, this could hurt significantly.

Fungibility and risks in physical real estate

Because real estate isn't fungible, physical property traders must manage a number of risks that do not exist within liquid financial products.

Turnover of US real estate vs. Stocks, Property Derivatives Pricing, Hedging and Applications

1. Turnover in real estate market is much lower than most security markets, hence forcing traders/investors to take relatively long positions.
2. In market downturns, the lack of liquidity may force the investor to accept significantly lower bids, if any exists, than desired. 
3. If financing terms change unfavorably within the mortgage period, the lack of liquidity may force the investor to accept significantly lower bids, if any exists.
4. Physical real estate requires a ton of due diligence, a hefty implied expense at times
5. Physical real estate often involves very high transaction costs, again forcing some traders/investors to take relatively long positions.
6. The bid/offer spreads are significantly large. 
7. An investor who manages a portfolio of physical properties today would likely find it difficult to shift exposure from one sector of the market to another for the purpose of risk management.
8. It is usually not possible to take a short position in physical real estate, making market risk hedging very difficult. 
9. Risk of asset mismanagement is inherent with any 3rd party business, fund (or an uncle who's an alleged expert).

Excel Data Analysis 4.1 (Day-of-the-week, OVX)

Here we look at the day to day change in OVX (CBOE Crude Oil ETF Volatility Index) with respect to day of the week. According to academic theory, theta decay progresses smoothly with respect to time; if that is true, then making theta over the weekends would be practically free money. Let's see if that is exactly what most of the USO traders are doing.

Data preparations
OVX daily data is available at wikiposit. I like the way the data is already sorted going from top to bottom. For excel analysis, we download the csv format.

Again, we create space at the top for sample statistics for a feel of the data (Excel Data Analysis 1.0). Then we can create a column for the change in OVX "dOVX", I don't think there's need for logged returns here since implied volatility is already a percentage value. So dOVX is simply the difference between the latest value and the previous day's, i.e. OVX(t) - OVX(t-1).

 and we get this:

So we can see that like VXN, OVX has an upside skew.

Day of the week test

Next we create a column "WeekDay" in D with the weekday() formula. Keep in mind the default value for Sunday is 1, so to keep the values more "user friendly" I had subtracted each cell by 1.

Then we can use the following columns (E - I) for each day of the week. We will label the headers of these columns by the day of the week, 1 - 5. Then we could enter the formula in a single cell, and copy/paste it into the rest of the cells all the way down.

Logic: we check if the current row's weekday matches the column header, if so, we record that day's dOVX, if not, the cell leaves it blank. The $D14 means as we copy/paste this, it'll keep this value in column D, and for E$1, it keeps these values in the top row (column headers) as we copy/paste them.

Once we copy/paste that cell all the way through to I741, we end up with this

So we can see that there is a pretty significant weekend effect here, that traders tend to sell on Fridays hoping to get that "free theta", and buying them back on Mondays.

A way to potentially exploit this would be to do the opposite, i.e. buy vol near the end of Fridays, and sell on Mondays. Is this an edge that is likely to persist into the future? I'd think so, since blind faith in academic finance is relatively strong in today's cultures. However, whether this edge would likely overcome transaction costs and other implementation shortfalls would require more analysis.

Excel data analysis 4.0 (Index Vol Trades)

Here we look at a multi-parameter index option trading logic, using MAD (Median Absolute Deviation) with linear Realized Volatility forecasts(Excel Data Analysis 3.3). Stemmed from Robust Statistics, I like MAD mainly due to the fact that financial data distributions are usually not Gaussian.

Theory
Mentioned in the VIX white paper, VXN is a weighted average of Out-of The-Money NDX option implied volatilities. We can then estimate that an edge gained on the VXN can be realized in units of vega. So in this case we will see about getting an edge in the next-day's change in the VXN.

Data
For this post we will use data off VXN (CBOE NASDAQ100 Volatility Index) and QQQ (Powershares NASDAQ100 Index ETF). QQQ daily prices would be needed for realized volatility and MAD. For this example we use End-of-Day values.  
Data Range: 1/23/01 - 11/26/12

Once we get the data off Yahoo Finance, the first thing we need to do is get both series on the same page. Then we must match the dates.
What I do is to create a temporary column where it gives a 1 if the dates match for that row, and 0 if not. Then a quick scroll down would let us catch mismatches and delete the unmatched dates.

Once the dates are matched, we could delete the second "Date" column, and sort the data so that the latest data ends at the bottom. As mentioned earlier (Excel Data Analysis 1.0), I then inserted some rows near the top for basic statistics.

Deriving MAD (Median Absolute Deviation)
We first need the day to day changes of QQQ, here named "dQ" in column D. Again we use logged returns for this, 100* ln[X(t)/X(t-1)]  (why logged returns?). I multiplied it by 100 so we don't need to use the % symbol.

Now let's get the MAD(20)

Since my data here starts at row 13, and I've decided to do a 20Day MAD, we will utilize a rolling 20row window off "dQ". We still need to multiply the value by Sqrt(252) for annualization.

1 way to use MAD(20) is to analyze the ratio, VXN/MAD, to estimate whether these options are cheap or expensive. I created "rto.M" in column F for this.

Fundamentally, we already know that as insurance, options are usually traded at a premium so to compensate option sellers for the risk. From the screenshot, we can see that VXN has been above 2x MAD roughly 50% of the time.

Next I added columns for the standard squared realized volatility "RV" and linear forecast "FcRV" explained in an earlier post (Excel Data Analysis 3.2). We end up with this:

Multi-parameter strategy set up

We will need separate sections for longs and shorts. Since the logic/formulas are the same, I will only do the shorts here. The basic idea is to derive signals, and match them with the following day's change in VXN.

Since we have 2 parameters, "rto.M" and "FcRV", we will need a 3rd column for the signals.

(FcRV), here we see if forecast volatility (FcRV) is greater than current RV by at least the threshold in cell K13. I used "0" for threshold so that we get a signal off this filter for every upside forecast. It gives a 1 for signal, blank for no signal.

(rto.M), here we see if the VXN/MAD (rto.M) value is above the threshold value in cell L13 so that we don't enter trades when options are too cheap. I used "1.6", the lower quartile value. It gives a 1 for signal, blank for no signal.

Signals, so basically this checks if the sum of the above signals are 2; if so a trade signal is given, if not it is blank. The "Count" cell in M10 gives the total number of signals here.

Deriving average returns, equity curve

Next we create a column for the future 1Day change in VXN, "dVXN". Note that in row 32, we used data from row33, therefore this data reaches down to 1 day before last, i.e. 2 trading days ago.

In column O then we could estimate our VXN edge, or units of Vega/Trade "Vega/T". We use the if() function, basically if a signal is given, we look at dVXN, if not, it's a blank.

For the equity curve, "Eq.Curve", it adds to its latest value by the Vega/T if a signal is given.

We can see that this edge offers the option trades an average of 0.17Vega / trade, before transaction costs.
With the signal count, it roughly gives a trade every 3 days or so.


I prepare to print the equity curve with the 2-D Line chart. Make sure you also have the date column highlighted, to have the date in the chart. To do this, you can highlight 1 column, then press Ctrl and click the header of the 2nd column. So here is the estimated Vega possibly earned cumulatively off the above signals BEFORE transaction costs.

Historical performance stats 

Win%: This cell checks if there's a signal, if so, if it's a winner it gives a 1, if not a 0.

Wins: Here if the signal is a winner, we record it.

Losses: Here if the signal is a loser, we record it.

W/L Ratio: This gives the average Winner/Loser size ratio by taking the absolute value of the (average Winner)/(average Loser)

Let's see the stats

So the numbers are encouraging. However one must keep in mind that these estimates are before transaction costs and considerations of risks around liquidity, execution precision, and etc. Most of all, one must realize that the worst losing trades are never behind, but rather ahead of us in the future. Therefore to apply this logic for actual trading still requires work around effective risk and implementation shortfall management.

Feel free to contact me for questions, advise around excel modeling, trading. 

The EMH economist and the $100 bill

EMH: Effiicient Market Hypothesis

An economist with a student walk down the street, and come across a $100 bill. The student bends down to pick it up; yet the economist stops him, "don't bother, if it's a REAL $100 bill, somebody else would've taken it already."

Excel Data Analysis 3.2 (Volatility Forecasting off Linear Regression)

Today we look at forecasting RV( Realized Volatility) using linear regression for stock/index options trading. Generally speaking, an edge in estimating future realized volatility can be realized in units of vega off traded options; see Derman's paper, Trading Volatility, for details.

I'm still using the IYR data, and have updated the data (manually) up to Nov. 21, 2012.

Setting up linear regression

We need to match each realized volatility value from each day (row) with the future volatility, in this case I've picked the next 10 day. The calculation is exactly like the 20Day realized volatility, just instead of the past 20 days squared returns, we're using the coming 10days into the future. Logically, this column completes 10 trading days prior.

The future 10 day realized volatility is placed in the column F, RV.F10

 

Next we highlight the columns going from (E10, F10) down to the last row of RV.F10 at (E3127, F3127), and use the scatter plot.

Great, now the graph is up, we will have the 20Day RV on the X-axis, and RV.F10 on the Y-axis. Onto the regression fits, we right click the data on the chart and choose "add trendline". In the trendline menu, we want "Linear" type, and display "Equation on chart", "R-squared value on chart".

Remember that awesome Slope-Intercept Form, Y = mX + b, back from basic algebra? Well here it is again in the equation. As for the R-squared value, or Coefficient of Determination, the higher it is the better the equation represents the forecasting relationship.








Adaptive slope/intercept  values can be utilized as we update the excel worksheet day to day. This is so we wouldn't need to plot a graph every time for these values.

And we can see that the numbers are identical.











Great, now we can create a column, G, for realized volatility forecast values, I've named it FC(RV,10). Each day's next 10Day RV forecast would use the latest day's 20Day RV with the slope + intercept values.

So let's see how much of an edge this model offers us.

We can a column in H, for the day to day change in RV, named dRV. Then in columns I, named RV.u(FC) we can check each day's change in realized vol (dRV) if the previous day's forecast was higher than the previous day's ending RV. Vice versa for column J.

Note that in the if statement, I've used "" for value if false. Basically, it either gives the dRV value, or leaves it blank if false.

We can see from the average values that both directional forecasts had a slight edge, with the downside slightly more. Therefore in actual trading, I would not rely on this edge alone, but use it as a baseline bias for a feel of coming volatility.

Excel Data Analysis 3.1 (Volatility Filtering)

So here we apply a realized volatility (RV) filter as a watered down, simple form of quantitative risk management, to hopefully lower the negative swings of an invested asset. Let’s use the REIT, IYR again for this example. 

Cumulative Returns

Let’s create a column in our spreadsheet for Cumulative Returns, Rcumu, where we add up all the day to day log returns. Excel formula:

To chart a buy and hold return of IYR without having to include quartile numbers, we can scroll all the way down, highlight cell “A10”, then hold Shift and left click cell “B3134”.  Then at the top go to Insert -> Line -> 2D lines. 

And we can see that return off a purely buy and hold scheme would have experienced extremely high volatility. 

Let’s see if we can reduce the risk with a RV filter. The idea is to make it so that no position is held if the RV becomes greater than a certain threshold. In the example worksheet, I’ve used the cell “I3” for the threshold, and used 50% as a starting point. Created a new column, Rcumu*, for the cumulative returns based on the volatility threshold. 

Excel formulas  

 

Now we can compare the cumulative returns between with and without the 50% volatility filter.

And we can see that for this asset, in the sample period, the volatility filter reduced the buy and hold unrealized swings (risk), and increased over all expected return over time.

Renounce US citizenship, and make $

"
Facebook cofounder Eduardo Saverin has renounced his US citizenship. Doing so, he will probably save at least $67 million in taxes.
" 

 
WSJ article link 
Saverin isn't alone in this out-of-the-box thinking.
"
Last year, almost 1,800 U.S. citizens turned in their passports and green cards, a sixfold increase from 2008. 
"

As the future of US municipal debt look relatively bleak, local governments have expectedly escalated tax burdens upon the taxpayers. This however has not spooked the investors necessarily, according to Barron's Advisor Sentiment Polls.
 

Excel Data Analysis 3.0 (Realized Volatility, RV)

In this part we will look at calculation of Realized Volatility, henceforth RV, according to Nassim Taleb's Dynamic Hedging. We will look at iShares Dow Jones US Real Estate (IYR).

Variables definitions
dX : change in adjusted closing value
dXsq: squared returns
RV(20D): realized volatility off last 20 days of squared returns (annualized)
*here we use the standard 252 trading days/year value for the annualization.

So we get these stats:

From this we can see that real estate values could get quite volatile, and the quartiles suggest an upside skewness in the RV.

We can check it out with the skew() command. Excel Formulas

and here're the skewness values:

We can see from the skewness that like equity indices, IYR day-2-day returns have a negative skew. Its RV does indeed show a very fat tail on the upside, 25% of the time it's anywhere from 26% - 158%.

We will get into RV forecasting and associated risk management in future posts.

Excel data analysis 2

I will go over using excel to back-test a very simple mean reversion pattern in this part. It will utilize the If() function.

About the data
DIA (SPDR Dow Jones Industrial Average) will be used for this example. We have the data going from 1/20/98 to 11/14/2012. We will look at the change between daily opening and closing prices, using logged returns: 
 dX(O,C) = ln(C/O)

Where 
dX(O,C) : change in price from open to close
O : Opening Price
C : Closing Price

Excel formula :
*The Count() function gives the number of rows of data we have, in this case 3,734.
and we end up seeing this: 

So we can see that the average price move from open to close has been pretty close to 0 where a few more ups days had occurred than down days.

Theory around mean reversion

Since we know that the average Open to Close moves have been about 0, then would an up day follow a down day, and vice versa? 

We want the spreadsheet to give each day's dX(O,C) if the previous dX(O,C) was negative, and -dX(O,C) (to similate a short position) if the previous dX(O,C) was positive. Two columns are created for the Longs-> MR(Longs), and Shorts-> MR(Shorts)
*MR stands for Mean Reversion

The If() function get this done.

 and we end up seeing this: 

We can see that for both Long and Short signals, a slight edge had existed. Whether this would have overcome transaction costs is however uncertain.
 

excel data analysis 1.0 (data sorting, general stats)

This is a series of articles to demonstrate use of Microsoft Excel for financial data analysis; it will cover a pretty wide range of functions such as volatility forecasting, backtesting, cross-product analysis. I want to share some of the things I’ve found useful, hopefully for the readers too.

This post deals with an overview of historical data for NASDAQ listed CSCO, Cisco Systems

Getting the historical data

Historical daily prices are available at Yahoo Finance. I got the data going from Mar. 26 1990 to Nov. 13 2012, about 5,700 days (rows) of data. It is usually a good idea to save the data in .xlsx format (different if you don’t use office 2007) instead of the default .csv so that the later applied formulas would be retained.  

Sorting data vertically

The default spreadsheet from yahoo has the latest values at the top and the earliest bottom. I like my data the opposite, feel free to skip to next part unless you also want your data going from top to bottom. To do this, we highlight all the data EXCEPT the column headers (top row), and click on Sort.

Overview of quartile statistics

To keep this simple, I’ve kept only the Adjusted Closing values for the sample period.

I usually insert a few rows at the top so that we could have room to see the quartile and average values for each column of data. In this case I highlighted rows 2 – 10 for the insert, and hit Freeze Pane so that these rows would always show when we scroll down the spreadsheet.

Why Quartiles and not Standard Deviations? Because financial data sets are usually not explained by normal/Gaussian distributions. The excel quartile formula set allows us to see each data column’s 25% ranges going from minimum, median, to maximum. It provides a much more practical overview of the data from a risk management perspective. From the above screenshot, we can see that since inception, CSCO has spent about 50% of the time above $16.6/share, with the average price at $15.86/share.

Excel formulas (press Ctrl + ` to make formulas show in excel):

Note that I've made the ranges reach 11,000+, it is so I wouldn't have to update the formulas until the spreadsheet exceeds that many rows.

dX (change in price)

The change in price, here I use 1Day per time step, is needed for lots of more complex analysis. Sticking with mathematical finance, I use the logged return formula ln[X(t)/X(t-1)] where

X = last adjusted closing price

t =  time step units, in this case the day where X is observed, and X(t-1) would be the price from the previous day.

Excel formulas (press Ctrl + ` to make formulas show in excel):

So, we end up looking at this:

From the above we can immediately notice a few things about CSCO. It has grown significantly since inception, with the average day to day return at 0.09% before transaction costs and etc. From the quartile analysis, we can see that 50% of the time it gets quite volatile, where the single day moves have exceeded 20%.

Most importantly however, you must keep in mind that the state of the company today is in no way identical to where it was in March of 1990. Therefore, this 0.09%/day return is highly unlikely to persist into the future.

I will finish this post at this point. Feel free to contact me for any help with excel data analysis.