IRRの履歴データを作りたい

質問

以下の投資成績Excel シートがあります。
- A列: 日付
- B列: 投資額[JPY]
　投資した場合(株を買った場合など)に負数で、決済した場合に正数で書いています。

- C列: 投資残高[JPY]
　投資してる商品の(株など)当日末残高です。

　ここで、D列にIRR(内部収益率)を書きたいと思います。D列に入れるべき関数等を教えてください。要件は以下の通りです。IRRの起算日はA1の日付です。IRRの最終日は同行A列の日付です。利率は、日複利の年利換算で算定します。

- 日々の作業は、最終行をコピー&ペーストして、B列とC列の数字を入力するだけにしたい。

表ははこんなイメージです。

2001/01/01, -10000, 10000, 0.00[%]
2001/05/01, 0, 10200, 6.21[%]
2002/01/01, -10000, 20500, 5.00[%]
2003/01/01, -10000, 31216, 4.00[%]
2004/01/01, +10000, 22000, *.**[%]

　2004/01/01に、1万円出金して残高が22000でした。このときの作業は、3行目をコピペして4行目に張り付け、B4とC4を書きこむことを想定します。その時にD4にIRRが出るような関数等を知りたいと思います。多分、Excelに詳しいだけの人には答えられないと思いますので、数学カテゴリで質問しました。

回答No.1

xexstyle

IRRは均等な時間間隔における利益率を計算するので今回のケースの場合は使えません。
この場合は日付を指定できるXIRRを使います。
XIRRの使い方は、
=XIRR(CFのセル範囲, 日付のセル範囲, 推定値<省略可>)
です。
CF（キャッシュフロー）のセル範囲には、お金の出入れ額が入ったセル範囲を指定します。
その最後のセルには残高（投資成果）を入れることになります。
今回のケースの場合、CFのセル列（B列)と、残高のセル列(C列）が異なるので、どう指定していいか私には分かりません。

推定値は、省略した場合の規定値は10％です。
これが大きく異なるとエラーになりますので、エラーの場合は利益率を自分で大まかに計算して指定しなければなりません。

XIRRの結果は年利で返ってきます。

お礼

ありがとうございます。そこまでは、自力で到達しています。
　おっしゃる通り、「CFのセル列（B列)と、残高のセル列(C列）が異なる」ので、
XIRR関数はが通常の方法では利用できません。
　その上で、XIRR関数と同様の効果を発生させることを考える質問です。私の勘では、質問にも書いたとおり、Excelの質問ですがも数学に明るい人じゃないと解決できないだろうと思っています。
　次善の策として、D列の値はその1行上の数値と大幅には乖離しないという前提を置いて推定値として利用することは許容範囲です。