Looking closer into this, I'm a bit confused about the 'estimated return' parameter of the XIRR function. What I'm dealing with is this:
time 1 = amount of money I had at one point in the past
time 2 = amount I have now (more than time1 due to investing)
variable 1 = amount I put into investments in that time period (the 'buys')
outputs I want:
1. how much of the change between time2 and time1 was my 'buys' versus my investment returns?
2. what's my investment return considering this?
I'm not seeing how XIRR gets me there
XIRR gets you #2: your total annualized investment return considering BOTH the return of the underlying investments AND your "buys".
It doesn't tell you #1 (what your investment return would have been without your "buys"), but do you actually want to know this? It sounds like your goal is to calculate your total annualized investment return, perhaps as a comparison to other investment options. Your wife pointed out that you were doing it wrong because your "buys" were overstating your rate-of-return. With #1, you're essentially looking for "how wrong was my previous calculation?" Who cares? Just do the calculation with XIRR going forward.
The 'estimated return' parameter is simply a requirement of the formula, because the mathematics behind the formula means that it can arrive at multiple different answers. So you have to give it a guess to start at to make sure it doesn't give you some crazy answer instead. Just put in '0.1' (for 10%) and I think that works in almost all cases. Only ever think about that value again if you ever see it give you a crazy, totally-unexpected result, which it probably won't.
ETA: and for utmost accuracy, your "variable 1 = amount I put into investments in that time period (the 'buys')" should really be a whole set of rows in your spreadsheet, one row with the dollar-amount and date of each investment. If you don't know the dates and amounts any more, you can lump a year's worth of "buys" into a single amount on a single date, but that will lower the accuracy of the calculation.