Suppose the returns on long-term corporate bonds and T-bills are normally distri

Question

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 6.8% and a standard deviation of 9.8%. For the same period, T-bills had an average return of 5.3% and a standard deviation of 4%. 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.) Probability of return greater than 10 percent 37.07 % Probability of return less than 0 percent % 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.) Probability of T-bill return greater than 10 percent 12.65 % Probability of T-bill return less than 0 percent 10.78 % c. In one year, the return on long-term corporate bonds was 5.5 percent. How likely is it that such a low return will recur at some point in the future? T-bills had a return of 11.82 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.) Probability of return on long-term corporate bonds less than –5.5 percent % Probability of T-bill return greater than 11.82 percent %

Explanation / Answer

The syntax for excel normdist function is:

=normdist(x,mean,standard_dev,cumulative)

Where X=the value at which you want to want to evaluate the distribution function

             Mean= mean of the distribution

             standard_dev=Standard deviation of the distribution

             Cumulative: True =Cumulative normal distribution function (CDF)

                                    False=Normal Probability density function (CDF)

a) So the first part asks what is probability of corporate bonds having a return greater than 10 % in any one year.

So the normdist function will give us what is the probability of returns less than equal to 10%. So return greater than 10 % can be written as:

P(X>10) =1- normdist(x,mean,standard_dev,cumulative)

Where x=10

             Mean=6.8%

             standard_dev=9.8%

             cumulative=True

Therefore,    P(X>10) =1- normdist(10,6.8,9.8,True)

=1- 0.627989

=0.372011=37.20%

So now the question asks what is the probability of returns being less than 0%.We just have to use the normdist function

Therefore, P(X<0)= normdist(0,6.8,9.8,True)

= 0.24388=24.39%

Conclusion: Probability of returns greater than 10% is 37.20, while probability of returns less than 0% is 24.39%.

b) Now we have to do the same exercise for T-Bills:

P(X>10) =1- normdist(x,mean,standard_dev,cumulative)

Where x=10

             Mean=5.3%

             standard_dev=4%

             cumulative=True

P(X>10) =1- normdist(10,5.3,4,TRUE)

=1-0.880003

=0.119997=12%

P(X<0) = normdist(0,5.3,4,TRUE)

   =0.092586=9.26%

Conclusion: Probability of returns greater than 10% is 12%, while probability of returns less than 0% is 9.26%.

c) We have to evaluate the probabilities of returns less than -5.5% for corporate bonds, and greate than 11.82% for T-Bills. We will use the normdist function:

Therefore, P(X< -5.5)= normdist(-5.5,6.8,9.8,true)

= 0.104721=10.47%

Therefore P(X>11.82)= 1-normdist(11.82,5.3,4,true)

      = 11.82- 0.948449= 0.051550748

=5.15%

Conclusion: Therefore, probability of returns less than to -5.5 % is 10.47%, and returns greater than 11.82% is 5.15%.

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.