1. ホーム
  2. 金融工学を初等数学で
  3. エクセルでポートフォリオ分析

エクセルで株式ポートフォリオ分析（接点ポートフォリオ、資本市場線、分離定理など）

注記
 以下はＥｘｃｅｌ２０１３を使用して書いた文である。最新のバージョンでは行列演算が動的配置のため若干操作が異なるかもしれないが同一の結果が得られる。


３つ以上の資産から成るポートフォリオの収益率、分散等を計算する場合には簡単な行列演算が必要となるので、エクセルによる計算例をまとめてみた。行列と聞いただけで毛嫌いする人のいるかもしれないが、単に数字の塊を操作するに過ぎないと思えば案外と気楽に付き合えると思う。エクセルでは MMULT,MINVERSE,TRANSPOEといった３つの関数を使うことで大概の計算は済ますことができ、計算に使うコマンドのパターンも限られているので、一旦コマンドパターンを作っておけば、あとは引数のセル範囲を適宜に変更するだけで自分が必要とする計算は完了する。簡単な行列演算を使って、最小分散ポートフォリオ、接点ポートフォリオ、資本市場線（CML),ポートフォリオ分離定理などについて計算実験してみる。

 エクセルの行列演算では配列数式が作られるが、その計算結果を編集しようとすると、「配列の一部を変更することはできません」といった警告メッセージが頻繁に表示され、うるさくて頭が沸騰しやすくなる。これは健康上よろしくないので、予めwindowsアクセサリのメモ帳のようなテキストエディターを起動しておき、数式を編集するときには、数式バーに出ている数式をテキストエディターに一旦貼りつけておき、次に、エクセルに出力されている配列数式の全てのセルを削除してセルを更地にしてしまい、エクセルの余白セルにテキストエディターの数式を文字列として貼り付けて、自分の好みのように編集をする。これは自己勝手流なので良い方法かどうかは分からないが、自分の経験上は心穏やかに作業が出来るようになったと思う。具体的な手順については連立方程式の解法のところで少し触れてみたい。(When I'm trying to edit an array formula, I often see you cannot change part of an array. To avoid this, I  use a text editor like windows notepad. Before editing the cell, I  paste the cell into notepad and then editing the formula on notepad, then I delete the cells which contain the array formula  and paste the edited formula from notepad to Excel as text strings.Then
 press Ctrl+Shift+Enter in Excel)

ポートフォリオの収益率を求める(finding the portfolio's return)

株式　A、B、Cの銘柄から成るポートフォリオで、A,B,C　の投資比率が均等割合であったとすると、投資比率ベクトル　X=(0.333,0.333,0.333)で表せる。３銘柄　A,B,C　の収益率ベクトルをμで表し仮に
μ=(0.02,0.08,0.14)　とする。横書きになっているので、うまく縦に表示できないがベクトルは全て列ベクトルとする。ポートフォリオ全体の収益率は投資比率ベクトルと収益率ベクトルの内積で求められる。下記のエクセルのシートではすべて列ベクトルで表しており以下のようになる。





ポートフォリオの収益率は０．０８と計算されている。ここでの数式は　　=MMULT(TRANSPOSE(C18:C20),C9:C11)　と記入し、ワークシート画面上部に表示されている（数式バー　fx）のところに表示されている数式をクリックして<ctrl>+<shift>+<enter>の３つのキーを同時に押すと演算結果がセルに表示される。投資比率ベクトルも収益率ベクトルも３行１列の列ベクトル(3x1)で定義しているので(1x3)*(3x1)でスカラーである内積を求めるためには、収益率ベクトルをTRASPOSE(C18:C20)として行ベクトルに転置し、MMULTを使って転置された収益率ベクトルと列ベクトルである投資比率ベクトルの積を求めている。もし、当初から収益率ベクトルが行ベクトルで、投資比率ベクトルが列ベクトルで与えられていれば、TRANSPOSEは使わずに、そのままセル範囲を記入すればよい。もし、かけ算の順序を間違えて=MMULT(C9:C11,TRANSPOSE(C18:C20))　とすると３行３列の行列が計算されてしまう。収益率ベクトル(3x1)に行ベクトルに転置された投資比率ベクトル（1x3):を乗じているので(3x1)*(1x3)の３行３列の全く意図しない行列が計算される。

