In the previous article, we discussed how modern portfolio is relevant for investors looking to minimize their risk while maintaining or increasing their returns.
In this article, we’ll identify the set of assets that will comprise our portfolio. We’ll use this mix of investments throughout the series to create the efficient frontier, conduct historical tests, and test the theory in real-time. Our portfolio consists of of the following.
- SPY (SPDR S&P 500 ETF) – Representing large cap stocks
- IWM (iShares Russell 2000 Index Fund) – Representing small cap stocks
- VWO (Vanguard Emerging Markets ETF) – Representing emerging markets
- EFA (iShares MSCI EAFE Index Fund) – Representing European, Australian, Asian, and Far Eastern stocks
- LQD (iShares iBoxx $ Invest Grade Corp Bond) – Representing public, investment-grade, taxable, fixed income securities
- HYG (iShares iBoxx $ High Yield Corporate Bd) – Representing public, non-investment-grade, taxable, fixed income securities
Risk Free Rate
- The yield on the 1 year US treasury bill
Portfolio Data Set
We will use Microsoft Excel to create an efficient portfolio in this article; however, it is important to note that although this is a widely used application there is a better alternative. The R statistical tool is a open-source application that is available for most operating systems. It is a powerful and flexible alternative to Excel and is used by professionals and academics to perform complex statistical analysis. Check out this article for instructions on how to use R to quickly create an optimal portfolio.
We’ve prepared the following Excel file that contains the data set we’ll use to construct the optimal portfolio. The spreadsheet contains a number of worksheets, each formatted for easy reference.
Click here to download the file: Portfolio Theory – Investment Data – Monthly
The file (which can be downloaded by clicking the link above) contains the following data:
- Historical monthly prices of the six ETF’s (SPY, IWM, VWO, EFA, LQD, and HYG). We’ve chosen the end of January, 2010 as the cut-off for our data (the last trading day was January 25, 2010). The data includes the monthly data for each ETF going back to January, 2000 or when the fund was launched.
- Historical monthly US treasury bill yields for bills maturing in 1 month, 3 months, 1 year, etc. For our analysis, we’ll be using bills that mature in 1 month. The data goes back to January, 2000 and is current through January 25, 2010.
We’ve also included some preliminary statistical summaries, including each fund’s expected monthly return and the standard deviation of the returns. Additionally, we’ve constructed a histogram for each fund to show how the monthly returns are distributed. (Although most of the funds exhibit a distribution that could be approximated as normal, they all exhibit fat tails.)
We have also created the brief video below that provides an overview of the Excel file. You may find it helpful to download the file above and then watch this video.
In the next article, we’ll use these data to create covariation tables. These tables help to explain how related the movements in one stock price are to the others. We can use these relationships to help pick combinations of investments that don’t move together all the time. (Obviously, we care less if they move up together. We really care if they move down all together.)