分離したセルから Excel 範囲を作成する

Question

私は投資口座のスプレッドシートを持っており、毎月の入出金と月末残高を表示しています。

IRR 関数を使用して収益率を計算します。入出金の列を指定すると、最後に残高がゼロであると仮定して収益が計算されます。たとえば、次のようになります。

Deposit 100
Withdraw 10

収益率は -90% と表示されます。ただし、口座にまだ 95 ドル残っている場合、実際の収益率は +5% になります。

私のテーブルは次のようになります:

Deposits  Balance
100       100
-10       95
0         103
0         98
100       215

右側に「収益率」の列を追加し、次の式を入力します。"=IRR($A$2:A3)"

しかし、IRR 関数は最終残高がゼロであると想定し、単一の範囲のみを受け入れるため、この場合も不正確なデータが生成されます。次のような方法はありますか。

=IRR(ConcatenateRange($A$2:A3, B3))

...最終的な「引き出し」が現在の残高と等しくなるようにするにはどうすればいいですか?

Answer 1

おそらく、あなたが特定した回避策、つまり月末残高を (名目上の) 引き出しとして月末 (または期間) に表示し、前月の月末残高の入金を (名目上の) 入金として月初に表示するという方法で行き詰まっていると思います。残念ながら、もう 1 つ微妙な誤りを犯していると思います。IRR は、キャッシュ フローが偶数期間にあることを前提としています。"0" エントリが偶数期間 (週単位?) を維持するためでない限り、そのようには考えていないようです。キャッシュ フローが定期的に発生しない場合にリターンを計算するには (特に 1 年以上などの長期間にわたってこれを使用している場合)、XIRR 関数を使用する必要があります。この関数は、取引の日付とキャッシュ フローの 2 つの値の範囲を取ります。

* アップデート *

以前も似たようなことをやったことがあるとわかっていたのですが、見つけるのに時間がかかったのです！これを見てください糸Mr Excel より。要点は、キャッシュ フローと日付 (期末残高の値と日付を含む) を 2 つの配列にロードし、その 2 つの配列の XIRR を評価することです。これを行うための UDF に関する投稿は、リストの下から 3 番目くらいにあります。直前の投稿では、IRR は非連続範囲を受け入れると主張しています。私はそれをテストしたことはありませんが、試してみる価値はあります。