In many cases, the portfolio return is at least approximately normally distributed. Then Excel’s NORMDIST function can be used to calculate the probability that the portfolio return is negative. The relevant formula is =NORMDIST (0, mean,stdev,1), where mean and stdev are the expected portfolio return and the standard deviation of portfolio return, respectively.
a. Modify the model in Example 7.9 slightly, and then run Solver to find the portfolio that achieves at least a 0.12 expected return and minimizes the probability of a negative return. Do you get the same optimal portfolio as before? What is the probability that the return from this portfolio will be negative?
b. Using the model in part a, proceed as in Example 7.9 to use SolverTable and create a chart of the efficient frontier. However, this time, put the probability of a negative return on the horizontal axis.
EXAMPLE 7.9 PORTFOLIO SELECTION AT PERLMAN & BROTHERS
Perlman & Brothers, an investment company, intends to invest a given amount of money in three stocks. From past data, the means and standard deviations of annual returns have been estimated as shown in Table 7.7. The correlations among the annual returns on the stocks are listed in Table 7.8. The company wants to find a minimum-variance portfolio that yields an expected annual return of at least 0.12.
Objective To use NLP to find the portfolio of the three stocks that minimizes the risk, measured by portfolio variance, subject to achieving an expected return of at least 0.12.
WHERE DO THE NUMBERS COME FROM? Financial analysts typically estimate the required means, standard deviations, and correlations for stock returns from historical data, as discussed at the beginning of this section. However, you should be aware that there is no guarantee that these estimates, based on historical return data, are relevant for future returns. If analysts have new information about the stocks, they should incorporate this new information into their estimates.