☰

從不相交的儲存格建立 Excel 範圍

tag-icon • tag-icon microsoft-excel
從不相交的儲存格建立 Excel 範圍

我有一個包含我的投資帳戶的電子表格，顯示每個月當月發生的存款或提款以及月末餘額。

我想使用 IRR 函數來計算我的報酬率。我可以給它存款和取款一欄，它會假設最後餘額為零來計算回報。例如這個：

Deposit 100
Withdraw 10

將顯示-90%的回報率。然而，如果我的帳戶裡還有 95 美元，那麼實際回報率為 +5%。

我的桌子看起來像這樣：

Deposits  Balance
100       100
-10       95
0         103
0         98
100       215

我可以在右側添加一列“回報率”，其公式為"=IRR($A$2:A3)"

然而，這又給了我不準確的數據，因為 IRR 函數假設最終餘額為零並且只接受單一範圍。有沒有辦法做這樣的事情：

=IRR(ConcatenateRange($A$2:A3, B3))

……這樣它看到的最終「提款」等於我目前的餘額？

答案1

我認為您可能堅持您所確定的解決方法 - 將期末餘額顯示為月底（或期間）的（名義）提款 - 並將上個月的期末餘額存入作為（名義）存款月初。不幸的是，我認為您犯了另一個微妙的錯誤：IRR 假設偶數時間段的現金流量 - 我不認為您有這樣的情況，除非“0”整體保持偶數週期（每週？）。如果現金流量不定期發生（特別是如果您在較長的時間段（例如一年或更長）內使用此方法），要計算您的回報，您將必須使用XIRR 函數，該函數採用兩個值範圍- 現金流的日期交易和現金流量。

* 更新 *

我知道我以前做過類似的事情 - 只是花了一段時間才找到它！看看這個線來自 Excel 先生。重點是，您將現金流量和日期（包括期末餘額值和日期）載入到兩個陣列中，然後評估這兩個陣列的 XIRR。具有 UDF 來執行此操作的帖子大約是列表中的第三個。上一篇文章斷言 IRR 將接受非連續範圍 - 我從未測試過這一點，但值得一試！

相關內容