ExcelのXIRR関数の使い方｜不定期キャッシュフローの内部収益率

スポンサーリンク

ExcelのXIRR関数の使い方｜不定期キャッシュフローの内部収益率

設備投資の稟議書、不動産投資の利回りシミュレーション、M&A案件のリターン評価。「年率で何%回るか」を数字で示せと言われた経験はありませんか？

そこで多くの人が最初に手を伸ばすのが IRR関数 ですが、実務の現場では「入金日と支払日が月初・月中・月末でバラバラ」「四半期に1回まとめて精算」「途中で追加投資が発生」など、IRR関数が前提とする「等間隔」のキャッシュフローはむしろ少数派。そこで活躍するのが XIRR関数 です。

XIRR関数は、日付がバラバラに並んだキャッシュフローでも、年率での内部収益率（年利回り）を一発で計算してくれる Excel の財務関数です。この記事では、XIRR関数の構文・使い方・IRR関数との違い・実務シーン別の活用パターン・エラー対処までを、稟議資料やROI試算でそのまま使えるレベルで解説します。

姉妹関数の XNPV関数（不定期キャッシュフローの正味現在価値）と組み合わせて使えば、財務関数の応用力が一段上がります。

XIRR関数とは？IRR関数との違い

XIRR関数は、不定期な日付のキャッシュフローから内部収益率（IRR、年率%）を計算する Excel の財務関数です。読み方は「エックス・アイ・アール・アール」。先頭の X は eXtended（拡張版）の X で、IRR関数を「日付指定対応」に拡張したものというイメージで OK です。

IRR関数との違い（最重要ポイント）

XIRR関数を理解するには、まず IRR関数との違いを押さえるのが近道です。

項目IRR関数XIRR関数
キャッシュフローの間隔等間隔のみ（年次・月次など）不定期OK（任意の日付）
引数values のみvalues + dates（必須）
経過時間の扱い期間番号（0,1,2…）実日数 / 365 で年率換算
戻り値期間あたりのIRR年率のIRR
月次データの扱い月利が出るので年利化が必要最初から年率で出る

ざっくり言うと、

  • IRR関数: 「等間隔の期間番号」をベースに計算する → 1月1日に入金しても12月31日に入金しても、同じ「1期目」として扱う
  • XIRR関数: 「実際の日付」をベースに計算する → 入金が早ければ早いほど IRR が高く出る（時間価値が反映される）

実務では「四半期決算で精算」「月次の家賃収入」「不定期な追加出資」など、日付が均等でないケースが大半です。そのため、現実の投資判断は IRR ではなく XIRR で計算するのが基本と覚えておきましょう。

対応バージョン

XIRR関数は Excel 2007 以降のすべてのデスクトップ版で標準搭載されています。Excel 2003 以前は分析ツール（アドイン）の追加が必要でしたが、現行バージョンを使っていれば追加設定なしで使えます。

XIRR関数の構文と引数

XIRR関数の構文はシンプルです。

=XIRR(values, dates, [guess])

引数の詳細

引数必須/省略可内容
values必須キャッシュフロー（現金収支）の系列。少なくとも1つの正の値（収入）と1つの負の値（支出）が必要。最初の値は通常マイナス（投資額）
dates必須values に対応する日付の系列。最初の日付が起点となり、以降の日付はそれより後でなければならない
guess省略可XIRR の答えに近いと推測される値（小数）。省略時は 0.1（10%）。#NUM! エラーが出るときに調整に使う

ポイントは3つあります。

  1. values と dates の対応: 配列の順序が同じインデックスで対応している必要があります（1番目同士、2番目同士…）
  2. 符号のルール: 投資（出ていくお金）はマイナス、回収（入ってくるお金）はプラスで入力します
  3. 起点日: dates の最初の値が「投資の起点日」として扱われ、すべての日数はこの日からの経過日数で計算されます

計算式の中身（参考）

XIRR関数は内部で以下の式を満たす rate（年率）を反復計算で求めています。

