[[20231007230939]]　『IRRの計算に閏年を考慮したい。』（ちょ）　ページの最後に飛ぶ

『IRRの計算に閏年を考慮したい。』（ちょ）

XIRR関数だと１年を365日で計算していますが、閏年を加味してIRRを計算する方法があれば教えてください。

＜使用 Excel：Microsoft365、使用 OS：Windows11 ＞

 ちょっと、質問の意図が不明なんですが、

 普通、利回り計算は年率です。
 年率と言っても、360日ベースと365日ベースがあります。
 でも366日ベースと言うのはありません。
 XIRR関数は、まさしく実経過日数(閏年なら年366日)と年割日数365日ベースで計算しているので
 閏年は考慮されています。

 そう言う話でなければ「閏年を加味する」とはどう言う意味なのか先ずご説明ください。
 (具体的かつ簡単な数値の例を挙げて、お願いします）

（半平太） 2023/10/08(日) 07:53:24

 同感です。加えて少しメモ。
 そのIRR計算の結果は何に使いますか？
 お客さんとのキャッシュフロー計算には使いませんよね。
 ある種の分析評価のために使うのではないですか？
 XIRR関数は1年を365日とした計算ですが、それで十分という割りきりでしょう。
（xyz） 2023/10/08(日) 08:13:39

 私のは「割り切り」じゃないです。

 それが正しいんです。

（半平太） 2023/10/08(日) 08:52:53

 1年を366日として考えたいと言うことなんですかねぇ。 
 なら、(1+XIRR)＾(366/365)-１　となるでしょうね。

 でもこれは閏年で、キッチリ1年の場合だけですよ。
 2年連続で閏年はあり得ないので、2年間の投資とかだったらそんな式になりません。

 ＞(具体的かつ簡単な数値の例を挙げて、お願いします）
 そうリクエストしましたが「現実の詳細データ」を提示して貰った方が、
 行き違いが生じなくていいような気がしてきました。

（半平太） 2023/10/08(日) 09:40:51

ありがとうございます。説明足らずですみません。

例えば2020年は閏年なので、
2020年1月1日　-100
2021年1月1日　110
の時XIRR関数だとIRRは10%を下回りますが、これを10%になるよう補正したいのです。

何に使うのかといいますと、以下はある投資案件のキャッシュフローとその発生日でしてこの投資案件のIRRが10%に到達する2023年11月1日のリターン金額を求めたいと考えております。（簡便的に件数を減らしてますが、実際のキャッシュフローは100件ほどあります）

A列：発生日　　B列：キャッシュフロー（マイナスは投資、プラスはリターン）

2015年1月1日 -1,000,000
2016年3月1日 -275,000
2020年10月30日 425,000
2021年2月15日 525,000
2023年11月1日？？？

当初、XIRRとソルバーで求めたのですが、XIRRですと経過日数を365で割っていますが、閏年は366日で計算したく、関数で実現できないものかと考えておりました。
現在の試みとしては2021年2月15日までの各キャッシュフローに、2023年11月1日までの年利10%の終価係数をかけた金額の合計を計算して、それに-1をかければ求められるのかなと考えてますが、いずれにしても閏年は366日としてカウントできないかなと。
（ちょ） 2023/10/08(日) 13:15:55

  1年間が365日と366日がある中、日数が違うのに、
  同じ期間として表せるのは年単位しかないです。

  キャッシュフローがピッタリ、カレンダーの１年毎に異動するなら、
  何か算出方法があるのではないかと思います。

  しかし、その中間にいくらでも異動がある場合、
  その期間(日数）は何年に相当するのかを算出しなければならなくなります。
  閏年の1日は、1/366年、平年の1日は1/365年と考えるんでしょうかね。

  しかし、いつからいつまでの期間に入る日を閏年換算するのかは、自明とも言えないです。
  とりあえず、閏年の年初から翌年初までの間異動した日と単純に決める事にしてクリアしても、
  IRRの計算はキャッシュフロー最終日まで、閏年か否かで分かち計算をしなければならないことになります。

  これは考えただけで厄介そうに思えます。

  ・・と言うことで、申し訳ないですが、私は退散させていただきます。

（半平太） 2023/10/08(日) 18:11:44

そうなんです、厄介なんです。笑
いえいえ、何かヒントがあればと思い投稿してみただけですので、改めて考えます。
コメントいただきありがとうございました。
（ちょ） 2023/10/08(日) 18:52:12

 要するに、キャッシュフローをコロガシて行って、
 2023年11月1日時点の残高を求めればよいのですよね。
 単なる利息計算ということでしょうか。

 ワークシート計算はたぶん質問者さんのほうが詳しいと思います。

         A              B     C        D      E              F               G
 1  2015/1/1   -1,000,000            365                             -1,000,000
 2  2016/1/1                 365     366     10%     -1,100,000      -1,100,000
 3  2016/3/1     -275,000     60     366     10%     -1,117,322      -1,392,322
 4  2017/1/1                 306     365     10%     -1,507,810      -1,507,810
 5  2020/1/1                1095     366     10%     -2,006,895      -2,006,895
 6  2020/10/30    425,000    303     366     10%     -2,171,663      -1,746,663
 7  2021/1/1                  63     365     10%     -1,775,555      -1,775,555
 8  2021/2/15     525,000     45     365     10%     -1,796,542      -1,271,542
 9  2023/11/1                989     365     10%     -1,646,220      -1,646,220

 C列  C2:=A2-A1                                  以下コピー
 D列  =IF(DAY(DATE(YEAR(A1),2,29))=29,366,365)   以下コピー
 E列  年利率
 F列  F2:  =G1*(1+E2)^(C2/D1)                    以下コピー
 G列  G1: =B1
      G2: =F2+B2                                 以下コピー

 F列の最終行が求めるものでは？

 キャッシュフロー発生日から最後までの間にある
 ・閏年の1月1日と、
 ・その翌年の1月1日の行
 を、データの最後に追加したうえで、日付列で昇順ソートします。
 こうしておいて上記の式を埋めればよいと思います。
 ミスがあるかもしれません。そちらで修正してください。

 # いずれにしても投資案件のキャッシュフローは予測に基づくものが多いでしょうから、
 # 利率計算だけ精密にしたところで効果があるのかなあと思ったりもします。

（xyz） 2023/10/08(日) 19:03:10

 逆に、閏年を反映したXIRRを求めるには、以下のようにすればできます。
 その残高計算表において
 ・最終行nのB列セル(Bnセル)にキャッシュフローを入力したうえで、
 ・最終行のG列(Gnセル)の値を0とするような年利を「ゴールシーク」で求めればよいでしょう。
     ・「数式入力セル」を Gnセル、
     ・「目標値」を0
     ・「変化させるセル」にE2を指定します
          （E2に利率を入れ、E3以下は、=$E$2としておくものとします。記載もれ。）
 トライしてみてください。

 なお、Excel365ならもっとしゃれた関数を使ってできるかもしれませんね。
 （私には環境もなく無理なので低レベル関数です）
（xyz） 2023/10/09(月) 07:11:36

 回答したので、それに対する返事を頂きたいですね。
 まったく無視ということはあり得ませんよね。
（xyz） 2023/10/10(火) 20:39:53

お返事遅くなりましてすいません。ご回答ありがとうございます。
複数のアプローチをご提示いただき大変参考になります。
実際のキャッシュフローデータが膨大な上に計算過程やロジックを関係者に説明する必要がありまして、四苦八苦しているところです。
ご教示いただいた内容を基に引き続きトライしてみます。
ありがとうございました。
（ちょ） 2023/10/11(水) 13:18:24

 了解しました。ご返事ありがとうございます。
 複数のアプローチというのがよくわかりませんが、いずれにせよ頑張ってください。

（xyz） 2023/10/11(水) 15:23:33

コメント返信：

[ 一覧(最新更新順) ]

[[20231007230939]] 『IRRの計算に閏年を考慮したい。』（ちょ） ページの最後に飛ぶ

[[20231007230939]]　『IRRの計算に閏年を考慮したい。』（ちょ）　ページの最後に飛ぶ