IRR関数の変動について

質問

IRR関数の変動について

IRR関数を使って10年間投資した場合のIRRの変動を見ようと思っております
下記コードを使って作成しても、10年年後のIRRの結果が10個作成されるだけです

Dim Values(i) as Doubleとしても、定数式を要求されてしまいます
rate = IRR(Values(i))としても型が一致しませんと出てしまいます
また、Values(3)くらいで止めたところ「インデックスが有効範囲にありません」
と出てしまいます。恐らくIRRの値がマイナスになっているからだと思います

これらを回避しつつ、10年分のIRRの変動をさせるやり方を教えて下さい
お願いします

Dim i As Integer
Dim rate As Double
Dim Values(10) As Double
Dim L As Integer
Dim equity As Currency

equity = Range("A8").Value - Range("H10").Value) * -1

Values(0) = equity
Values(1) = Range("C13").Value
Values(2) = Range("D13").Value
Values(3) = Range("E13").Value
Values(4) = Range("F13").Value
Values(5) = Range("G13").Value
Values(6) = Range("H13").Value
Values(7) = Range("I13").Value
Values(8) = Range("J13").Value
Values(9) = Range("K13").Value
Values(10) = Range("L13").Value

For i = 1 To 10
rate = IRR(Values())
L = i + 9
Cells(L, 12).Value = rate
Next i

End Sub

回答No.1

end-u · ベストアンサー

Dim rate As Double
Dim i　　As Long
ReDim Values(10) As Double

Values(0) = (Range("A8").Value - Range("H10").Value) * -1
For i = 1 To 10
　　Values(i) = Range("B13").Offset(, i).Value
Next
For i = 10 To 1 Step -1
　　rate = 0
　　On Error Resume Next
　　rate = IRR(Values())
　　On Error GoTo 0
　　If rate = 0 Then
　　　　Cells(i + 9, 13).Value = "error"
　　Else
　　　　Cells(i + 9, 13).Value = rate
　　End If
　　ReDim Preserve Values(i - 1)
Next
こんな感じで、最初に配列をセットしてしまって
縮めながら計算していくと良いかもしれません。

また、Excelワークシート関数をApplicationレベルで使うと
エラー処理を気にせず、まとめる事もできます。
Dim i As Long
Dim rate 'As Double
Dim Values

Values = Range("B13:L13").Value
Values(1, 1) = Range("H10").Value - Range("A8").Value
For i = UBound(Values, 2) To 2 Step -1
　　rate = Application.IRR(Values)
　　Cells(i + 8, 13).Value = rate
　　ReDim Preserve Values(1 To 1, 1 To i - 1)
Next

equityをセルにセットして連続範囲にまとめればもっと簡単です。
例えばB13セルが使えるなら
B13=H10-A8
With Range("M10:M19")
　　.Formula = "=IRR(OFFSET($B$13,0,0,1,ROW(A2)))"
　　.Value = .Value
End With

B13セルを使えなくてもどこかのセルを作業用として
=H10-A8
=C13
=D13
=E13
：
とし、その範囲を参照させれば良いです。

お礼

end-uさん
またまた回答ありがとうございました
VBAのコードのIRRより
Application.IRRの方が、対象年数が変化したときの使い勝手がいいので
こちらを使いました

ありがとうございます