"arceaf" wrote:
I have created a table that lists returns of a security
over a given period of time. I have used formulas to
calculate the mean and standard dev of the returns.

Right now, I am using the norm.inv function
=norm.inv(.05, Standard Dev,Mean)
to come up with the minimum return @ a 95% confidence.

That is incorrect. Ignoring the typo, that does not determine a limit with
"95% confidence".


"arceaf" wrote:
Now, I want to come up with the MAX return at 95% confidence,
but don't know which formula I should use:
NORM.INV(0.95,Mean,Standard dev)
or
NORM.DIST(0.05,Mean,Standard dev,TRUE)

NORMDIST returns a probability, not a value in the distribution.

__If__ the security returns [sic] are normally distributed (TBD), 95% of the
security returns are between:

minRtn: =NORMINV(2.5%,mean,sd)
maxRtn: =NORMINV(97.5%,mean,sd)

However, that does __not__ mean we are 95% confident of those limits.

Usually, the confidence interval is around the mean.

First, we must calculate the std error of the mean:

se: =sd/SQRT(n)

That assumes sd is the __sampling__ std dev, using STDEV or STDEV.S.

Then the 95% confidence interval around the mean is:

minMean: =mean-NORMSINV(97.5%)*se
maxMean: =mean+NORMSINV(97.5%)*se

That assumes n is "large".

If n is "small", we should use the Student's t-distribution. Then the 95%
confidence interval around the mean is:

minMean: =mean-TINV(5%,n-1)*se
maxMean: =mean+TINV(5%,n-1)*se

Note: There is no agreement about what "large" and "small" are. But for
n=475, both NORMSINV(97.5%) and TINV(5%,n-1) are 1.96 when rounded to 2
decimal places, the common estimate of the multiplier for the 95% confidence
interval. On the other hand, there is no harm in always using TINV.

We might substitute minMean and maxMean into the minRtn and maxRtn formulas
above to determine the 95% confidence interval __for_each__.

But that is __not__ to say that we are 95% confident that 95% of the
security returns are between the smallest minRtn and the largest maxRtn, for
example.

To be honest, I am not sure how to specify a single range for 95% of the
security returns with 95% confidence. That is beyond (my) "basic
statistics". :-)

-----

Nevertheless, the minRtn and maxRtn formulas are predicated on the
__assumption__ that the security returns [sic] are normally distributed.

(That assumption is not required for the minMean and maxMean formulas, due
to the Central Limit Theorem.)

There are a variety of tests for normality. Not everyone agrees on which is
best, AFAIK.

I would simply create a histogram of the sample data and compare it to a
normal distribution the data to see if they are "close". Arguably, that is
subjective and error-prone.

However, "conventional wisdom" is that __log__ returns are normally
distributed, not __simple__ returns. I don't know which you mean by
"security returns".

And usually, that expectation (normal distribution) applies to an asset
__class__, not necessarily an individual security.

If the security returns are not normally distributed, I would use the
following formulas if we have the original sample data:

minRtn: =SMALL(A1:An,n*2.5%)
maxRtn: =SMALL(A1:An,n*97.5%)

where A1:An represents the range of n data starting in A1.

Does that help? Or TMI?