0 = Σ Pi / (1 + rate)^((di - d1) / 365)
  • Pi: i番目のキャッシュフロー
  • di: i番目の日付
  • d1: 起点日付（最初の日付）
  • 1年は 365日固定（うるう年も含む）

要するに「現在価値の合計がゼロになる年率」を逆算しています。(di - d1) / 365 の部分が「実日数 / 1年」で経過年数を計算している箇所で、IRR関数との最大の違いはここです。

計算式の詳細は Microsoft 公式ドキュメント にも掲載されています。

XIRR関数の基本的な使い方

まずはシンプルな例で XIRR関数の動きを確認しましょう。

シンプルな投資シナリオで試す

ある事業に 1,000万円 を投資して、3年間で以下のように回収するケースを想定します。

日付キャッシュフロー
2026-01-15-10,000,000
2026-08-302,500,000
2027-03-153,000,000
2027-12-203,500,000
2028-09-104,000,000

セル D2 に以下の式を入力します。

=XIRR(B2:B6, A2:A6)

セルの表示形式を「パーセンテージ（小数点以下1〜2桁）」に変えると、年率15%前後の結果が表示されます（具体的な数値は日付の組み合わせで微妙に変わります）。

これが「投資額1,000万円が、合計1,300万円に回収されるまでの2年8カ月の期間で、年率15%前後の利回りだった」という意味です。同じ条件を IRR関数で計算しようとすると「日付がバラバラ」なので正確には出せません。XIRR関数だからこそ、実日数ベースで正しく年利回りが計算できているわけです。

ポイント: 結果は 年率 で出ます。月利や期間あたりの利回りではないので、そのまま「年利○%」として稟議資料に書けます。

XIRR関数の実務活用パターン5選

ここからは実務で頻出する5つのパターンを紹介します。テンプレとしてそのまま使えるレベルで作ったので、自社のケースに合わせて数字を差し替えてください。

パターン1: 設備投資の利回り評価

製造業や卸売業でよくある「設備を導入して粗利を増やす」ケースです。

日付内容キャッシュフロー
2026-04-01設備購入-8,000,000
2026-09-30上期粗利増加分800,000
2027-03-31下期粗利増加分900,000
2027-09-30上期粗利増加分950,000
2028-03-31下期粗利増加分1,000,000
2028-09-30上期粗利増加分1,100,000
2029-03-31下期粗利増加分 + 残存価値4,500,000
=XIRR(C2:C8, A2:A8)

結果が例えば年利10%前後と出れば、「年利10%で回る投資」と評価できます。設備投資のハードルレートが8〜12%なら、ぎりぎり判定ラインなので、リスク要因の精査が次のステップになります。

パターン2: ハードルレートとIF関数で投資判断を自動化

XIRR の結果を社内ハードルレート（投資の最低基準利回り）と比較して、自動で「採用」「却下」を表示させる実務テクニックです。

=IF(XIRR(C2:C8, A2:A8) >= 0.10, "採用", "却下")

これで「XIRR が10%以上なら採用、未満なら却下」と1セルで判定できます。複数案件を比較するシートでは IF関数 と組み合わせるのが定番。さらにハードルレートをセル参照にしておくと、感度分析（レートを6%〜15%で動かしたときの判定変化）も簡単にできます。

=IF(XIRR(C2:C8, A2:A8) >= $F$1, "採用", "却下")

セル F1 にハードルレート（例: 0.10）を入れておけば、F1 の値を変えるだけで判定が連動します。

パターン3: 不動産投資の年利回り計算

ワンルームマンション投資や1棟アパート投資のシミュレーションです。

日付内容キャッシュフロー
2026-01-10頭金+諸経費-5,000,000
2026-02-28月次手取り（家賃-管理費-ローン）25,000
2026-03-31同上25,000
…（毎月続く）………
2030-06-30大規模修繕一時金-300,000
2031-12-31売却益（売却額-残債-諸費用）6,500,000

