In: Finance
Suppose the average return on Asset A is 7.1 percent and the standard deviation is 8.3 percent, and the average return and standard deviation on Asset B are 4.2 percent and 3.6 percent, respectively. Further assume that the returns are normally distributed. Use the NORMDIST function in Excel® to answer the following questions. a. What is the probability that in any given year, the return on Asset A will be greater than 12 percent? Less than 0 percent? (Do not round intermediate calculations and enter your answers as a percent rounded to 2 decimal places, e.g., 32.16.) b. What is the probability that in any given year, the return on Asset B will be greater than 12 percent? Less than 0 percent? (Do not round intermediate calculations and enter your answers as a percent rounded to 2 decimal places, e.g., 32.16.) c-1. In a particular year, the return on Asset A was −4.38 percent. How likely is it that such a low return will recur at some point in the future? (Do not round intermediate calculations and enter your answer as a percent rounded to 2 decimal places, e.g., 32.16.) c-2. Asset B had a return of 10.9 percent in this same year. How likely is it that such a high return will recur at some point in the future?
ASSET A: | ||||||||||||||
Mean | 7.1 | percent | ||||||||||||
Standard Deviation | 8.3 | percent | ||||||||||||
Return on asset greater than | 12 | percent | ||||||||||||
Cumulative value at 12% | 0.722526 | (Using NORMDIST function of excel with X=12, mean=7.1, Standard Deviation=8.3, Cumulative=TRUE) | ||||||||||||
Probability that return is greater than 12 | 0.277474 | (1-0.722526) | ||||||||||||
Probability in percentage | 27.75% | |||||||||||||
Note: This can also be calculated using Standard Normal Distribution Table as shown below) | ||||||||||||||
D=(X-Mean)/Standard Dev.=(12-7.1)/8.3 | 0.590361 | |||||||||||||
Using Standard Normal Distribution Table: | ||||||||||||||
Cumulative area under D=0.590361 | 0.7224 | |||||||||||||
Less Than | 0% | |||||||||||||
Cumulative value at 0% | 0.196159 | (Using NORMDIST function of excel with X=0, mean=7.1, Standard Deviation=8.3, Cumulative=TRUE) | ||||||||||||
Probability that return is Less Than 0% | 0.196159 | |||||||||||||
Probability in percentage | 19.62% | |||||||||||||
ASSET B: | ||||||||||||||
Mean | 4.2 | percent | 2.166667 | |||||||||||
Standard Deviation | 3.6 | percent | ||||||||||||
Return on asset greater than | 12 | percent | ||||||||||||
Cumulative value at 12% | 0.98487 | (Using NORMDIST function of excel with X=12, mean=4.2, Standard Deviation=3.6, Cumulative=TRUE) | ||||||||||||
Probability that return is greater than 12 | 0.01513 | (1-0.98487) | ||||||||||||
Probability in percentage | 1.51% | |||||||||||||
Less Than | 0% | |||||||||||||
Cumulative value at 0% | 0.121673 | (Using NORMDIST function of excel with X=0, mean=4.2, Standard Deviation=3.6, Cumulative=TRUE) | ||||||||||||
Probability that return is Less Than 0% | 0.121673 | |||||||||||||
Probability in percentage | 12.17% | |||||||||||||
ASSET A: | ||||||||||||||
Mean | 7.1 | percent | ||||||||||||
Standard Deviation | 8.3 | percent | ||||||||||||
Return on asset Equal to | (4.38) | percent | ||||||||||||
Cumulative value at (-4.38%) | 0.083312 | (Using NORMDIST function of excel with X=-4.38, mean=7.1, Standard Deviation=8.3, Cumulative=TRUE) | ||||||||||||
Probability that return is LESS than -4.38% | 0.083312 | |||||||||||||
Probability in percentage | 8.33% | |||||||||||||
Probability that such low return will occur again | 8.33% | |||||||||||||
ASSET B: | ||||||||||||||
Mean | 4.2 | percent | 2.166667 | |||||||||||
Standard Deviation | 3.6 | percent | ||||||||||||
Return on asset equal to | 10.9 | percent | ||||||||||||
Cumulative value at 10.9% | 0.968636 | (Using NORMDIST function of excel with X=10.9, mean=4.2, Standard Deviation=3.6, Cumulative=TRUE) | ||||||||||||
Probability that return is greater than 10.9% | 0.031364 | (1-0.98487) | ||||||||||||
Probability in percentage | 3.14% | |||||||||||||
Probability that such HIGH return will occur again | 3.14% | |||||||||||||