「投資の入金・出金がバラバラの日付で発生していて、年利が計算できない…」
株式の追加購入や配当、不動産の家賃収入のように、実際のお金の出入りは月初や年末にきれいに揃うわけではありませんよね。IRR関数は等間隔のキャッシュフローを前提としているので、こうした不規則な日付には対応できません。
スプレッドシートのXIRR関数を使えば、日付がバラバラのキャッシュフローでも年利換算の内部収益率を一発で算出できます。実際の入出金日を入力するだけなので、株式投資や不動産投資の収益率計算にぴったりです。
この記事では、XIRR関数の基本構文から実務での活用例、IRR関数との使い分けまでまとめて紹介します。
XIRR関数とは？ — スプレッドシートで不規則な日付のIRRを求める関数
XIRR関数（読み方: えっくす あい あーる あーる）は、不規則な日付で発生するキャッシュフローから内部収益率（IRR）を求める関数です。
名前は「eXtended Internal Rate of Return（拡張内部収益率）」の略で、IRR関数を日付ベースに拡張したものです。
通常のIRR関数は「キャッシュフローが等間隔（毎年・毎月）に発生する」という前提で計算します。一方XIRR関数は、実際の発生日を引数に渡せるため、入金や出金がバラバラの日付で起きるリアルな投資にも対応できます。
XIRR関数にできることをまとめると、次のとおりです。
- 不規則な日付のキャッシュフローから年利換算の収益率を算出する
- 株式の追加購入・配当受取など実際の取引日ベースで利回りを計算する
- 不動産投資の家賃収入や経費支出を月またぎで集計して年利を出す
- 複数年にまたがるプロジェクトのROIを正確に評価する
NOTE
XIRR関数はGoogleスプレッドシートとExcelの両方で使えます。結果は常に年利換算で返されるので、月次データを使っても12倍する必要はありません。
XIRR関数の書き方（構文と引数）
基本構文
=XIRR(値, 日付, [推定値])第1引数にキャッシュフローの金額、第2引数に対応する発生日を指定します。
引数の説明
| 引数 | 必須/任意 | 説明 |
|---|---|---|
| 値 | 必須 | キャッシュフローの金額。支出はマイナス、収入はプラスで入力する |
| 日付 | 必須 | 各キャッシュフローの発生日。値と同じ件数を指定する |
| 推定値 | 任意 | 内部収益率の推定値（省略時は0.1 = 10%）。計算が収束しない場合に調整する |
値と日付には必ずプラスとマイナスの両方を含めてください。すべて同じ符号だとエラーになります。
TIP
第1引数の「値」と第2引数の「日付」は同じ件数で、同じ順番に並べる必要があります。1件でもズレると正しい結果になりません。
XIRR関数の基本的な使い方
シンプルな例で動きを確認しましょう。
不規則な日付の投資収益率を求める
100万円を投資して、3回に分けてリターンを受け取ったケースです。
| セル | 日付 | 金額 |
|---|---|---|
| A2 / B2 | 2024/1/15 | -1,000,000 |
| A3 / B3 | 2024/6/30 | 300,000 |
| A4 / B4 | 2025/2/10 | 400,000 |
| A5 / B5 | 2025/12/20 | 500,000 |
A列に日付、B列に金額が入っている想定です。
=XIRR(B2:B5, A2:A5)結果は 約9.4% です。「年利9.4%相当のリターンが得られた投資」と判断できます。
ここでのポイントは2つあります。
- 初期投資（B2）はマイナス値で入力する
- 日付は実際の入出金日をそのまま使う
IRR関数のように「年単位で並べ直す」必要はありません。実務の取引履歴をそのまま使えるのが強みです。
キャッシュフローの符号ルール
XIRR関数では、お金の流れる向きを符号で表現します。
| 取引 | 符号 | 例 |
|---|---|---|
| 自分から出ていくお金（投資・購入・経費） | マイナス | -1,000,000 |
| 自分に入ってくるお金（売却益・配当・家賃） | プラス | +50,000 |
符号を間違えると結果が大きく変わります。「自分から出ていくか・入ってくるか」を基準に判断してください。
推定値を指定する
通常は推定値を省略して構いません。ただし、#NUM! エラーが出る場合は推定値を変えてみてください。
=XIRR(B2:B5, A2:A5, 0.05)推定値に0.05（5%）を指定しています。XIRR関数は内部で反復計算を行っており、推定値はその出発点になります。
XIRR関数の実践的な使い方・応用例
株式投資の年利を計算する
ある銘柄を複数回に分けて買い、最後に売却したケースです。
| 日付 | 取引 | 金額 |
|---|---|---|
| 2023/4/10 | 100株購入 | -250,000 |
| 2023/9/22 | 100株追加購入 | -280,000 |
| 2024/3/15 | 配当受取 | 8,000 |
| 2024/10/5 | 配当受取 | 9,500 |
| 2025/3/30 | 200株売却 | 620,000 |
=XIRR(B2:B6, A2:A6)結果は 約11.3% です。「この株式投資は年利11.3%相当のパフォーマンスだった」と評価できます。
配当の入金日や追加購入のタイミングがバラバラでも、実際の日付をそのまま入れるだけで正確に計算できるのがXIRR関数の便利なところです。
不動産投資のROIを算出する
中古マンションを購入して2年間運用し、売却したケースです。
| 日付 | 取引 | 金額 |
|---|---|---|
| 2023/2/1 | 物件購入＋諸費用 | -15,000,000 |
| 2023/3/1〜2025/1/1 | 月次家賃収入（24カ月分） | +75,000 × 24 |
| 2024/8/15 | 修繕費 | -350,000 |
| 2025/2/28 | 物件売却 | 14,500,000 |
実務では家賃収入の24行を1行ずつ並べて入力します。月次データであってもXIRR関数の結果は自動的に年利換算されるので、後から12倍する必要はありません。
=XIRR(B2:B27, A2:A27)このように経費・家賃・売却益が混在しても、すべての日付を含めて1つの数式で年利が出ます。
プロジェクト収益評価で使う
新規事業を立ち上げ、開発費・運用費・売上が複数年にまたがるケースもXIRRが活躍します。
| 日付 | 取引 | 金額 |
|---|---|---|
| 2024/1/10 | 初期開発費 | -8,000,000 |
| 2024/7/20 | 追加開発費 | -2,500,000 |
| 2025/1/15 | 売上入金 | 4,000,000 |
| 2025/7/15 | 売上入金 | 5,500,000 |
| 2026/1/15 | 売上入金 | 6,500,000 |
=XIRR(B2:B6, A2:A6)結果のIRRが社内のハードルレート（目標利回り）を超えていれば、プロジェクト継続の判断材料になります。
XIRR関数とIRR関数の使い分け
XIRR関数とIRR関数は、どちらも内部収益率を求める関数ですが、前提が違います。
| 比較項目 | XIRR関数 | IRR関数 |
|---|---|---|
| キャッシュフローの間隔 | 不規則（日付指定） | 等間隔（年・月など） |
| 引数 | 金額 + 日付 | 金額のみ |
| 結果の単位 | 年利（自動換算） | 入力期間の単位（月次CFなら月利） |
| 月次データの扱い | そのまま入力で年利が出る | 月利が出るので12倍が必要 |
| 典型的な使い方 | 実際の取引履歴ベースの投資評価 | 期間が揃った計画ベースの投資判断 |
使い分けのポイント: 「実際の入出金日がバラバラ」ならXIRR、「期初・期末などキャッシュフローが等間隔に整っている」ならIRRです。
実務では、計画段階ではIRR、実績評価ではXIRRを使うのが定石です。計画は「3年間で毎年いくら」のように整った前提を置きますが、実績は日付がバラバラになりますよね。
TIP
1日でも日付の差を反映したい場合は、たとえ月次や年次のデータでもXIRRを使うとより正確になります。日付情報がある時点でXIRRが第一選択肢です。
よくあるエラーと対処法
XIRR関数で「思った結果にならない」ケースをまとめました。
| 症状 | 原因 | 対処法 |
|---|---|---|
#NUM! エラーが出る | 値にプラスとマイナスが混在していない | 初期投資（マイナス）を範囲に含めているか確認する |
#NUM! エラーが出る | 計算が収束しない | 推定値を変えてみる（例: 0.05, -0.1, 0.5） |
#VALUE! エラーが出る | 日付セルが日付型になっていない | セルの書式を「日付」に設定する。文字列の日付はNG |
#NUM! エラーが出る | 値と日付の件数が一致していない | 範囲のサイズを揃える |
| 結果がIRRと一致しない | XIRRは年利、IRRは入力期間の単位 | 期間と単位の対応を確認する |
| 結果が異常に大きい/小さい | 日付の年が間違っている | 入力日付の年が合っているか確認する |
TIP
#NUM!エラーで最も多い原因は「初期投資のマイナス値を入れ忘れている」ことです。XIRR関数にはプラスとマイナスの両方が必要なので、出ていくお金は必ずマイナスで入力しましょう。
Excelとの違い
XIRR関数はExcelとGoogleスプレッドシートでほぼ同じ動作です。
| 項目 | Excel | Googleスプレッドシート |
|---|---|---|
| 構文 | =XIRR(値, 日付, [推定値]) | =XIRR(値, 日付, [推定値]) |
| 動作 | 不規則日付の内部収益率を返す | 同じ |
| 推定値の省略時 | 0.1（10%） | 0.1（10%） |
| 結果の単位 | 年利 | 年利 |
引数の数も意味も完全に同じなので、ExcelファイルとGoogleスプレッドシートでXIRR関数の数式を相互にやり取りしても問題ありません。
まとめ
XIRR関数は、不規則な日付のキャッシュフローから年利換算の内部収益率を求める関数です。
ポイントを整理します。
- 構文は
=XIRR(値, 日付, [推定値]) - 値と日付は同じ件数・同じ順番で指定する
- 支出はマイナス、収入はプラスで入力する
- 結果は自動的に年利換算されるので12倍などの調整は不要
- IRR関数との違いは「日付がバラバラでもOK」な点
- 株式投資の利回り、不動産ROI、プロジェクト収益評価で活躍する
#NUM!エラーはプラス・マイナス混在の確認か推定値の調整で解消できる
実際の取引履歴があれば、日付と金額をそのまま並べるだけで年利が出ます。まずは過去の投資データでXIRR関数を試してみてください。
