Spreadsheet: 734647d1622324464-xirr-search-range-for-first-negative-value-xirr-first-neg
Sheet: Sheet1

 | IRR (Unadjusted): |  |  |  |  |  | <--How can I automate this formula so that XIRR looks-up the first negative value in the range and then starts the calculation as of that date?
 | IRR (SHOULD BE):
 | Difference:
 |  | Investor #1 | Investor #2 | Investor #3 | Investor #4 | Investor #5
 | 2007-07-03 00:00:00 | 0 | 0 | 0 | 0 | -100000
 | 2007-07-12 00:00:00 | 0 | 0 | -100000 | 0 | 0
 | 2007-08-30 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2007-09-06 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2007-09-10 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2007-09-14 00:00:00 | 0 | 0 | 0 | -100000 | 0
 | 2007-09-30 00:00:00 | 0 | -1249.86 | -1250.96 | -1249.86 | -1248.34
 | 2007-10-02 00:00:00 | -100000 | -100000 | 0 | 0 | 0
 | 2007-10-12 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2007-10-19 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2007-11-02 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2007-11-15 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2007-11-19 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2007-11-20 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2007-11-28 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2007-11-30 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2007-12-31 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2008-02-26 00:00:00 | 0 | 0 | -30000 | 0 | 0
 | 2008-02-27 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2008-02-28 00:00:00 | 0 | 0 | 0 | 0 | -100000
 | 2008-03-12 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2008-03-19 00:00:00 | 0 | 0 | -17500 | 0 | 0
 | 2008-03-31 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2008-04-10 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2008-04-16 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2008-05-06 00:00:00 | 0 | 0 | -20000 | 0 | 0
 | 2008-05-09 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2008-06-11 00:00:00 | -100000 | 0 | 0 | 0 | 0
 | 2008-06-19 00:00:00 | 0 | 0 | -32500 | 0 | 0
 | 2008-06-23 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2008-06-30 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2008-07-08 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2008-07-16 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2008-07-18 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2008-07-30 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2008-09-04 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2008-09-05 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2008-09-11 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2008-09-30 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2008-10-06 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2008-11-06 00:00:00 | 0 | 0 | -11000 | 0 | 0
 | 2008-11-10 00:00:00 | 0 | 0 | 0 | 0 | -11000
 | 2008-12-10 00:00:00 | 0 | 0 | 0 | 0 | -22333
 | 2008-12-30 00:00:00 | 0 | 0 | -22000 | 0 | 0
 | 2008-12-31 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2009-01-07 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2009-01-20 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2009-01-27 00:00:00 | 0 | 0 | 0 | 0 | -66667
 | 2009-01-28 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2009-01-29 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2009-01-30 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2009-02-18 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2009-03-27 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2009-03-31 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2009-04-02 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2009-05-07 00:00:00 | -66666 | 0 | 0 | 0 | 0
 | 2009-05-14 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2009-05-19 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2009-06-01 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2009-06-12 00:00:00 | -233333.34 | 0 | -233333.34 | -233333.34 | -233333.34
 | 2009-06-25 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2009-06-29 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2009-06-30 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2009-07-01 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2009-07-02 00:00:00 | 0 | -66667 | 0 | 0 | 0
 | 2009-09-30 00:00:00 | 4062.27 | 0 | 4062.26 | 4062.259999999999 | 4062.26
 | 2009-11-30 00:00:00 | 0 | -166666.67 | 0 | 0 | 0
 | 2009-12-01 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2009-12-04 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2009-12-30 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2009-12-31 00:00:00 | 3391.43 | 0 | 3391.436666666666 | 3391.44 | 3391.44
 | 2010-01-26 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2010-02-11 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2010-03-18 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2010-03-31 00:00:00 | 3354.17 | 0 | 3354.17333333333 | 3354.17 | 3354.17
 | 2010-04-28 00:00:00 | 0 | 0 | -6270.5 | 0 | -7444.5
 | 2010-05-28 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2010-06-02 00:00:00 | 0 | 0 | -2198 | 0 | -2611.5
 | 2010-06-15 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2010-06-30 00:00:00 | 24427.32 | 14170 | 22788.79 | 15818.32 | 26342.32
 | 2010-08-05 00:00:00 | 0 | 0 | -2114.54 | 0 | -2686
 | 2010-08-31 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2010-09-20 00:00:00 | 0 | 0 | -2236.53 | 0 | -2555.5
 | 2010-09-24 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2010-09-28 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2010-09-30 00:00:00 | 16574 | 10299 | 16897 | 11083 | 19325
 | 2010-10-07 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2010-10-19 00:00:00 | 0 | 0 | -2201.5 | 0 | -2546
 | 2010-10-22 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2010-10-29 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2010-11-04 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2010-11-17 00:00:00 | 0 | 0 | -2200.5 | 0 | -2551.5
 | 2010-12-13 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2010-12-28 00:00:00 | 0 | 0 | -2169.5 | 0 | -2536
 | 2010-12-30 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2010-12-31 00:00:00 | 11087 | 6750 | 11238 | 7342 | 12675
 | 2011-01-04 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2011-01-11 00:00:00 | -166000 | 0 | 0 | 0 | 0
 | 2011-02-04 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2011-03-31 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2011-04-30 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2011-05-31 00:00:00 | 0 | 1250 | 1250 | 0 | 1250
 | 2011-06-30 00:00:00 | 1250 | 1250 | 1250 | 1250 | 1250
 | 2011-07-31 00:00:00 | 1250 | 1250 | 1250 | 1250 | 1250
 | 2011-08-31 00:00:00 | 1250 | 1250 | 1250 | 1250 | 1250
 | 2011-09-30 00:00:00 | 1250 | 1250 | 1250 | 1250 | 1250
 | 2011-10-31 00:00:00 | 1250 | 1250 | 1250 | 1250 | 1250
 | 2011-11-30 00:00:00 | 1250 | 1250 | 1250 | 1250 | 1250
 | 2011-12-31 00:00:00 | 1250 | 1250 | 1250 | 1250 | 1250
 | 2012-01-31 00:00:00 | 1250 | 1250 | 1250 | 1250 | 1250
 | 2012-02-29 00:00:00 | 1250 | 1250 | 1250 | 1250 | 1250
 | 2012-03-31 00:00:00 | 1250 | 1250 | 1250 | 1250 | 1250
 | 2012-04-30 00:00:00 | 1250 | 1250 | 1250 | 1250 | 1250
 | 2012-05-31 00:00:00 | 1250 | 1250 | 1250 | 1250 | 1250
 | 2012-06-30 00:00:00 | 1250 | 1250 | 1250 | 1250 | 1250
 | 2012-07-31 00:00:00 | 1250 | 1250 | 1250 | 1250 | 1250
 | 2012-08-31 00:00:00 | 1250 | 1250 | 1250 | 1250 | 1250
 | 2012-09-30 00:00:00 | 1250 | 1250 | 1250 | 1250 | 1250
 | 2012-10-31 00:00:00 | 1250 | 1250 | 1250 | 1250 | 1250
 | 2012-11-30 00:00:00 | 1250 | 1250 | 1250 | 1250 | 1250
 | 2012-12-31 00:00:00 | 1250 | 1250 | 1250 | 1250 | 1250
 | 2013-01-31 00:00:00 | 1250 | 1250 | 1250 | 1250 | 1250
 | 2013-02-28 00:00:00 | 1250 | 1250 | 1250 | 1250 | 1250
 | 2013-03-31 00:00:00 | 1250 | 1250 | 1250 | 1250 | 1250
 | 2013-04-30 00:00:00 | 1250 | 1250 | 1250 | 1250 | 1250
 | 2013-05-31 00:00:00 | 1250 | 1250 | 1250 | 1250 | 1250
 | 2013-06-30 00:00:00 | 1250 | 1250 | 1250 | 1250 | 1250
 | 2013-07-31 00:00:00 | 17673.48 | 6699.05 | 11090.35 | 6962.76 | 12953.53
 | 2013-08-31 00:00:00 | 1250 | 1250 | 1250 | 1250 | 1250
 | 2013-09-30 00:00:00 | 1250 | 1250 | 1250 | 1250 | 1250
 | 2013-10-31 00:00:00 | 1250 | 1250 | 1250 | 1250 | 1250
 | 2013-11-30 00:00:00 | 1250 | 1250 | 1250 | 1250 | 1250
 | 2013-12-31 00:00:00 | 1250 | 1250 | 1250 | 1250 | 1250
 | 2014-01-31 00:00:00 | 59089.64 | 19324.53 | 33872.75999999999 | 20065.99 | 40116.93
 | 2014-02-28 00:00:00 | 1250 | 1250 | 1250 | 1250 | 1250
 | 2014-03-31 00:00:00 | 1250 | 1250 | 1250 | 1250 | 1250
 | 2014-04-30 00:00:00 | 1250 | 1250 | 1250 | 1250 | 1250
 | 2014-05-31 00:00:00 | 1250 | 1250 | 1250 | 1250 | 1250
 | 2014-06-30 00:00:00 | 1250 | 1250 | 1250 | 1250 | 1250
 | 2014-07-31 00:00:00 | 22122.05 | 10365.53 | 17800.69 | 10715.1 | 21012.34
 | 2014-08-31 00:00:00 | 1250 | 1250 | 1250 | 1250 | 1250
 | 2014-09-30 00:00:00 | 1250 | 1250 | 1250 | 1250 | 1250
 | 2014-10-31 00:00:00 | 1250 | 1250 | 1250 | 1250 | 1250
 | 2014-11-30 00:00:00 | 1250 | 1250 | 1250 | 1250 | 1250
 | 2014-12-31 00:00:00 | 1250 | 1250 | 1250 | 1250 | 1250
 | 2015-01-31 00:00:00 | 1250 | 1250 | 1250 | 1250 | 1250
 | 2015-02-28 00:00:00 | 1250 | 1250 | 1250 | 1250 | 1250
 | 2015-03-31 00:00:00 | 44357.92 | 19356.36 | 34213.41 | 19930.66 | 40702.9
 | 2015-04-30 00:00:00 | 1250 | 1250 | 1250 | 1250 | 1250
 | 2015-05-31 00:00:00 | 1250 | 1250 | 1250 | 1250 | 1250
 | 2015-06-30 00:00:00 | 1250 | 1250 | 1250 | 1250 | 1250
 | 2015-07-31 00:00:00 | 1250 | 1250 | 1250 | 1250 | 1250
 | 2015-08-31 00:00:00 | 1250 | 1250 | 1250 | 1250 | 1250
 | 2015-09-30 00:00:00 | 1250 | 1250 | 1250 | 1250 | 1250
 | 2015-10-31 00:00:00 | 1250 | 1250 | 1250 | 1250 | 1250
 | 2015-11-30 00:00:00 | 1250 | 1250 | 1250 | 1250 | 1250
 | 2015-12-31 00:00:00 | 1250 | 1250 | 1250 | 1250 | 1250
 | 2016-01-31 00:00:00 | 1250 | 1250 | 1250 | 1250 | 1250
 | 2016-02-29 00:00:00 | 1250 | 1250 | 1250 | 1250 | 1250
 | 2016-03-31 00:00:00 | 1250 | 1250 | 1250 | 1250 | 1250
 | 2016-04-30 00:00:00 | 3365.17 | 1346.6 | 2458.87 | 1377.51 | 2951.69
 | 2016-05-31 00:00:00 | 3374.38 | 1360.41 | 2465.79 | 1390.48 | 2955.94
 | 2016-06-30 00:00:00 | 3380.19 | 1368.77 | 2464.38 | 1397.32 | 2950.93
 | 2016-07-31 00:00:00 | 3371.35 | 1387.61 | 2464.19 | 1415.07 | 2942.64
 | 2016-08-31 00:00:00 | 3370.43 | 1396.06 | 2463.7 | 1422.71 | 2938.51
 | 2016-09-30 00:00:00 | 3369.62 | 1403.64 | 2463.25 | 1429.55 | 2934.8
 | 2016-10-31 00:00:00 | 3368.8 | 1411.3 | 2462.79 | 1436.47 | 2931.04
 | 2016-11-30 00:00:00 | 3368.04 | 1418.43 | 2462.37 | 1442.91 | 2927.53
 | 2016-12-31 00:00:00 | 3367.28 | 1425.51 | 2461.95 | 1449.31 | 2924.09
 | 2017-01-31 00:00:00 | 3366.62 | 1432.35 | 2461.6 | 1455.49 | 2920.8
 | 2017-02-01 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2017-02-28 00:00:00 | 3365.39 | 1438.02 | 2460.81 | 1460.59 | 2917.39
 | 2017-03-31 00:00:00 | 3365.39 | 1438.02 | 2460.81 | 1460.59 | 2917.39
 | 2017-04-30 00:00:00 | 3362.92 | 1449.86 | 2459.24 | 1471.23 | 2910.34
 | 2017-05-31 00:00:00 | 3361.74 | 1455.49 | 2458.49 | 1476.29 | 2906.97
 | 2017-06-30 00:00:00 | 3360.64 | 1460.74 | 2457.79 | 1481 | 2903.87
 | 2017-07-31 00:00:00 | 3359.54 | 1465.96 | 2457.09 | 1485.7 | 2900.8
 | 2017-08-31 00:00:00 | 3358.49 | 1471 | 2456.42 | 1490.23 | 2897.76
 | 2017-09-30 00:00:00 | 3357.5 | 1475.71 | 2455.79 | 1494.46 | 2894.97
 | 2017-10-31 00:00:00 | 3356.52 | 1480.4 | 2455.17 | 1498.68 | 2892.17
 | 2017-11-30 00:00:00 | 3355.6 | 1484.78 | 2454.58 | 1502.61 | 2889.6
 | 2017-12-31 00:00:00 | 3355.6 | 1484.78 | 2454.58 | 1502.61 | 2889.6
 | 2018-01-31 00:00:00 | 3353.79 | 1493.44 | 2453.43 | 1510.4 | 2884.41
 | 2018-02-28 00:00:00 | 3353.02 | 1497.12 | 2452.94 | 1513.71 | 2882.25
 | 2018-03-05 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2018-03-31 00:00:00 | 3352.19 | 1501.08 | 2452.41 | 1517.26 | 2879.86
 | 2018-04-25 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2018-04-30 00:00:00 | 3352.05 | 1505.07 | 2452.39 | 1520.88 | 2878.24
 | 2018-05-31 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2018-06-30 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2018-07-31 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2018-08-31 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2018-09-30 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2018-10-29 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2018-10-31 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2018-11-30 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2018-12-31 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2019-01-31 00:00:00 | 17047.83 | 7809.1 | 12475.16 | 7875.36 | 14579.32
 | 2019-02-28 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2019-03-31 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2019-04-30 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2019-05-31 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2019-06-30 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2019-07-31 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2019-08-31 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2019-09-30 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2019-10-31 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2019-11-30 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2019-12-31 00:00:00 | 17093 | 8029 | 12576 | 8083 | 14632
 | 2020-01-31 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2020-02-29 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2020-03-31 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2020-04-30 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2020-05-31 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2020-06-30 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2020-07-31 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2020-08-31 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2020-09-30 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2020-10-31 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2020-11-30 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2020-12-31 00:00:00 | 52037 | 24724 | 38255 | 24860 | 44389
 | 2021-01-31 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2021-02-28 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2021-03-31 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2021-04-30 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2021-05-31 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2021-06-30 00:00:00 | 0 | 0 | 0 | 0 | 0
 | 2021-06-30 00:00:00 | 455196.61 | 219514.56 | 334306 | 220369.51 | 386518.54