ポートフォリオの分散を求める。(finding the portfolio's variance)

ポートフォリオの分散は 投資比率ベクトル（行ベクトル）×分散共分散行列×投資比率ベクトル（列ベクトル）の行列演算で求められる。仮に３銘柄の分散共分散行列が下記のように与えられたとすればポートフォリオの分散は次のようになる。



ポートフォリオの分散は０．１０９１と計算されている。分散共分散行列に列ベクトルの投資比率ベクトルを乗じると(3x3)*(3x1)の計算で３行１列の列ベクトルが計算され、それに投資比率ベクトルを転置した(1x3)の行ベクトルを左側から乗じて(1x3)*(3x1)で内積であるスカラーとしての分散を計算している。

連立一次方程式の解を求める(solving systems of simultaneous linear equations)

以下のような連立一次方程式を解いてみる。
ｘ＋３ｙ＋３ｚ＝８
２ｘ－ｙ＋ｚ＝０
３ｘ＋ｙ－３ｚ＝１２

未知数はｘ、ｙ、ｚで、これを列ベクトルX、左辺の係数をAという係数行列、右辺の定数をBという列ベクトルで表すと以下のようになる。未知数のベクトルXは
係数行列Aの逆行列に定数ベクトルBを乗じて計算できるので下記のような行列演算となる。逆行列についても取りあえずは、大げさに考えずに、例えば、３の逆数は１／３　つまり３－１　で表すのと同じ感覚で、行列バージョンの逆数が逆行列と思えばよい。

連立一次方程式


数式表示　　=MMULT(MINVERSE(B63:D65),B70:B72)
と記入し、ワークシート画面上部に表示されている（数式バー　fx）のところに表示されている数式をクリックして<ctrl>+<shift>+<enter>の３つのキーを同時に押すと演算結果がセルに表示され（２，３，－１）との解を得る。つまり、ｘ＝２，ｙ＝３，ｚ＝－１となる。もし、配列数式を出力させるセル範囲を間違えた場合には、数式バーに出ている数式をテキストエディターに一旦貼りつける。エクセルに出力されている配列数式のすべてのセルを削除して更地にする。次にテキストエディターの数式をエクセルの余白のセルに文字列として貼りつける。例えば、上記の例でいえば=MMULT(MINVERSE(B63:D65),B70:B72)をメモ帳に貼り付け、それからメモ帳の文字列 　=MMULT(MINVERSE(B63:D65),B70:B72)  をエクセルの余白セルに文字列として貼り付ける。その後にセル範囲をB８0:B８2とか自分の好きな範囲に書き換えた後に、結果出力に必要なセル範囲を選択して、数式バーに出ている変更後の数式をクリックして<ctrl>+<shift>+<enter>の３つのキーを同時に押せば新たな計算結果が得られる。うまくいかない場合は配列数式が出ている全てのセルを削除して更地に戻し、上記の手順でやり直してみる。
　なお、逆行列の計算をする時には、無意味な計算を避けるために、事前に対象とする行列の行列式がゼロか非常にゼロに近い値でないことを確かめておく必要がある。下記の事例を示しておく。

逆行列の計算

　以下のような３行３列の行列Dの逆行列を求める。
　（１，２，３）
　（４，５，６）
　（７，８，９）

エクセルでは　=MINVERSE(B88:D90)を使ってけ計算すると
逆行列D-1  
-4.5036E+15 9.0072E+15 -4.5036E+15
9.0072E+15 -1.80144E+16 9.0072E+15
-4.5036E+15 9.0072E+15 -4.5036E+15
を得る。もし、これが正しければDD-1は３行３列の単位行列になる筈である。
DD-1を計算すると
0.0000 0.0000 0.0000
-4.000 0.0000 -4.0000
0.0000 0.0000 0.0000
となり、単位行列になっていない。
Dの行列式を　=MDETERM(B88:D90)　で計算してみると限りなくゼロに近い数値となっている。このような場合は逆行列は無意味となる。
Dの２列を２倍し、そこから第１列を差し引くと第３列に等しくなり３つの列が独立でないことが分かる。このような場合は逆行列は計算できないので、
-4.5036E+15 9.0072E+15 -4.5036E+15
9.0072E+15 -1.80144E+16 9.0072E+15
-4.5036E+15 9.0072E+15 -4.5036E+15
は使えない。

以上の基本的操作法を覚えてしまえばその後の作業は大変に楽になる。
まず手始めに最小分散ポートフォリオ(global minimum variance portfolio)の計算をしてみる。

最小分散ポートフォリを求める(finding the global minimum variance portfolio)

リスク資産である株式　３銘柄　A,B,C　の投資比率をX=(ｗA,ｗB  ,ｗC)
３銘柄　A,B,C　の収益率ベクトルをμ＝（0.02,0.08,0.14）　　
A,B,Cの収益率の分散共分散行列∑は以下のように与えられている。




ここでポートフォリオの分散が最小となるような投資比率を求めるには

.ｗA　＋　ｗB 　＋　ｗC　＝１　

という制約条件の下にポートフォリオの分散を最小化するという条件付極値問題となる。これはラグランジュ関数Lを作り連立方程式を解くというラグランジュ乗数法を適用する。ラグランジュ関数を各変数で偏微分すると連立方程式が導ける。This is the constrained minimization problem.To solve this,the method of lagrangian multpliers is used)




