I want to use XIRR to calculate return on investment where there's been a series of payments into the investment, but the investment is still held. In many cases there have been no payments out.
I can easily do that by notionally adding a line as if the investment was cashed in, based on the current valuation. And then overtype with the new date and amount each time I get a new valuation.
But I want to be a bit smarter than that.
Firstly, I don't like having that notional value line mixed in with other real transactions. I'd like to separate out the valuation date and amount so it's near my XIRR formula. But I can't figure out how to include non-contiguous ranges.
Example: Dates in A1,A2,A3,A4,F6; amounts in B1,B2,B3,B4,G6
F and G obviously being the notional valuation.
Secondly, those previous valuations have some important insight that I don't want to lose. Even when I have a later valuation I'd still like to contrast the return rate with the rate at the time of the earlier valuation, and even track the rate of return since the previous valuation.
What I'd ideally like to do is build a list of actual investments and returns, and a separate list of valuations, and for each valuation have a XIRR that pulls in the 'real' list to give ROI at that point, and a second XIRR that pulls the previous valuation to give a ROI from the previous point. I think if I can find a way to do non-contiguous XIRR calculations I think I should be able to build something like that.
Can anyone give me any pointers on how I could do that?
Later on I might introduce the third level of complexity, which would be to filter different kinds of investment within the portfolio but I can live without that for now!
Thank you!
I can easily do that by notionally adding a line as if the investment was cashed in, based on the current valuation. And then overtype with the new date and amount each time I get a new valuation.
But I want to be a bit smarter than that.
Firstly, I don't like having that notional value line mixed in with other real transactions. I'd like to separate out the valuation date and amount so it's near my XIRR formula. But I can't figure out how to include non-contiguous ranges.
Example: Dates in A1,A2,A3,A4,F6; amounts in B1,B2,B3,B4,G6
F and G obviously being the notional valuation.
Secondly, those previous valuations have some important insight that I don't want to lose. Even when I have a later valuation I'd still like to contrast the return rate with the rate at the time of the earlier valuation, and even track the rate of return since the previous valuation.
What I'd ideally like to do is build a list of actual investments and returns, and a separate list of valuations, and for each valuation have a XIRR that pulls in the 'real' list to give ROI at that point, and a second XIRR that pulls the previous valuation to give a ROI from the previous point. I think if I can find a way to do non-contiguous XIRR calculations I think I should be able to build something like that.
Can anyone give me any pointers on how I could do that?
Later on I might introduce the third level of complexity, which would be to filter different kinds of investment within the portfolio but I can live without that for now!
Thank you!
