0

I have two columns that I'd like to use to calculate a rolling monthly IRR with. The data looks like this:

Date          Net Cash Flow   Principal    IRR
2023-01-31    0               0            idk
2023-02-28    -62             62           ...
2023-03-31    1.4             62           
2023-04-30    1.3             62
2023-05-31    1.3             62
2023-06-30    -62             124
...           ...             ...

I would like to calculate a rolling IRR that will continually recalculate each month for the lifetime of the fund assuming the principal is fully paid back. Basically it would be a "point in time" IRR that would update as I add new cash flows and the principal is written down or increases in value. How can I do this in a single column (or with only helper columns)?

weskpga
  • 2,017
  • 7
  • 29
  • 43
  • 6
    Perhaps include a few manually calculated expected results? – Jos Woolley Mar 23 '22 at 15:55
  • 2
    There is an `IRR`-Function in Excel: https://support.microsoft.com/en-us/office/irr-function-64925eaa-9988-495b-b290-3ad0c163c1bc - or do you mean sth different? – Ike Mar 24 '22 at 16:16
  • first google result is what you want, including excel example. https://www.investopedia.com/terms/i/irr.asp Because your periods are always the same (all months) it should work for you – Foxfire And Burns And Burns Mar 28 '22 at 13:03

1 Answers1

2

I'm guessing a bit at what you're asking here, but if you have the IRR formula start at cell R2C2 every time, and end at the relative cell RC3, wouldn't that give you what you're looking for?

Original table plus columns with the IRR formulas and values.

John Williams
  • 179
  • 3