Excel XIRR 函数:计算不定期现金流的年化内部收益率

Excel 的 XIRR 函数根据现金流的实际发生日期计算年化内部收益率。内部收益率是使现金流入和流出折算到首日后的总和等于零的折现率。如果现金流按月末等固定周期发生,可以用 Excel 的 IRR 函数计算每期收益率。

准备金额和日期区域

=XIRR(金额,日期,[估计值])

支出填负数,收入填正数。至少要有一个正数和一个负数,并为每笔金额填写对应日期。请使用 Excel 能识别的日期值。如果日期包含时刻,函数按整天计算。

将最早的日期放在第一行,作为计算基准。后续各行可以不按时间先后排列。如果需要排序,请同时移动两列,避免金额与日期错配。第三个参数是可选的初始估计值,默认为 10%。

Microsoft 的 XIRR 函数说明

用三笔现金流计算收益率

按下表在 A 列录入金额、在 B 列录入日期。如果 Excel 无法识别表中显示的日期格式,请使用本机的日期格式,或用 DATE 函数输入日期。

单元格 金额 日期单元格 日期
A2 -1000 B2 2025/01/01
A3 300 B3 2025/04/01
A4 820 B4 2026/01/01

在 C2 输入下面的公式,在“开始”选项卡中将结果设置为百分比,保留两位小数。请按照 Excel 的区域设置使用逗号或分号分隔参数。

=XIRR(A2:A4,B2:B4)

结果约为 15.42%。初始支出为 1000,净流入为 120。XIRR 还考虑到 300 在第 90 天收到,820 在第 365 天收到。

理解年化结果并用 XNPV 验算

XIRR 将实际经过的天数除以 365 来确定折现期间。例如,2025 年 1 月 1 日支出 100,2026 年 1 月 1 日收到 110,年化收益率为 10%。如果仅把收款日提前到 2025 年 7 月 1 日,收益仍是 10,但只用了 181 天收回款项,年化收益率约为 21.19%。

把 C2 的收益率传给 XNPV,可以计算以第一天为基准的净现值。对于上面的三笔现金流,结果应接近零。请直接引用 C2,以保留显示为 15.42% 的结果在舍入前的精度。

=XNPV(C2,A2:A4,B2:B4)

Microsoft 的 XNPV 函数说明

排查错误和异常结果

  • #NUM! 检查两个区域是否等长、是否同时包含正负金额,以及是否有日期早于首行日期。迭代计算未收敛也可能出现此错误。
  • #VALUE! 确认 Excel 能识别各个日期。如果 ISNUMBER(B2) 为 FALSE,日期可能以文本形式保存。TRUE 只表示该单元格含有数值,还需确认其对应的是预期日期。

现金流的符号多次变化时,可能有多个收益率满足方程。核对数据后,可以调整第三个参数,并用 XNPV 检查每个候选值。结果接近零表示该收益率是一个解,仍可能存在其他解。