シミュレーションに使えるNORM.INV関数

バラツキも考慮したシミュレーションを行う際に便利な関数として、NORM.INV関数があります。これを使うと平均だけでなく、標準偏差も考慮にいれて、シミュレーションすることができます。

NORM.INV 関数 - Microsoft サポート

例えば、年7%の投資案件があったとします。その場合、当初100だったものが、10年後は約2倍の196.7になります。バラツキ（標準偏差）を考慮していないので、単純に毎年7%ずつ増加していくことになります。

しかし、実際には7%は平均であり、上振れすることや下振れすることもあります。この平均からのバラツキが標準偏差となります。例えば、標準偏差7%より14%のほうが、バラツキが大きいといえます。このバラツキによって、10年後の結果が大きく変わることがあります。

標準偏差14%と7%でのシミュレーション結果

この図は、標準偏差14%と7%の2パターンで10回シミュレーションしたものです。B列は単純に年7%で推移した様子で、10年後（B14セル、B35セル）に196.7となっていることがわかります。D列からM列までが10回シミュレーションしたものです。10年後（14行や35行）の値が196.7ではなく、それよりも上下していることがわかりますね。

標準偏差14%のパターンでは平均199.0で、最小106.5（G14セル）から最大398.4（H14セル）まで上下しています。10年後、196.7になることを期待していたが、もしかすると398.4まで増えるかもしれない。反対に106.5と、ほとんど増えない可能性もある、ということになります。

一方、標準偏差7%のパターンでは平均197.1で、最小142.6（J35セル）から最大298.5（L35セル）まで上下しています。平均値はあまり変わりませんが、標準偏差7%のほうが10年後の最小から最大までの幅が小さくなっています。

このように同じ年平均7%であっても、バラツキ（標準偏差）が異なることで結果が変わります。また、これはあくまで10回のシミュレーションの結果であり、繰り返す数を変えたり、シミュレーションをやり直すことで結果も変化します。

それでは、このようなシミュレーションを行うために、先ほどのNORM.INV関数をどのように使えばいいのでしょうか。平均7%、標準偏差14%で正規分布する値を取り出したいときは、このように数式を記載します。

=NORM.INV(RAND(),7%,14%)

試してみるとわかりますが、少ないシミュレーション回数では平均が7%から外れることが多く見られます。数を増やしていくと、平均が7%に近づくことがわかります。

この例であれば、O1セルに100が入っているとすると、O2セルに「=O1*(1+NORM.INV(RAND(),7%,14%))」とすると、1年後の結果となります。これをO11セルまで繰り返すと、10年後の結果となります。

バラツキも考慮した財務シミュレーションに使えるNORM.INV関数のご紹介でした。