Excel Basics

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

Relative References

Date Dow Chemical S&P 500 Difference Difference Formula
1/4/10 5.46% 1.59% 3.87% =B2-C2
1/5/10 4.36% 0.31% 4.05% =B3-C3
1/6/10 1.76% 0.05% 1.70% =B4-C4

Absolute References

Interest Rate Year Balance Balance Formula
5% 2010 $1,000.00  
  2011 $1,050.00 =C2*(1+$A$2)
  2012 $1,102.50 =C3*(1+$A$2)

Single-Cell Formulae

RAND() returns a new value between 0 and 1 every time you recompute the workbook.

Description Result Formula
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()

Multiple-Cell Formulae

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
1/3/00 1394.46 25.75 -2.03% =LN(B3/B2) -16.42% =LN(C3/C2)
2/1/00 1366.42 21.85 9.23% =LN(B4/B3) 7.92% =LN(C4/C3)
3/1/00 1498.58 23.65 -3.13% =LN(B5/B4) 9.09% =LN(C5/C4)

This generates the following monthly returns data, which is used in the subsequent calculations.

Date S&P 500 Index Duke Energy Stock
1/3/00 -2.03% -16.42%
2/1/00 9.23% 7.92%
3/1/00 -3.13% 9.09%

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 by 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
standard deviation 4.47% 6.52% =STDEV.P(B2:B176)
annualized standard deviation 15.50% 22.60% =E4*SQRT(12)
minimum -18.56% -31.64% =MIN(B2:B176)
maximum 10.23% 20.09% =MAX(B2:B176)

Note that the R-Squared value (RSQ) is the square of the correlation (CORREL). INTERCEPT and SLOPE take an array of y-values first, followed by an array of x-values. For RSQ and 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)    
slope 0.49 =SLOPE(C2:C176,B2:B176)  
y-intercept -0.0051 =INTERCEPT(C2:C176,B2:B176)  
correlation 0.33 =CORREL(C2:C176,B2:B176)  
R-squared 0.111 =RSQ(C2:C176,B2:B176)  

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
1/3/00 1394.46 25.75 -2.03% -16.42%
2/1/00 1366.42 21.85 9.23% 7.92%
3/1/00 1498.58 23.65 -3.13% 9.09%

Solver Plug-in

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.

image-center

Year Cash Balance   Interest Rate
0 $50,000.00   6.3%
1 $53,132.93 =(1+$D$2)*B2  
2 $56,462.16 =(1+$D$2)*B3  
3 $60,000.00 =(1+$D$2)*B4  

This content is taken from my notes on the Coursera course “Mastering Data Analysis in Excel.” It is sponsored by Duke University and the course content is presented by Professor Daniel Egger.