毎月の手取りが小さくても、最終的な売却益でリターンが大きく変わるのが不動産投資の特徴。XIRR関数なら、月次・不定期支出・売却益すべてを実日数ベースで一括処理できます。

注意: 不動産の利回り表示には「表面利回り」「実質利回り」「IRR」など種類があります。XIRRで計算するのは キャッシュフロー全体に対する真の年利回り（IRR） で、最も実態に近い指標です。

パターン4: M&A・株式投資のリターン評価

事業会社のM&Aや、長期保有の株式投資・暗号資産投資の振り返り評価にも使えます。

日付内容キャッシュフロー
2023-06-15初回投資-30,000,000
2024-03-20配当金1,200,000
2024-09-15追加投資-10,000,000
2025-03-20配当金1,500,000
2025-09-15配当金1,500,000
2026-04-30売却50,000,000

途中で追加投資（マイナス）が入っていても、XIRR関数は問題なく処理してくれます。ホールド期間が3年弱でも、追加投資のタイミング込みの真の年利回りが出ます。

パターン5: SaaS・サブスク事業のIRR試算

SaaSやサブスクリプション事業の収益試算にも有効です。

  • マイナス: 開発費、初期マーケティング費用、サーバー費
  • プラス: 月次MRR（Monthly Recurring Revenue）の積み上げ
  • 不定期: 大型ユーザー獲得、追加プロダクト投資

「3年後の累計回収額」だけ見ると赤字に見えても、XIRRで時間価値を反映すると「年率18%で回る成長事業」と評価できることはよくあります。投資家への説明資料でも XIRR は説得力のある指標です。

ハードルレートと利回り判定の考え方

XIRR の結果（年利○%）が出ても、「それで投資すべきかどうか」を判断するには ハードルレート との比較が必要です。ハードルレートとは「最低限これ以上の利回りでないと投資しない」という社内基準で、資本コスト（WACC）にリスクプレミアムを上乗せして決めるのが一般的です。

業種別の参考レンジ

投資対象一般的なハードルレート目安
国内大企業の設備投資5〜8%
中小企業の設備投資8〜12%
不動産投資（住宅・実需）4〜7%
不動産投資（収益物件・アパート）7〜12%
M&A（事業会社）10〜15%
ベンチャー投資（VC）20〜30%
エンジェル投資・スタートアップ初期30%以上

※ あくまで一般的な参考値です。実際は企業の資本コスト・案件のリスク・業界慣行によって異なります。

XIRR と XNPV をセットで提示する

稟議資料での説得力を高めるには、XIRR（年率%）と XNPV（正味現在価値の絶対額・円）を両方掲載するのが鉄則です。

関数出る数字判定基準
XIRR年率%ハードルレートと比較
XNPV円（絶対額）プラスなら投資価値あり

XIRR だけだと「年利15%」のインパクトしか伝わりませんが、XNPV と組み合わせると「年利15%で、正味現在価値は+1,200万円」と、率と絶対額の両面から評価できます。経営会議では、率（XIRR）に強い人と絶対額（XNPV）に強い人の両方がいるので、両方提示するとどちらにも刺さります。

よくあるエラーと対処法

XIRR関数でつまずきやすいエラーと対処法をまとめます。

#NUM! が出るときの典型例

#NUM! エラーが出る原因は主に3つです。

  1. values にプラスとマイナスが両方含まれていない
  • 投資（マイナス）と回収（プラス）が両方ないと IRR は計算できません
  • 確認: values の中に少なくとも1つずつ正と負の値があるか
  1. 計算が収束しない
  • XIRR は内部で反復計算（最大100回）していますが、解が見つからないことがあります
  • 対処: guess引数に予想される利回り（例: 0.05、0.2）を入れて試す
  1. dates の最初より前の日付がある
  • dates の1番目が起点になるため、それより前の日付がエラーの原因になります
  • 対処: 日付を昇順に並べ直すか、起点日を確認

