Hi Folks,
Great forum! This is my first post, but I've used this resource in the past. Here's an issue I haven't been able to solve with the usual googling / forum lurking:
I have ~monthly portfolio snapshots that include total portfolio value, contributions, and the date. It's a bit of a trouble to arrange the data to use XIRR as the XIRR function wants one array of values and one of dates. I'm not able to figure out how to use Choose() or another function to aggregate the arrays. I cannot use IRR because the data isn't necessarily in equal periods of time.
GOAL: I would like to keep entering monthly data and have a cell in each monthly column that calculates the portfolio rate of return for the year through that month (or some other period)
I've created a very simple example. The top ~half of the spreadsheet replicates my data. The yellow cells represent where I'd like to add a formula to calculate return.
The purple cells are what I have to do to calculate using XIRR which is in the green cells. Unfortunately, this workaround isn't really sustainable as I have much more data than this simplistic spreadsheet.
Would love someone to point me the right direction on this. Do I need to educate myself more about the array functions, would that solve this?
Thanks in advance!
Keith
Great forum! This is my first post, but I've used this resource in the past. Here's an issue I haven't been able to solve with the usual googling / forum lurking:
I have ~monthly portfolio snapshots that include total portfolio value, contributions, and the date. It's a bit of a trouble to arrange the data to use XIRR as the XIRR function wants one array of values and one of dates. I'm not able to figure out how to use Choose() or another function to aggregate the arrays. I cannot use IRR because the data isn't necessarily in equal periods of time.
GOAL: I would like to keep entering monthly data and have a cell in each monthly column that calculates the portfolio rate of return for the year through that month (or some other period)
I've created a very simple example. The top ~half of the spreadsheet replicates my data. The yellow cells represent where I'd like to add a formula to calculate return.
The purple cells are what I have to do to calculate using XIRR which is in the green cells. Unfortunately, this workaround isn't really sustainable as I have much more data than this simplistic spreadsheet.
Would love someone to point me the right direction on this. Do I need to educate myself more about the array functions, would that solve this?
Thanks in advance!
Keith
Attachments

13 KB Views: 7