不定期不定額的真實年化獲利：XIRR 內部報酬率演算法與 Excel/Stoxer 實作
解析不定期不定額投資年化報酬率 XIRR 的數學原理、牛頓拉夫森疊代法與 Excel/Google Sheets 計算，附台股定期定額完整試算範例。
在理財投資世界中，最常見的盲點就是「把累積總報酬率當成年化報酬率」。例如一個投資人在 3 年內分批扣款 12 次，累積投入 30 萬元，最後股票總市值變為 36 萬元，總損益為 +6 萬元 (+20%)。
如果直接將 +20% 除以 3 年得到「每年約 6.67%」，這其實嚴重低估了實際的資金效率！因為那 30 萬元並不是在第 1 天就一次全部投入的。考慮到後續投入的資金在市場停留的時間更短，這項投資實際發揮的年化報酬率其實高達 11.5% 以上！而能精準算出來這個數值的工具，就是 XIRR (Extended Internal Rate of Return)。
一、 XIRR 的數學定義與折現原理
XIRR 是內部報酬率 (IRR) 的擴充版本，專為「不固定時間間隔」且「金額不固定」的現金流設計。其核心原理是尋找一個折現率 r，使得所有未來與過去的現金流折現至初始日期的淨現值 (NPV, Net Present Value) 正好等於零：
其中：
- Pi：第 i 筆發生的現金流金額 (買進投資填負數，賣出/領息填正數)
- di：第 i 筆現金流發生的具體年月日日期
- d1：第一筆現金流發生的起始日期
- r：求解出的折現年化報酬率 (即 XIRR)
由於上述方程式無法直接用代數公式導出簡單解，軟體（如 Excel 或 Stoxer App）在後台會透過牛頓拉夫森法 (Newton-Raphson Method) 進行數值疊代計算，通常在 10~20 次逼近後求得估算到小數點後四位的 r 值。
二、 Excel / Google Sheets `=XIRR()` 手把手教學
在試算表中計算 XIRR 非常簡單，只需要建立兩欄資料：
| A 欄 (日期 Date) | B 欄 (現金流 Cash Flow) | 交易性質說明 |
|---|---|---|
| 2024-01-15 | -100,000 | 買進台股 0050 (口袋掏錢) |
| 2024-07-16 | +3,500 | 領取現金股利 (現金流入) |
| 2025-01-15 | -50,000 | 加碼買進 (口袋掏錢) |
| 2026-08-18 | +185,000 | 結算今日持股市值 (假設全賣出) |
公式語法：
按下 Enter 後，儲存格設定為百分比格式，即可精準得到這一段期間的實際年化報酬率。
三、 XIRR vs 簡單年化報酬率：差距有多大？
以下用同一個定期定額投資案例，比較「簡單年化報酬率」與「XIRR」兩種算法，說明為何後者更接近資金實際效率：
| 買進日期 | 買進金額 | 距今持有天數 | 資金年化貢獻 |
|---|---|---|---|
| 2024-01-01 (第 1 筆) | -50,000 | 约 600 天 | 資金最久，貢獻最高 |
| 2024-07-01 (第 2 筆) | -50,000 | 约 415 天 | 中等貢獻 |
| 2025-01-01 (第 3 筆) | -50,000 | 约 230 天 | 較低貢獻 |
| 2025-07-01 (第 4 筆) | -50,000 | 约 48 天 | 最新投入，貢獻極低 |
| 2026-08-18 (今日結算) | +240,000 | - | 總投入 20 萬，市值 24 萬 |
簡單年化報酬率（直覺算法）
≈ 13.3%
總獲利 4 萬 ÷ 總投入 20 萬 ÷ 約 1.5 年
⚠️ 忽略各筆入場時間差異，準確度低
XIRR（精準折現年化）
≈ 22.7%
考慮每筆資金實際在市場停留的天數
✅ 實際反映資金時間效率
這個例子中，因為後兩筆資金（2025-07-01 那筆）在市場的時間極短，但簡單年化法卻把它算為「投入整整 1.5 年」，大幅低估了前期資金發揮的效率。XIRR 透過折現機制準確還原每一筆資金的實際貢獻，因此計算出更高的年化值。
四、 XIRR 常見錯誤與注意事項
❌ 錯誤一：正負號填反
買進（投入資金，口袋的錢流出去）應填負數；賣出、收到股利（資金流回口袋）應填正數。若全部填正或正負一致，Excel 會回傳 #NUM! 錯誤。
❌ 錯誤二：忘記加入當前市值（未平倉持股）
若股票尚未全部賣出，必須在最後一列補上「今日日期」與「目前持股總市值（正數）」，模擬假設今日全賣出的情境，否則計算結果會極度失真（通常會是極端的負值）。
⚠️ 注意：股票股利（配股）不填入 XIRR
股票股利不是現金，無法作為現金流入。但它會增加你的持股數量，在最後計算「今日持股市值」時會自動反映進去。因此，只有現金股利（Cash Dividend）才需要在 XIRR 中填入為正數現金流。
⚠️ 注意：手續費與交易稅需納入計算
每次買進應填入「含手續費的實際交割付款金額（負數）」，賣出應填入「扣除手續費與證交稅後的實際淨收款（正數）」。這樣才能讓 XIRR 實際反映交易成本對報酬率的影響。
五、 台股定期定額完整試算：每月買 0050 三年的 XIRR
以下是一個最貼近台灣小資族的實際場景：每月 15 日固定扣款 5,000 元買進 0050 零股，連續 3 年（共 36 次），並每年領取現金股利一次。我們示範如何用 Excel XIRR 函數精準計算這 3 年的年化報酬率：
📋 Excel 操作步驟：
- 在 A 欄建立 36 筆買進日期（2023-01-15, 2023-02-15 ... 2025-12-15），每月 15 日一筆
- 在 B 欄對應填入每次扣款金額，全部填 -5,000（負號代表資金流出）
- 找到每年配息日期（如 2023-08-01, 2024-08-01, 2025-08-01），新增對應列，B 欄填入當年度領到的現金股利總額（正數）
- 在最後一列填入「今日日期」與「當前持股總市值（正數）」
- 在任意空白格輸入：
=XIRR(B2:B42, A2:A42) - 將該儲存格格式設定為「百分比」，即可看到精準的年化報酬率
🔥 延伸應用：退休資產與 FIRE 4% 法則試算
計算出精準的 XIRR 年化報酬率後，進一步結合台灣通膨率，即可估算退休金資產是否可永續提領！歡迎參考 FIRE 族 4% 法則退休金試算與通膨資產壽命模擬器。
💡 小技巧：用 Stoxer App 自動計算
若覺得手動建立 Excel 表格繁瑣，可以使用 Stoxer App 直接輸入每筆交易與股利記錄，系統會自動套用 Newton-Raphson 演算法即時顯示 XIRR 年化報酬率，免去手動建表的工作。所有資料儲存在你的本機裝置，完全不上傳至任何伺服器。
本專題內容僅供教育學習與算術觀念交流參考，不構成任何財務或投資建議。過去績效與 XIRR 計算結果不代表未來獲利保證。