I'm using the Excel XIRR function because I have inflows and outflows on the same dates. To use the IRR function instead, I would have to compute the net inflow or outflow on a given date and then have a single row for that date's net inflow/outflow. All of my dates are always on the first of the given month.
The problem I'm having is the XIRR function is not giving the IRR that I have guessed. To illustrate this, in the spreadsheet below is a list of inflows and outflows (column C) and the corresponding dates (column A). The IRR computed by Excel's XIRR function is 39.098%. You can see this on the spreadsheet and you can see the function I used to compute it.
I then compute the month number, where the first month is month number zero. The formula to compute the month number is also shown.
I then compute the present value of the inflow/outflow (from column C) using the month number and a rate that I guessed at (cell C31). I then add up all of the present values, getting 1.97 in cell I29 (all the formulas are shown). I just fiddled around with the rate (cell C31) trying to get a sum of zero. I stopped when I got to 1.97, figuring that was close enough. As this sum is nearly zero, the rate of 33.430% is the IRR.
There is a fairly significant difference between Excel's XIRR and my IRR. Why is this?