Question

Yearly Returns for stocks is normally distributed  around a mean of 9% with a standard deviation of 12%. A random sample of 2000 stock returns is shown on the Stock Returns worksheet.

(6) (To three decimal places) what is the probability of a stock having a negative yearly return?

(7)(Tothree decimal places) what is the probability of a stock having a yearly return between 5% and 8%?

(8) (To three decimal places) What is the relative frequency of stocks, from the random sample on the Stock Returns worksheet, that had a negative yearly return?

(9) (To three decimal places) What is the relative frequency of stocks, from the random sample on the Stock Returns worksheet, that had between 5% and 8% for a yearly return?

(10) (To three decimal places) what yearly return is exceeded 80% of the time?

Can you tell me which excel function to use for each question and how to solve it?

Answer 1

Solution Q6 and Q7

Let X = Yearly Returns (in %) for stocks. Then, we are given X ~ N(9, 12²) ………………… (1)

Back-up Theory

If a random variable X ~ N(µ, σ²), i.e., X has Normal Distribution with mean µ and variance σ², then, Z = (X - µ)/σ ~ N(0, 1), i.e., Standard Normal Distribution and hence

P(X ≤ or ≥ t) = P[{(X - µ)/σ} ≤ or ≥ {(t - µ)/σ}] = P[Z ≤ or ≥ {(t - µ)/σ}] .………........………...…(2)

Probability values for the Standard Normal Variable, Z, can be directly read off from Standard Normal Tables ………… (3a)

or can be found using Excel Function: Statistical, NORMSDIST(z) which gives P(Z ≤ z) …..............................................(3b)

Now to work out the solution,

Q6

Probability of a stock having a negative yearly return

= P(X < 0)

= P[Z < {(0 - 9)/12}] [vide (2) and (1)]

= P(Z < - 0.75)

= 0.2266 [vide (3b)] Answer

Q7

Probability of a stock having a yearly return between 5% and 8%

= P(5 < X < 8)

= P[{(5 - 9)/12} < Z < {(8 - 9)/12}] [vide (2) and (1)]

= P(- 0.3333 < Z < - 0.0833)

= P(Z < - 0.0833) - P(Z < - 0.3333)

= 0.4668 – 0.3695 [vide (3b)]

= 0.0973 [vide (3b)] Answer

Now to respond to the specific query, ‘which excel function to use’,

The appropriate Excel function is: Statistical, NORMSDIST.

A concise write up on how to use this function is given below:

Method to find probability using Excel Function for Standard Normal Distribution

In Excel Worksheet, click ‘f_X’. Screen displays ‘Insert Function’ In the window ‘Search for a function’ type ‘NORMSDIST’ and click ‘Go’. Under ‘Select a function’, NORMSDIST appears. Click ‘Ok’

Screen displays just one window, z. Against ‘z’ window, type the z value for which probability is required. Required probability, is displayed below the window against ‘=’ sign.

Note that the above probability value is the cumulative probability of less than or equal to the z-value typed in the window.

Examples

To find P(Z ≤ - 0.0833), type -0.0883 in z-window and display would be 0.4668.

Similarly, to find P(Z ≤ - 0.3333), type -0.3333 in z-window and display would be 0.3695.

DONE

Unable to give solutions for Q8 to Q10 since Stock Returns worksheet data is not given along with the question.