表は今回計算するためサンプル資料です。
教科書では、IRRとは「将来の費用と収入を現在価値に直して評価する・・・・」などと記述されていると記憶しています。
経済学を勉強をしていない自分には高尚過ぎる説明に感じます。
式をよく見てみますと、利息計算の逆数だと判ります。 この説明のほうが自分には納得できます。
IRRの方程式は指数関数のため、逆数をXとして計算しています。
Newton近似式も参照してください。
Y軸がゼロの位置がIRR解答で、約７％であることが解ります。
近似曲線とその式を作成し、IRRを計算したところ6.56%でした。（誤差があるのは歴然ですが。）
計算回数１の３行分の式と空白行を纏めてオートフィルしてゆけば、画面の結果を得られます。
前回のIRR値との差が1%未満になったところを解答としますと、IRRは6.68%です。
水色のセルは関数での計算結果です。
Sub Macro2()
'
' ﾏｸﾛ記録日 : 1999/12/16 ﾕｰｻﾞｰ名 : hoshino
'
Dim T_Count, NN, TM As Integer
SheetNo = "IRR_VBA"
TM = Range("T_Max").Value ':セル名前(T_Max)のデータ(6年間)を取得しTMに代入
S_FX = 0 ':初期化
S_Dfx = 0
NN = 0
X = 1 / (1 + Range("R_Initial").Value) ':セル名前(R_Initial)のデータ(IRRの初期値)を取得し計算後Xに代入
Range("Start_P").Select
StPR = ActiveCell.Row ':「B-C」のデータを取り込むための初期セルの行位置を取得
StPC = ActiveCell.Column ':「B-C」のデータを取り込むための初期セルの列位置を取得
Do While NN < 11
NN = NN + 1
T_Count = 0
S_FX = 0
S_Dfx = 0
For TLP = 1 To TM
T_Count = T_Count + 1
FDX = Worksheets(SheetNo).Cells(StPR, StPC + T_Count).Value ':「B-C」のデータを順次取り込む
S_FX = S_FX + FDX * (X ^ T_Count) ':合計計算
S_Dfx = S_Dfx + T_Count * FDX * X ^ (T_Count - 1) ':合計計算
XT = X - S_FX / S_Dfx
If Abs((XT - X) / X) < 0.00001 Then ':前回の値との誤差計算
Exit Do
End If
X = XT ':今回の計算結果をXに置き換える
Loop
If NN = 10 Then
MsgBox "エラーです。"
End If
Range("Answer").Value = (1 - X) / X ':計算結果をセル名前(Answer)に出力する。
End Sub
上記のニュートン法による解法は微分方程式を求めなければなりません。微分方程式を求めるのは不可能、困難、或いは面倒くさい場合に便利なのが差分法です。 以下紹介します。
微分は以下の式で求められます。
| dYi/dXi | = | lim dx→0 |
( | F(x+dx)-F(x) | ) |
| dx |
インターネットで検索をしますとdx=0.000001が妥当のようです。
この差分法でマクロを書き換えますと以下の通りです。
---------------- マクロ ----------------
Function UDF_IR2(myRng As Range, R_Init As Single, dX As Double) As Double
'
' IRR without using a Differential Equation
'
Dim T_Count, NN, TM As Integer
TM = myRng.Columns.Count
R_Init = Range("R_Initial")
If R_Init = 0 Then R_Init = 0.2
If dX = 0 Then dX = 0.000001
x = 1 / (1 + R_Init)
NN = 0
Do While NN < 11
NN = NN + 1
T_Count = 0
S_FX = 0
S_Dfx = 0
For TLP = 1 To TM
T_Count = T_Count + 1
FDX = myRng(1, T_Count)
S_FX = S_FX + FDX * (x ^ T_Count)
S_Dfx_n = FDX * (x + dX) ^ T_Count _
- FDX * x ^ T_Count
S_Dfx = S_Dfx + S_Dfx_n / dX
Next
XT = x - S_FX / S_Dfx
If Abs((XT - x) / x) < 0.00001 Then
Exit Do
End If
x = XT
Loop
If NN = 30 Then
MsgBox "Error"
End If
UDF_IR2 = (1 - x) / x
End Function
---------------- マクロ ----------------
サンプルファイルのダウンロード : IRR計算