Loading [MathJax]/jax/output/HTML-CSS/jax.js

シンギュラリティ実験ノート

購入した1000ドルPCで何がどこまでできるのか実験した記録です。

LOGEST関数によるカーブフィッティング

データを指数関数で近似し、成長率(年利)を計算しようとすると、通常はRやPythonといったスクリプト言語を使用することになる。しかしエクセル等の表計算ソフトにあるLOGEST関数を使うと簡単に成長率(年利)や決定係数を計算でき、それなりにグラフを描くこともできる。今回はその方法を紹介したい。

LOGEST関数はGoogleスプレッドシートや、LibreOfficeのCalcでもサポートされている。LibreOfficeLinuxでしか動かないと思っていたが、最近は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=(m1)×100

分析データをシートに貼り付ける

分析データをシート名「Data」に貼り付ける。今回使用したのはAppleの株価データである。日付はYYYY-MM-DDの形式になっていると仮定する。

Dataシート

分析データを加工する

日付とデータを別シートに読み出して加工する。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列と指定してグラフを描く。

LOGEST結果

補足

以前に私がアップしたAppleの株価のグラフでは決定係数が0.917になっているが、LOGESTでは0.931となる。私のプログラムのバグかと思ったが、これはLOGESTの仕様によるもののようだ。LOGESTではyの対数をとって直線に直したときの決定係数を求めているようだ。下記のページに解説があった。

rikunora.hatenablog.com

私の計算ではyそのままで決定係数を求めている。LOGESTの決定係数は少し甘めの判定になるようだ。

さいごに

ちょっと試しに計算してみるのなら、エクセルで十分できると思う。是非試してみていただきたい。私は頑張ってPythonでプログラムを作ったが、Rスクリプトの方が簡単にできるのかもしれない。上記のページにもサンプルが載っているが、これは「非線形回帰」というすごく難解な方法のサンプルだ。単純な指数近似のサンプルを見つけたら紹介したい。