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. 



No comments:

Post a Comment