バルサラの破産確率表をexcelで作ってみる(ダウンロード可能)　～　excel

2018年7月18日2024年3月26日

バルサラの破産確率はナウザー・バルサラ(Nauzer J. Balsara)という数学者が考案したもので、Money Management Strategies for Futures Traders の著書があります。

破産確立

• リスクにさらす資産の割合
• 勝率
• 損益比率(ペイオフレシオ)=平均利益/平均損失

3つの要素があり、リスクにさらす資産の割合を固定したとき、トレードを続けたら勝率と損益比率によって破産する確率を表したものですが、破産確立は次の式で表せます。

excelのゴールシーク

excelで破産確立表を作るときにgoalseekを使うため、二次方程式の解を求めるのに使用してみます。

goalseekは式から逆算して答えを出すexcelの機能です。

まず、what-if分析が入っていない場合は入れてください。

図のようにB2,B3,B4に数字をいれ、B2のセルに「0」、B6のセルに「=B2*B5^2+B3*B5+B4」を入れます。

次にメニューから「データ」->「what-if分析」->「ゴールシーク」を選びます。

数式入力セルには「$B$6」、目標値「0」、変化させるセル「$B$5」としてOKを押します。

するとXの一つの答えはB5に入ります。答えは1なのですが、「0.999986727733363」の近似値となりました。

より近い値にするため、「ファイル」->「オプション」->「数式」の変化の最大値を「0.001」から「0.0000000001」にしてみます。

Xの答えは「1」になりました。

二次関数のグラフから分かるのですが、f(x)の最小値は-15なのでもう一つの解はマイナスと分かります。そこで、B2のセルに「-8」とか入れてみます。Xの答えは「-5」となりました。

とても便利です。

ゴールシークをvbで使う

前回メニューから行ったものと同じことをvbでも計算することがてきます。

開発->Visual Basicより、標準モジュールにmodule1を作り次の式を入れます。

次のようにも書くことができます。

このように使えることが分かったところで表を作るための準備をします。

バルサラの破産確率表の作成

計算用calcシート、表示用のtableシートを用意しておきます。

calcシート

calcシートには勝率の刻みと損益比の刻みと一つの勝率で設定できる損益比の個数を設定できるようにしています。
資金、リスク掛金をいれてリスク割合も設定します。

リスク割合 B8には「=B7/B6」を入れていますが目安です。

D列以降は2行目に関数を入れて下にコピーします。今回は301行目までコピーしています。刻みや個数で増やしてください。

tableシート

calcシートを利用しtableシートを作成します。

G1に確率刻み、K1に損益率刻みをいれるようにしています。

calcシートからはsumproduct関数で取得します。複数条件にあったものを求める方法で有(True)が「1」、無(False)が「0」の積を利用した方法になります。

条件1×条件2×値　/ 有(1)×無(0)×値 = 0　/ 有(1)×有(1)×値　= 値

4行目に数式をいれて下にコピーします。

モジュール

Range.GoalSeek メソッドを使い自作関数を作りますが、
開発->Visual Basicより、標準モジュールにmodule1を作り次の式を入れます。

Sub calcseek()
  Dim i As Integer
  Dim lastRow As Integer
  With ThisWorkbook.Sheets("Calc")

    lastRow = Worksheets("Calc").Range("D1").End(xlDown).Row

   '初期値を0に設定
    For i = 2 To lastRow
      Range("M" & i) = 0
    Next

    'ゴールシーク計算
    For i = 2 To lastRow
      .Range("L" & i).GoalSeek Goal:=0, ChangingCell:=.Range("M" & i)
    Next
  End With
End Sub

実行

マクロの実行をし、計算させます。時間かかかるので刻みを変えたいときだけおなうとよいです。

ただし、その前に近い値にするため、「ファイル」->「オプション」->「数式」の変化の最大値を「0.001」から「0.0000000001」にしておきます。

書式設定をしたほうが見やすくなります。

次のよう表示されました。

損益率が高ければ勝率が低くても破産しないことが分かります。ただし、リスク割合を低くした場合ですので、リスクを冒すと破産する確率が高くなるのも分かります。

入力するのが大変だと思いますので、ダウンロードできるようにしておきます。

マクロが含まれていますので各自の責任でお願いします。

バルサラの破産確率表(eightban).xlsm

1 ファイル 69.69 KB

ダウンロード