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