Despite not being considered a “serious” analytics tool due to its accessible nature and the limited data volume it can handle, Excel is still the most broadly used tool for data manipulation. This note contains fundamental information about how to use it.
Types of References
|Date||Dow Chemical||S&P 500||Difference||Difference Formula|
|Interest Rate||Year||Balance||Balance Formula|
RAND() returns a new value between 0 and 1 every time you recompute the workbook.
|log to the base “e” of 1.5||0.4055||=LN(1.5)|
|log to the base 2 of 1.5||0.5850||=LOG(1.5,2)|
|1 divided by the square root of pi||0.5642||=1/PI()^0.5|
|random number between 0 and 1||0.1598||=RAND()|
The continuously compounded monthly return rate is calculated by taking the natural log of the ratio of each month’s closing prices, with the more recent month in the numerator:
|Date||S&P 500 Index||Duke Energy Stock||S&P 500 Index||Formula||Duke Energy Stock||Formula|
This generates the following monthly returns data, which is used in the subsequent calculations.
|Date||S&P 500 Index||Duke Energy Stock|
Note that the standard deviation function used was
STDEV.P. There are other options for how to calculate standard deviation in Excel, such as
STDEV.S. If the data you are manipulating represents the entire population,
STDEV.P is the appropriate formula.
Note that to convert the standard deviation of the monthly return to the
standard deviation of the yearly return requires multiplying the monthly value
SQRT(12), not 12, as is the case for converting average monthly returns to average annual returns. This is a property of the standard deviation in statistics.
|S&P 500 Index||Duke Energy Stock|
|average monthly return||0.18%||0.59%||=AVERAGE(B2:B176)|
|average annual return||2.21%||7.14%||=E2*12|
|annualized standard deviation||15.50%||22.60%||=E4*SQRT(12)|
Note that the R-Squared value (
RSQ) is the square of the correlation (
SLOPE take an array of y-values first, followed by an array of x-values. For
CORREL, the computation works out the same way regardless of the order they are entered.
Duke Energy is plotted on the X-axis; S&P 500 on the Y.
|Duke on S&P 500 (X on Y)|
Scatter Plots and Trend Lines
The monthly closing prices and monthly continuously compounded returns for the S&P 500 and Duke Energy are presented below. The data itself is a 14-year period beginning January of 2000, but only a few data points are shown. All the rows of returns data are included in the plot that follows the table.
|Date||S&P 500 Index||Duke Energy Stock||S&P 500 Index||Duke Energy Stock|
You have $50,000. What single, fixed annual interest rate would you need to earn in order for your money to grow to $60,000 in three years? Assume annual compounding.
|Year||Cash Balance||Interest Rate|