In: Finance
Problem 10-28 Using Probability Distributions [LO 3] Suppose the returns on long-term corporate bonds and T-bills are normally distributed. Assume for a certain time period, long-term corporate bonds had an average return of 5.8% and a standard deviation of 8.9%. For the same period, T-bills had an average return of 4.3% and a standard deviation of 3.1%. Use the NORMDIST function in Excel® to answer the following questions: a. What is the probability that in any given year, the return on long-term corporate bonds will be greater than 10 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 T-bills will be greater than 10 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. In one year, the return on long-term corporate bonds was −4.7 percent. How likely is it that such a low return will recur at some point in the future? T-bills had a return of 10.62 percent in this same year. How likely is it that such a high return on T-bills will recur at some point in the future? (Do not round intermediate calculations and enter your answers as a percent rounded to 2 decimal places, e.g., 32.16.)
Answer -
The Excel NORMDIST function calculates the Cumulative Normal Distribution by specifying TRUE as the type of distribution to be used.
The syntax of the function is:
NORMDIST(x, mean, standard_dev, cumulative)
Where-
x is the value at which distribution function is evaluated
mean is the average of the distribution
standard_dev is the standard deviation of the distribution
cumulative is the logical argument specifying TRUE as the type of distribution to be used
In the question following information is given:
Long-term corporate bonds | T-bills | |
Average Return (mean) | 0.058 | 0.043 |
Standard Deviation (standard_dev) | 0.089 | 0.031 |
Answer - a
Using Excel NORMDIST function -
Probability of Long-term corporate bonds return less than 10% = NORMDIST(0.10, 0.058, 0.089, TRUE) = 0.6815
Probability of Long-term corporate bonds return greater than 10% = 1 - 0.6815 = 0.3185 or 31.85%
Probability of Long-term corporate bonds return less than 0% = NORMDIST(0, 0.058, 0.089, TRUE)
Probability of Long-term corporate bonds return less than 0% = 0.2573 or 25.73%
Answer - b
Using Excel NORMDIST function -
Probability of T-bills return less than 10% = NORMDIST(0.10, 0.043, 0.031, TRUE) = 0.9670
Probability of T-bills return greater than 10% = 1 - 0.9670 = 0.0330 or 3.30%
Probability of T-bills return less than 0% = NORMDIST(0, 0.043, 0.031, TRUE)
Probability of T-bills return less than 0% = 0.0827 or 8.27%
Answer - c
Using Excel NORMDIST function -
Probability of Long-term corporate bonds return less than -4.7% = NORMDIST(-0.047, 0.058, 0.089, TRUE)
Probability of Long-term corporate bonds return less than -4.7% = 0.1190 or 11.90%
Using Excel NORMDIST function -
Probability of T-bills return less than 10.62% = NORMDIST(0.1062, 0.043, 0.031, TRUE) = 0.9793
Probability of T-bills return greater than 10.62% = 1 - 0.9793 = 0.0207 or 2.07%