johno

EXCEL VBAによる
投資分析ツールの開発と応用
2007年
経済学部４年Ｂ０３３２０６
城野圭史
はじめに

背景
Excelは、汎用ソフトウェアであるため、ユー
ザーのニーズに合致する全ての関数や分析
ツールが用意されているわけではない。
→Excel上で動作する操作性の良い計量ファ
イナンス・分析ツールに対するニーズは多い。
→Excel VBAによるプログラム製作
はじめに

開発した分析ツール
→１ショートフォール確率・VaR分析ツール
→２株価評価ツール
→３債券評価ツール
導入
①セキュリティ
[ツール][マクロ][セキュリティ]を選択
導入
②インストール
（ⅰ）ファイル→名前を付けて保存（投資分析
ツール）
導入
（ⅱ）ツール→アドイン
導入

ツール→マクロ→マクロ
１ショートフォール確率・VaR分析ツー
ル

ショートフォール確率とは
個別証券やポートフォリオのリターンがある一定の利率を下
回る確率を示したもの

VaRとは
バリュー・アット・リスク（ＶＡＲ）とは、ある資産を保有している
とき、その保有期間中に一定の確率で発生し得る最大損失
額を統計的に推計したもの
１－１ショートフォール確率を求める手
順
ショートフォール確率の計算コード
Sub calculater15(gr As Single, er As Single, h As Single, outrange As String)
Dim sf As Single, asf As Single
sf = Application.NormDist(gr, er, h, True)
asf = sf * 100
Range(outrange).Select
ActiveCell.Range("a1") = "ショートフォール確率(%)"
ActiveCell.Offset(0, 1).Select
ActiveCell.Range("a1") = asf
If asf <= 30 Then
ActiveCell.Interior.ColorIndex = 5
Else
ActiveCell.Interior.ColorIndex = 3
End If
End Sub
１－２ VaRを求める手順
VaRの計算コード
Sub calculater16(gr As Single, h As Single, k As Single, outrange As String)
Dim Var As Single
Var = Application.NormInv(1 - k / 100, gr, h)
Range(outrange).Select
ActiveCell.Range("a1") = "VaR(%)"
ActiveCell.Offset(0, 1).Select
ActiveCell.Range("a1") = Var
If Var <= 0 Then
ActiveCell.Interior.ColorIndex = 5
Else
ActiveCell.Interior.ColorIndex = 3
End If
End Sub
２株価評価ツール
定率成長モデル
P=D/k-g
 内部成長率
g=ROE×（１－配当性向）

２－１成長率を求める
「成長率を求める」の計算コード
Sub calculater3(roe As Single, h As Single, outrange As String)
Dim g As Single
g = roe * (1 - h)
Range(outrange).Select
ActiveCell.Range("a1") = "成長率(%)"
ActiveCell.Offset(0, 1).Select
ActiveCell.Range("a1") = g
End Sub
２－２株価を求める
「株価を求める」の計算コード
Sub calculater4(d As Single, k As Single, g As Single, outrange As String)
Dim p As Single
p = d / (k - g)
Range(outrange).Select
ActiveCell.Range("a1") = "予想株価"
ActiveCell.Offset(0, 1).Select
ActiveCell.Range("a1") = p
End Sub
３債券評価
受取額
→元本x円の金融商品を金利y％でz年間運用
したときの受け取れる額
 利率
→元本x円をz年後にa円にして受け取るための
利率
 元本
→利率y％でz年後にa円受け取るための元本

計算式
受取額
単利：
複利：
÷a)}
 利率
単利：
複利：
 元本
単利：
複利：

受取額＝元本×｛１＋（利率×運用年数）｝
受取額＝元本×｛１＋（利率÷a）＾（運用年数
利率＝｛（受取額÷元本）－１｝÷運用年数
利率＝｛（受取額÷元本）＾（1÷運用年数）｝－１
元本＝受取額÷｛１＋（利率×運用年数）｝
元本＝受取額÷｛（１＋利率）＾運用年数｝
３－１債券評価の手順（単利で受取額
を計算する場合）
受取額（単利）の計算コード
Sub calculater1(g As Single, r As Single, t As Single, outrange As String)
Dim p As Single
p = g * (1 + (r * t))
Range(outrange).Select
ActiveCell.Range("a1") = "受取額(単利)"
ActiveCell.Range("a1").Interior.ColorIndex = 3
ActiveCell.Offset(0, 1).Select
ActiveCell.Range("a1") = p
ActiveCell.Range("a1").Interior.ColorIndex = 3
ActiveCell.Offset(1, -1).Select
ActiveCell.Range("a1") = "元本"
ActiveCell.Offset(0, 1).Select
ActiveCell.Range("a1") = g
ActiveCell.Offset(1, -1).Select
ActiveCell.Range("a1") = "利率"
ActiveCell.Offset(0, 1).Select
ActiveCell.Range("a1") = r
ActiveCell.Offset(1, -1).Select
ActiveCell.Range("a1") = "運用期間"
ActiveCell.Offset(0, 1).Select
ActiveCell.Range("a1") = t
End Sub
３－２債券評価の手順（複利で利率を
計算する場合）
利率（複利）の計算コード
Sub calculater6(p As Single, g As Single, t As Single, outrange
As String)
Dim r As Single
r = (((p / g) ^ (1 / t)) - 1) * 100
Range(outrange).Select
ActiveCell.Range("a1") = "利率(%)"
ActiveCell.Range("a1").Interior.ColorIndex = 3
ActiveCell.Offset(0, 1).Select
ActiveCell.Range("a1") = r
ActiveCell.Range("a1").Interior.ColorIndex = 3
ActiveCell.Offset(1, -1).Select
ActiveCell.Range("a1") = "受取額"
ActiveCell.Offset(0, 1).Select
ActiveCell.Range("a1") = p
ActiveCell.Offset(1, -1).Select
ActiveCell.Range("a1") = "元本"
ActiveCell.Offset(0, 1).Select
ActiveCell.Range("a1") = g
ActiveCell.Offset(1, -1).Select
ActiveCell.Range("a1") = "運用期間"
ActiveCell.Offset(0, 1).Select
ActiveCell.Range("a1") = t
おわりに
３つの分析ツールを開発
→ショートフォール・VaR分析ツール
→株価評価ツール
→債券評価ツール

→→ニーズに合致するものを作ることが可能

Download Report