Calculating XIRR w/ zero's in first few datapoints.

Greetings, I saw one post that previously addressed this issue with the Match and Offset function, but to be honest I don't think I fully understand either of those functions (I attempted to use them only to get errors).

That said, I've created the attached sheet to demonstrate my issue. What I need is for the range of the values and dates in the XIRR function to be dynamic according to the first value in a data-set. Any help would be appreciated. See attached for the example.

Cells C6 and D6 are obviously incorrect, and it's occurring because the XIRR function can't handle zero's as beginning values. In real life I have hundreds of deals in this portfolio instead of just 3, otherwise this wouldn't be an issue.

Really really appreciate the help.

I am not aware of the OFFSET and MATCH functions, but Excel reported your expected IRR values when I used this Excel XIRR function tadXIRR that is part of tadXL add-in

As you can see from the second last row that I have entered all values to the tadXIRR function calls including the leading zeroes and it produced the desired results

Other vantages of using tadXL version of XIRR includes the options for finding intra-year XIRR for different compounding frequencies of interest such as semi-annual, quarterly, monthly, fortnightly, weekly, daily or even hourly and continuous compounding amongst others

	Deal A	Deal B	Deal C
1/1/2010	-100	0	0
1/2/2010	0	-100	0
1/3/2010	0	0	-100
4/1/2013	200	200	200
XIRR	23.77819%	0%	0%
Should be	23.77819%	23.80048%	23.8228%
Using tadXL	tadXIRR(B2:B5,A2:A5)	tadXIRR(C2:C5,A2:A5)	tadXIRR(D2:D5,A2:A5)
tadXIRR	23.77819%	23.80048%	23.8228%

You can try out the tadXIRR function by downloading one of the following two files depending on your version of Excel

for 32 bit Excel 2007, 2010, 2013
http://tadxl.com/dl.php?fn=32001.zip

for 64 bit Excel 2010, 2013
http://tadxl.com/dl.php?fn=64001.zip