Principles of Finance with Excel (Основы финансов c Excel)
Подождите немного. Документ загружается.
PFE, Chapter 11: Downloading data from Yahoo page 58
Step 6: In the author’s browser Yahoo offers to save a file called Table.csv. We
changed the name of this file to
Merck.csv and saved it on our hard disk.
Step 7: The author’s browser offered to open the file immediately (it will open as an
Excel file):
PFE, Chapter 11: Downloading data from Yahoo page 59
Here’s the way the opened Excel file looks. Note that only the adjusted stock prices are
given.
Step 7: It is advisable to use the Excel command File|Save As to save the file as a
standard Excel file:
PFE, Chapter 11: Downloading data from Yahoo page 60
PFE Chapter 12, Appendix: The efficient frontier with more than two assets Page 1
CHAPTER 12: PORTFOLIO
RETURNS—THE EFFICIENT FRONTIER
*
This version: 15 August 2003
Chapter contents
Overview......................................................................................................................................... 2
12.1. The advantage of diversification—a simple example........................................................... 4
12.2. Back to the real world—Microsoft and General Motors .................................................... 12
12.3. Graphing portfolio returns .................................................................................................. 13
12.4. The efficient frontier and the minimum variance portfolio ................................................ 22
12.5. The effect of correlation on the efficient frontier ............................................................... 27
Summary....................................................................................................................................... 31
Exercises ....................................................................................................................................... 32
Appendix 1: Deriving the formula for the minimum variance portfolio ..................................... 40
Appendix 2: Portfolios with three and more assets ..................................................................... 41
Exercises for Appendix 2.............................................................................................................. 48
*
Notice: This is a preliminary draft of a chapter of Principles of Finance with Excel by Simon Benninga
(benninga@wharton.upenn.edu
). Check with the author before distributing this draft (though you will probably get
permission). Make sure the material is updated before distributing it. All the material is copyright and the rights
belong to the author.
PFE Chapter 12, Appendix: The efficient frontier with more than two assets Page 2
Overview
How should you invest your money? What’s the best investment portfolio? How do you
maximize your return without losing money? People often ask these knotty questions, and you
may even be reading this book in order to answer them. However, to a large extent these
questions have only partial answers. In this and the next chapter we explore some of these
partial answers; you’ll see that—although no one can tell you exactly how to invest—we can
shed considerable light on some important general investment principles. We can also show you
some rules of thumb about how not to invest.
Let’s go back to the questions with which we started the previous paragraph:
• How should you invest
your money? Finance
can’t tell you in what to
invest, but it can give
you some guidelines.
The most important of
these is: You should
diversify your investment—spread it out among many assets in order to lower the risk.
Using simple examples with only two stocks, this chapter will show you how
diversification can lower investment risk.
• What is the best investment portfolio? It won’t surprise you that the finance answer to
this question tells you that there is no single best investment portfolio. It all depends on
PFE Chapter 12, Appendix: The efficient frontier with more than two assets Page 3
your willingness to trade off return for additional risk.
1
What may surprise you,
however, is that we can say a lot about how not to invest. In this chapter we develop the
notion of the efficient frontier—this is the set of all portfolios that you would consider as
investment portfolios. Inherent in the concept of the efficient frontier is that there are
many portfolios that are not good investments, and that these portfolios can be somehow
described (statistically).
• How do you maximize your return without losing money? To some extent the efficient
frontier answers this question: It shows us which portfolios are so bad that you can
improve both the return and the risk. Once we’ve gotten on the efficient frontier,
however, the risk-return tradeoff begins to operate, and higher returns mean larger risks.
2
In most of this chapter we examine the risk and return of portfolios composed of two
financial assets. By choosing a combination of the two assets, you can achieve significant
reductions in risk.
3
Much of the chapter relies on the statistics for portfolios discussed in the
previous chapter. Even our main example, which considers portfolios of General Motors (GM)
and Microsoft (MSFT) stock, is one we started in Chapter 11.
1
As you learned in Chapter 10, nearly all the interesting finance questions involve the word “risk.” Portfolio choice
is no different!
2
As the author’s father used to say: “It is better to be rich and healthy than poor and sick.” The investment
interpretation of this is that we would all like to have more return and risk less. The efficient frontier represents the
set of difficult investment choices: Once you’re on the efficient frontier, it is impossible to get more return without
taking on more risk.
3
Of course in the real world there are many investment assets. We use the two-asset case to develop the requisite
intuitions and ask you to take it on faith that the multi-asset case is similar.
PFE Chapter 12, Appendix: The efficient frontier with more than two assets Page 4
The close links in the materials of this chapter and the materials in Chapter 11 should not
blind you to their differences. Whereas Chapter 11 develops the statistical concepts necessary
for portfolio choice, this chapter looks at portfolio choice as an economic choice. In this chapter
we develop concepts that help us think more precisely about acceptable and unacceptable
portfolios. In the next chapter we carry this line of thought further.
Finance concepts in this chapter
• Mean and standard deviation of portfolio of two assets
• Portfolio risk and return
• Minimum variance portfolio
• The efficient frontier
• Mean-variance calculations for three-asset portfolios
Excel concepts and functions used
• Average( ), Varp( ), Stdevp( )
• Regression
• Sophisticated graphing
• Solver
12.1. The advantage of diversification—a simple example
In this section we give an example that illustrates the benefits of diversification. In
finance jargon, diversification means investing in several different assets as opposed to putting
PFE Chapter 12, Appendix: The efficient frontier with more than two assets Page 5
all of your money in one single asset. In our examples you will see when diversification pays off
(and when it doesn’t). The examples are much simpler than the real-world examples that follow
in the next sections, but they embody many of the intuitions of why investors invest in portfolios.
In particular, you will see how the correlation between asset returns is important in determining
the amount of risk-reduction you can get through portfolio formation.
In each of the following examples you can invest two assets. The return on each asset is
uncertain and is determined by the flip of a coin: If the coin comes up heads, the asset returns
20% and if the coin comes up tails, the asset returns -8%. In dollar terms: If you invest $100 in
one of the two assets, you’ll get back $120 if the coin comes up heads and $92 if it comes up
tails.
In terms of the sequence of coin flips, here’s what the asset returns look like:
Coin comes up heads,
Asset B returns 20%
Coin comes up heads,
Asset A returns 20%
Coin comes up tails,
Asset B returns -8%
Coin comes up heads,
Asset B returns 20%
Coin comes up tails
Asset A returns -8%
Coin comes up tails,
Asset B returns -8%
Coin flip
determines the
return on asset A
Coin flip determines
the return on asset B
Coin flip determines
the return on asset B
A
sset A returns 20%
A
sset B returns 20%
A
sset A returns 20%
A
sset B returns -8%
A
sset A returns -8%
A
sset B returns 20%
A
sset A returns -8%
A
sset B returns -8%
Case 1: Investing in a single risky asset
Suppose you decide to invest your $100 wholly in asset A. If the coin comes up heads,
you’ll earn 20% on your investment, and if it comes up tails you will lose 8%. Your $100
investment in asset A will have the following cash flow and return pattern:
PFE Chapter 12, Appendix: The efficient frontier with more than two assets Page 6
1
2
3
4
5
6
7
8
9
10
11
AB CDE FG H I J
Cash flow Return Return statistics
120 20% <-- =C4/A6-1 Average return 6.00% <-- =AVERAGE(E4,E8)
Variance 0.0196 <-- =VARP(E4,E8)
100 Standard deviation 14.00% <-- =SQRT(I5)
92 -8% <-- =C8/A6-1
CASE 1: MEAN AND STANDARD DEVIATION OF RETURN FROM A SINGLE COIN FLIP
Coin flip:
heads
Coin flip:
tails
Notice the return statistics in column I: Asset A has average return of 6% and return
standard deviation of 14%.
4
Case 2: The case of the “fair” coin: splitting your investment between the assets
In Case 1 you invested only in one asset. In Cases 2-5 you will invest in both assets A
and B.
In Case 2 we suppose that the coin that determines the returns on asset A and the coin
that determines the returns on asset B are uncorrelated. In simple terms you can think of a single
coin that is flipped twice—once to determine the return of A and the second time to determine
the return of B. If the coin flip is “fair” then the results of the first coin flip have no influence on
the results of the second coin flip.
Now here’s the question we want to answer: Should you invest all your money in A? in
B? Or should you split your investment between the two? The answer has to do with the effects
of diversification. To examine this question more closely, let’s assume that you have decided to
invest $50 in each asset. Your final outcomes are given below:
4
You’ll notice that we’ve used the Excel function Varp to compute the portfolio variance and not the function Var.
The reasons for this choice—which we make throughout the book—were given in Chapter 11. Similarly we would
PFE Chapter 12, Appendix: The efficient frontier with more than two assets Page 7
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
ABCD E FGH I JKL
Payof
f
Return Probability
120 20% 0.25
106 6% 0.25
106 6% 0.25
92 -8% 0.25
Return statistics
Average return 6.00% <-- =SUMPRODUCT(G4:G21,H4:H21)
Variance 0.0098 <-- =VARP(G4:G21)
Standard deviation 9.90% <-- =SQRT(D26)
CASE 2: FLIPPING A FAIR COIN--TWO UNCORRELATED COIN TOSSES
$100 invested:
$50 in A
$50 in B
A comes up heads
B comes up heads
Probability: 0.5*0.5=0.25
Payoff: $50*1.2 (A) + $50*1.2 (B) = $120
A
comes up tails
B comes up tails
Probability: 0.5*0.5=0.25
Payoff: $50*0.92 (A) + $50*0.92 (B) = $92
A comes up heads
B comes up tails
Probability: 0.5*0.5=0.25
Payoff: $50*1.2 (A) + $50*0.92 (B) = $106
A comes up tails
B comes up heads
Probability: 0.5*0.5=0.25
Payoff: $50*0.92(A) + $50*1.2 (B) = $106
As you can see the average return from the investment in two assets (6 percent) is the
same as the average return in case 1, where we invested in only one asset. Note, however, that
the standard deviation went down from 14 percent to 9.9 percent—you earn the same but incur
less risk.
Message: Diversification in uncorrelated assets improves your investment returns even if
the asset returns are the same.
This message—that diversification pays off because it reduces risk—can be explored
further. In the next example we explore the returns when you have correlated assets.
Case 3: The case of the counterfeit coin: a correlation of +1
Now suppose you have the same situation as above; only this time your coin is
counterfeit. You do not know if you will get heads or tails but you do know that whatever the
use StDevp to compute the standard deviation and not StDev (although in this particular example, we’ve computed
the portfolio standard deviation by taking the square root of its variance).