ラグランジュ乗数法
これを整理すると以下のような式になる。

ラグランジュ乗数

Zは係数行列になるが最初の３行３列は分散共分散行列を２倍した数字になっている。この連立方程式を行列演算で解くと以下のようになる。



　この連立方程式を解く過程でラグランジュ乗数λの数値も計算されるが、これは使うことはない。なお、ラグランジュ乗数法の幾何学的な説明やラグランジュ乗数λの経済学的な解釈については、小山昭雄　（１９９５）　経済数学教室　５　微分積分の基礎　上巻　岩波書店　で詳しく論じられている。色々と読み比べをしたわけではないが、数学者により３０ページほどをも費やしてラグランジュ乗数法について解説されている貴重なテキストだと思う。


最小分散ポートフォリオ
投資比率(weight of 3 stocks)
A 0.71967
B 0.16318
C 0.11715
合計 1

次に最小分散ポートフォリオの収益率と分散を求めてみる。この計算は前半で述べたような行列演算で下記のような結果を得られる。




接点ポートフォリオ（Tangent portfolio)を求めてみる(finding the tangent portfolio)

上記の最小分散ポートフォリオの計算例に無リスク資産（安全資産）を追加したポートフォリオを検討してみる。収益率や分散共分散行列は最小分散ポートフォリオの数値例と変わらないものとし、新たに無リスク資産収益率ｒが加わってくる。A、B、,Cの３つの危険資産とRという無リスク資産から構成されるポートフォリオの効率的フロンティアを求めることになる。無リスク資産は収益率ｒで定数なので、それ自体の分散はゼロとなる。また株式A,B,Cとも相関関係が無いので各株式と無リスク資産の収益率の共分散もゼロとなる。従って収益率の分散共分散行列は以下のようになる。



無リスク資産収益率をｒ、無リスク資産を含む全体のポートフォリオの収益率をμP 無リスク資産の投資比率をWRとすれば以下の制約条件の下でポートフォリオ全体の分散を最小化さすという条件付極値問題となる。これもラグランジュ関数を各変数で偏微分すると連立方程式が導ける。

制約条件

制約条件
ラグランジュ乗数

この連立方程式を解くに際して、制約条件の式で全体ポートフォリオ（リスク資産＋無リスク資産）の収益率μpを何か指定する必要がある。ここではB株の収益率０．０８を指定して計算しているが、無リスク資産の収益率以上であれば何でも適当な数値でよい。何かいい加減な話に聞こえるかもしれないが、敢えてインテリぶった言い方をすれば、トービン（Tobin)の分離定理の教えに従っているにすぎない。ｘ軸にリスク指標である標準偏差をとり、ｙ軸には収益率をとると無リスク資産収益率ｒを切片として接点ポートフォリを通る１次直線つまり資本市場線が引けるが、この直線は通常、右肩上がりで無限に伸びていく。つまり、どんな収益率でも計算可能ということになる。

最小分散ポートフォリオの計算と同様に連立方程式の係数行列を作る。ここまでくれば、あとは連立一次方程式を解くだけである。




行列演算で解くと以下のようになる。



Above is the efficient portfolio of 3 stocks and a risk-free asset).

上記の解はリスク資産と無リスク資産の投資割合の合計が１となるような解なので、リスク資産だけで構成される接点ポートフォリオの投資割合は下記のようにして計算し、次に 接点ポートフォリオの収益率と分散を以下のように計算する。
 なお、空売りは制限されていない前提でのポートフォリオなので投資割合がマイナスの時は空売りを意味している。

calculating the weights of 3 stocks of the tangent portfolio



上記のようにシャープレシオ(Sharpe ratio)は０．２３０９２８となりこれが資本市場線（CML)の傾き(slope)となる。
無リスク資産を含む全体のポートフォリオの収益率と分散は以下のように計算できる。




無リスク資産を含む全体のポートフォリオの 分散の計算に際しては前述したように無リスク資産収益率ｒは定数なので、それ自体の分散はゼロとなり、リスク資産とも相関関係が無いので共分散もゼロとなる。従ってポートフォリオ（リスク資産＋無リスク資産）の分散は簡単な式になる。

ポートフォリオの分散

リスク資産と無リスク資産を含む全体のポートフォリオの収益率が 資本市場線の上にあるかどうかも確認しておく。



