Calculating a Rolling IRR in Excel

Question

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)?

Answer 1

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?