データを指数関数で近似し、成長率(年利)を計算しようとすると、通常はRやPythonといったスクリプト言語を使用することになる。しかしエクセル等の表計算ソフトにあるLOGEST関数を使うと簡単に成長率(年利)や決定係数を計算でき、それなりにグラフを描くこともできる。今回はその方法を紹介したい。
LOGEST関数はGoogleスプレッドシートや、LibreOfficeのCalcでもサポートされている。LibreOfficeはLinuxでしか動かないと思っていたが、最近はWindows版もあるようだ。私はエクセルを持っていないため、説明は便宜上Googleスプレッドシートを使わせてもらう。
複利の計算式
現在価値をb、将来価値をy、複利の利率(%)をR、運用期間(年)をxとし、更に変数mを使うと、複利計算の式は以下のように表現できる。
m=R÷100+1
y=b×mx
LOGEST関数とは
実データxとyに上記の指数関数の関係があると仮定し、誤差が最小となるようなbとmを求めてくれるのがLOGEST関数である。株価の場合は株価データをyとして、運用年数データをxとして下記のように指定するだけである。
=LOGEST(yの範囲, xの範囲, true, true)
mが求まれば利率は下記の式で簡単に計算できる。
R=(m−1)×100
分析データをシートに貼り付ける
分析データをシート名「Data」に貼り付ける。今回使用したのはAppleの株価データである。日付はYYYY-MM-DDの形式になっていると仮定する。
分析データを加工する
日付とデータを別シートに読み出して加工する。A2、B2、C2、D2のセルに下記の式を入力する。ここでは株価の終値を分析すると仮定する。
A2 =Data!A2
B2 =Data!E2
C2 =DAYS(A2,A$2)/365
D2 =year(A2)+days(A2,year(A2)&"-01-01")/365
A2、B2、C2、D2に上記の式を入力したら、セルをコピーし、3行目以降にデータ行数分貼り付ける。
LOGEST関数を入力する
E2のセルにLOGEST関数を入力すると、E2:F6に分析結果が表示される。赤枠で囲ったセルが求まったmとb。青枠で囲ったセルが決定係数である。
E2 =LOGEST(B2:B121,C2:C121,true,true)
G列で指数関数近似した結果を計算する。
G2 =F$2*POWER(E$2,C2)
G2のセルをコピーし3行目以降にデータ行数分貼り付ける。
X軸のデータ系列をD列、Y軸のデータ系列をG列と指定してグラフを描く。
補足
以前に私がアップしたAppleの株価のグラフでは決定係数が0.917になっているが、LOGESTでは0.931となる。私のプログラムのバグかと思ったが、これはLOGESTの仕様によるもののようだ。LOGESTではyの対数をとって直線に直したときの決定係数を求めているようだ。下記のページに解説があった。
私の計算ではyそのままで決定係数を求めている。LOGESTの決定係数は少し甘めの判定になるようだ。
さいごに
ちょっと試しに計算してみるのなら、エクセルで十分できると思う。是非試してみていただきたい。私は頑張ってPythonでプログラムを作ったが、Rスクリプトの方が簡単にできるのかもしれない。上記のページにもサンプルが載っているが、これは「非線形回帰」というすごく難解な方法のサンプルだ。単純な指数近似のサンプルを見つけたら紹介したい。