## Tuesday, September 27, 2016

### Calculating Confidence Intervals In Excel

Calculating Confidence Intervals In Excel

Question:   Using data on the price of Vanguard fund VB over the 7/1/2016 to 9/26/2016 time interval calculate a 90 percent confidence interval for the mean of the ETF price during this time period.  (You can get this data and data for other time intervals from Yahoo finance.)

For purposes of this post assume that the standard deviation of the price data is 1.995 and the sample size is 60.

Calculations:  Excel has a function call Confidence that calculates the width of the confidence.   The arguments of confidence are alpha, standard deviation and sample size.  The alpha for a 90% confidence interval is 0.10.   The standard deviation and sample size are calculated from the stdev.s function and the count function respectively.

Plugging the values of alpha equal to 0.10, Std=1.995 and Count=60 into the confidence interval I find that half the width for the confidence interval is 0.424.  This gives us a lower bound of 120.47 and an upper bound of 121.32.

 Confidence interval  Calculations in Excel alpha 0.100 Mean 120.897 Std 1.995 Count 60.000 confidence 0.424 LB 120.473 UB 121.321 Check of Confidence 0.424

You can check the calculation for half the width of the confidence interval by using the formula for creating a confidence interval.  Note abs(Norm.s.inv(0.05) x STD/n0.5) is 0.424.

Note that Excel has versions of the confidence function that use the normal distribution and versions that use the t-distribution.   This problem relied on the normal distribution.