資本市場線の算式通りで、リスク（標準偏差）が０．３０３１２５に見合う収益率が０．０８であることが確認できる。(We can confirm the return of the  portfolio of 3 stocks and a risk-free asset is on the CML)

--------------------------------

添付されたテキストファイル3stocksdata.txtの利用法

このテキストファイルを開いて全てを選択(Ctl + a)してコピーしExcelの新しいシートの セルA１にそのまま貼りつければ計算例のワークシートが再現できる。(Excel2016以降であれば可能、それより以前のversionではセルの分割作業が必要かもしれない）

具体的な手順は以下のとおり
１　このテキストファイルを開く
２　すべてテキストを選択する(Ctl + a)
３　Excelの新しいシートのセルA１に貼りつける

 行列演算の部分は＃スピル(spill) と表示されるがもしれないが、セルC29-C34の＃スピル！と表示されている部分を選択しF2 キーを押して、次に Ctrl+Shift+Enter で確定 さすと計算結果が得られる。この文を書いた時にはExce l　2013を使っていたが最近のバージョンでは動的配列で行列演算するようで＃スピル(spill) が出てしまう。各自で工夫して適当に修正して頂きたい。行列演算以外の基本的データは再現できると思う。

 To recreate the example worksheet, open this text file, select everything (Ctrl + a), copy it, and paste it directly into cell A1 of a new Excel sheet. (This is possible in Excel 2016 and later; earlier versions may require splitting cells.)
  The specific steps are as follows:
1. Open this text file.
2. Select all text (including blank spaces).
3. Paste it into cell A1 of a new Excel sheet.   The matrix operations may display #spill, but you can get the results by selecting the #spill! section in cells C29-C34, pressing F2, and then pressing Ctrl+Shift+Enter to confirm. I was using Excel 2013 when I wrote this, but recent versions seem to use dynamic arrays for matrix operations, resulting in #spill. Please modify this as needed. I believe you can recreate basic data other than matrix operations.

ワークシートでなくテキストファイルを添付した理由などについてはエクセルのワークシートをテキスト化して簡単に通信
を御参照
--------------------------------


仮に、非常にアグレッシブな投資家がいて資金調達も無リスク資産利子率で無制限に可能であるとする。その投資家が、リスク資産と無リスク資産を含む全体のポートフォリオの収益率が１００％となるようなポートフォリオ編成を依頼してきたとする。その投資家の要請に応えるには、上記の計算例のなかの収益率μP ＝０．０８を１．０に置き換えて投資比率を求めればよい。エクセルのシートは自動計算してくれるので ベクトル　X の解　は
WA -1.455545987
WB 4.634874294
WC 5.231648149
WR -7.410976455
合計　　　　　１．０

を得る。ここで無リスク資産の投資比率がマイナスの７．４１と計算されている。これの意味するところは、利子率０．０１で７．４１０９８倍の借入を行いその資金と自己資金をあわせてリスク資産ポートフォリオに８．４１０９７投資して、最終的に投資比率合計が１となるポートフォリオを示している。非常に大きなレバレッジを使ったポートフォリオになっている。なお、リスク資産のみから成る接点ポートフォリの投資比率は下記のように収益率８％の場合と変化はない。まさに、ポートフォリオの分離定理が成り立っていることが確認できる。

リスク資産だけのポートフォリオ　の投資比率の計算
WA+WB+WC=-1.45555+4.634874+5.231648 = 8.410976455
A　投資比率 -0.173053152 =-1.45555/8.410976
B　投資比率 0.55105068 =4.634874/8.410976
C　投資比率 0.622002472 =5.231648/8.410976
　　　　合計　　　 1



接点ポートフォリオの求め方（別法）


上記の計算法の他に超過収益率から接点ポートフォリオを求めることもできる。
つまり、

制約条件

という 制約条件式を下記のような数式整理をして一本の制約条件に集約する方法である。

制約条件

この場合はポートフォリオ（リスク資産＋無リスク資産）の超過収益率（μp-r)の
制約条件下でポートフォリオの分散を最小化する条件付極値問題となる。これもラグランジュ関数を各変数で偏微分すると連立方程式が導ける。


ラグランジュ乗数法

以上を整理すると下記のような 連立一次方程式を得る。

連立一次方程式

分散共分散行列の数値は変わらないので以下のように与えられている。

分散共分散行列


係数行列は以下のようになる。


係数行列

行列演算で連立方程式を解くと下記のようになる。

行列演算

ここで得た解は他に無リスク資産がある場合のリスク資産の投資ウエートを示しており、リスク資産だけのポートフォリオにおける投資割合は下記のようにして求める。

接点ポートフォリオ計算



準分散、ソルティノレシオ、LPM、UPMの計算例 in Excel



金融工学を初等数学で　目次