Using Excel for Asset Allocation
Using EXCEL for Asset Allocation and Performing
Market Efficiency Studies
1. Introduction
There are several features in EXCEL that are very useful for asset allocation and performing market efficiency studies. These features used in conjunction with live or historical data allows one to apply the sophisticated techniques discussed in this class in practical situations. This write-up will introduce students to the various mathematical and statistical functions available in
EXCEL and explain how to use them. In section 2, I will discuss the various array (matrix) functions available in EXCEL that are useful for data analysis and asset allocation. I will also introduce you to the capabilities of the SOLVER function in EXCEL and take you through a hypothetical example dealing with allocating wealth efficiently across three assets. In Section 3, we will discuss the statistical functions available in EXCEL, which can be used for running regressions, conducting hypothesis tests, etc.
2. Mathematical and Statistical Functions in EXCEL
There are two sources of statistical and mathematical tools in EXCEL. One is the function wizard and the other is the Data Analysis option under Tools.1 The function wizard has several useful functions under Math & Trig and Statistical. The Math & Trig option has several useful array (matrix) functions that are very useful for solving asset allocation problems. I explain below how to enter array formulas in EXCEL.
2.1 Array (matrix) functions in EXCEL
Suppose you have the following 5 entries in a column or a row in the spreadsheet: 1,2,3,4, and
5. You want to compute the square root of each number and place the result in the adjacent column or row. One way to do this is to use the square root function SQRT for each cell and copy the cell down or across to compute the square roots of all numbers. For a large number of entries this can be cumbersome. Alternatively, you can enter one array formula for the entire
range
Market Efficiency Studies
1. Introduction
There are several features in EXCEL that are very useful for asset allocation and performing market efficiency studies. These features used in conjunction with live or historical data allows one to apply the sophisticated techniques discussed in this class in practical situations. This write-up will introduce students to the various mathematical and statistical functions available in
EXCEL and explain how to use them. In section 2, I will discuss the various array (matrix) functions available in EXCEL that are useful for data analysis and asset allocation. I will also introduce you to the capabilities of the SOLVER function in EXCEL and take you through a hypothetical example dealing with allocating wealth efficiently across three assets. In Section 3, we will discuss the statistical functions available in EXCEL, which can be used for running regressions, conducting hypothesis tests, etc.
2. Mathematical and Statistical Functions in EXCEL
There are two sources of statistical and mathematical tools in EXCEL. One is the function wizard and the other is the Data Analysis option under Tools.1 The function wizard has several useful functions under Math & Trig and Statistical. The Math & Trig option has several useful array (matrix) functions that are very useful for solving asset allocation problems. I explain below how to enter array formulas in EXCEL.
2.1 Array (matrix) functions in EXCEL
Suppose you have the following 5 entries in a column or a row in the spreadsheet: 1,2,3,4, and
5. You want to compute the square root of each number and place the result in the adjacent column or row. One way to do this is to use the square root function SQRT for each cell and copy the cell down or across to compute the square roots of all numbers. For a large number of entries this can be cumbersome. Alternatively, you can enter one array formula for the entire
range