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.
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/n^{0.5}) is 0.424.
Note that Excel has versions of the confidence function that
use the normal distribution and versions that use the tdistribution. This problem relied on the normal
distribution.
No comments:
Post a Comment