guess引数（推定値）の使い方

通常は省略して問題ありませんが、#NUM! が出るときの 救済策 として有効です。

=XIRR(B2:B10, A2:A10, 0.2)

これで「答えは20%付近にあるはず」というヒントを Excel に渡せます。設備投資なら 0.1（10%）、ベンチャー投資なら 0.3（30%）、損失案件なら -0.1（-10%）など、シナリオに応じて使い分けましょう。複数解が存在する可能性がある場合は、guess を変えると違う解が出ることがあるので、-0.5, 0, 0.1, 0.5 の4パターンで試して結果が安定しているか確認するのが実務テクニックです。

IFERROR関数でラップする

複数案件を比較するダッシュボードでは、#NUM! などのエラーが見栄えを悪くします。IFERROR関数 でラップして、エラー時は文字列を返すようにしておくと安全です。

=IFERROR(XIRR(B2:B10, A2:A10), "計算不能")
=IFERROR(XIRR(B2:B10, A2:A10, 0.1), "要確認")

「計算不能」と表示されているセルだけを目視確認すれば、数十件の案件チェックも一気に効率化できます。

IRR関数・XNPV関数との違い・使い分け

最後に、財務関数5種の使い分けを1枚の表で整理します。

財務関数5種の比較表

関数用途入力出力特徴
NPV等間隔CFの正味現在価値割引率, values円（絶対額）投資の価値判定（基本形）
IRR等間隔CFの内部収益率values%（期間あたり）年率変換が必要な場合あり
MIRR修正IRR（複数解を回避）values, 借入率, 再投資率%（期間あたり）IRRの実務拡張版
XNPV不定期CFの正味現在価値割引率, values, dates円（絶対額）日付指定対応のNPV
XIRR不定期CFの内部収益率values, dates%（年率）日付指定対応のIRR

使い分けの判断軸

迷ったら、以下のフローで選んでください。

  1. キャッシュフローの日付は等間隔か、不定期か？
  • 等間隔（年次・月次） → IRR / NPV
  • 不定期 → XIRR / XNPV
  1. 率（%）で評価したいか、絶対額（円）で評価したいか？
  • 率 → IRR / XIRR
  • 絶対額 → NPV / XNPV
  1. キャッシュフローの符号が複数回切り替わるか？
  • 切り替わる（中盤で追加投資など） → MIRR を併用
  • 1回だけ → IRR / XIRR で十分

実務では「キャッシュフローは不定期」「率と絶対額の両方欲しい」が大半なので、XIRR + XNPV のセット運用が最強と覚えておけば OK です。

まとめ｜XIRR関数で不定期投資の利回りを正確に把握する

最後にXIRR関数のポイントを整理します。

  • XIRR関数は不定期な日付のキャッシュフローから年率の内部収益率を計算する関数で、構文は =XIRR(values, dates, [guess])
  • IRR関数との最大の違いは 「実日数 / 365」で経過時間を年単位に換算する点。月次・四半期・不定期のCFも正確に処理できる
  • 戻り値は 年率% なのでそのまま稟議資料に「年利○%」として書ける
  • 実務では設備投資・不動産投資・M&A・SaaS・スタートアップ投資など、日付がバラバラのケースで活躍
  • ハードルレートとの比較は IF関数 で自動化、エラー対策は IFERROR関数 でラップ
  • XNPV関数（正味現在価値）とセットで使うと、率と絶対額の両面から投資判断ができる

XIRR関数を使いこなせるようになると、Excel での投資判断の精度が一気に上がります。まずは身近な設備投資や個人の積立投資などで実際に計算してみて、IRR関数との結果の違いを体感してみてください。

なお、Googleスプレッドシートでも同じ仕様で使えるので、社外の人と共有するシミュレーションファイルでは Googleスプレッドシート版のXIRR関数 を使うのもおすすめです。

スポンサーリンク
タイトルとURLをコピーしました