Spreadsheet: 98332d1297277723-xirr-and-irr-irr-riddle
Sheet: Sheet1

 | Model starts CF on Jan 2010 |  |  |  |  |  |  |  |  |  |  |  |  |  | x |  |  |  |  |  |  |  |  |  |  |  | x |  |  |  |  |  |  |  |  |  |  |  | x |  |  |  |  |  |  |  |  |  |  |  | x |  |  |  |  |  |  |  |  |  |  |  | x |  |  |  |  |  |  |  |  |  |  |  | x |  |  |  |  |  |  |  |  |  |  |  | x |  |  |  |  |  |  |  |  |  |  |  | x |  |  |  |  |  |  |  |  |  |  |  | x |  |  |  |  |  |  |  |  |  |  |  | x |  |  |  |  |  |  |  |  |  |  |  | x |  |  |  |  |  |  |  |  |  |  |  | x |  |  |  |  |  |  |  |  |  |  |  | x |  |  |  |  |  |  |  |  |  |  | X
 |  |  |  |  |  | 2007-04-30 00:00:00 | 2007-05-31 00:00:00 | 2007-06-30 00:00:00 | 2007-07-31 00:00:00 | 2007-08-31 00:00:00 | 2007-09-30 00:00:00 | 2007-10-31 00:00:00 | 2007-11-30 00:00:00 | 2007-12-31 00:00:00 | 2008-01-31 00:00:00 | 2008-02-29 00:00:00 | 2008-03-31 00:00:00 | 2008-04-30 00:00:00 | 2008-05-31 00:00:00 | 2008-06-30 00:00:00 | 2008-07-31 00:00:00 | 2008-08-31 00:00:00 | 2008-09-30 00:00:00 | 2008-10-31 00:00:00 | 2008-11-30 00:00:00 | 2008-12-31 00:00:00 | 2009-01-31 00:00:00 | 2009-02-28 00:00:00 | 2009-03-31 00:00:00 | 2009-04-30 00:00:00 | 2009-05-31 00:00:00 | 2009-06-30 00:00:00 | 2009-07-31 00:00:00 | 2009-08-31 00:00:00 | 2009-09-30 00:00:00 | 2009-10-31 00:00:00 | 2009-11-30 00:00:00 | 2009-12-31 00:00:00 | 2010-01-31 00:00:00 | 2010-02-28 00:00:00 | 2010-03-31 00:00:00 | 2010-04-30 00:00:00 | 2010-05-31 00:00:00 | 2010-06-30 00:00:00 | 2010-07-31 00:00:00 | 2010-08-31 00:00:00 | 2010-09-30 00:00:00 | 2010-10-31 00:00:00 | 2010-11-30 00:00:00 | 2010-12-31 00:00:00 | 2011-01-31 00:00:00 | 2011-02-28 00:00:00 | 2011-03-31 00:00:00 | 2011-04-30 00:00:00 | 2011-05-31 00:00:00 | 2011-06-30 00:00:00 | 2011-07-31 00:00:00 | 2011-08-31 00:00:00 | 2011-09-30 00:00:00 | 2011-10-31 00:00:00 | 2011-11-30 00:00:00 | 2011-12-31 00:00:00 | 2012-01-31 00:00:00 | 2012-02-29 00:00:00 | 2012-03-31 00:00:00 | 2012-04-30 00:00:00 | 2012-05-31 00:00:00 | 2012-06-30 00:00:00 | 2012-07-31 00:00:00 | 2012-08-31 00:00:00 | 2012-09-30 00:00:00 | 2012-10-31 00:00:00 | 2012-11-30 00:00:00 | 2012-12-31 00:00:00 | 2013-01-31 00:00:00 | 2013-02-28 00:00:00 | 2013-03-31 00:00:00 | 2013-04-30 00:00:00 | 2013-05-31 00:00:00 | 2013-06-30 00:00:00 | 2013-07-31 00:00:00 | 2013-08-31 00:00:00 | 2013-09-30 00:00:00 | 2013-10-31 00:00:00 | 2013-11-30 00:00:00 | 2013-12-31 00:00:00 | 2014-01-31 00:00:00 | 2014-02-28 00:00:00 | 2014-03-31 00:00:00 | 2014-04-30 00:00:00 | 2014-05-31 00:00:00 | 2014-06-30 00:00:00 | 2014-07-31 00:00:00 | 2014-08-31 00:00:00 | 2014-09-30 00:00:00 | 2014-10-31 00:00:00 | 2014-11-30 00:00:00 | 2014-12-31 00:00:00 | 2015-01-31 00:00:00 | 2015-02-28 00:00:00 | 2015-03-31 00:00:00 | 2015-04-30 00:00:00 | 2015-05-31 00:00:00 | 2015-06-30 00:00:00 | 2015-07-31 00:00:00 | 2015-08-31 00:00:00 | 2015-09-30 00:00:00 | 2015-10-31 00:00:00 | 2015-11-30 00:00:00 | 2015-12-31 00:00:00 | 2016-01-31 00:00:00 | 2016-02-29 00:00:00 | 2016-03-31 00:00:00 | 2016-04-30 00:00:00 | 2016-05-31 00:00:00 | 2016-06-30 00:00:00 | 2016-07-31 00:00:00 | 2016-08-31 00:00:00 | 2016-09-30 00:00:00 | 2016-10-31 00:00:00 | 2016-11-30 00:00:00 | 2016-12-31 00:00:00 | 2017-01-31 00:00:00 | 2017-02-28 00:00:00 | 2017-03-31 00:00:00 | 2017-04-30 00:00:00 | 2017-05-31 00:00:00 | 2017-06-30 00:00:00 | 2017-07-31 00:00:00 | 2017-08-31 00:00:00 | 2017-09-30 00:00:00 | 2017-10-31 00:00:00 | 2017-11-30 00:00:00 | 2017-12-31 00:00:00 | 2018-01-31 00:00:00 | 2018-02-28 00:00:00 | 2018-03-31 00:00:00 | 2018-04-30 00:00:00 | 2018-05-31 00:00:00 | 2018-06-30 00:00:00 | 2018-07-31 00:00:00 | 2018-08-31 00:00:00 | 2018-09-30 00:00:00 | 2018-10-31 00:00:00 | 2018-11-30 00:00:00 | 2018-12-31 00:00:00 | 2019-01-31 00:00:00 | 2019-02-28 00:00:00 | 2019-03-31 00:00:00 | 2019-04-30 00:00:00 | 2019-05-31 00:00:00 | 2019-06-30 00:00:00 | 2019-07-31 00:00:00 | 2019-08-31 00:00:00 | 2019-09-30 00:00:00 | 2019-10-31 00:00:00 | 2019-11-30 00:00:00 | 2019-12-31 00:00:00 | 2020-01-31 00:00:00 | 2020-02-29 00:00:00 | 2020-03-31 00:00:00 | 2020-04-30 00:00:00 | 2020-05-31 00:00:00 | 2020-06-30 00:00:00 | 2020-07-31 00:00:00 | 2020-08-31 00:00:00 | 2020-09-30 00:00:00 | 2020-10-31 00:00:00 | 2020-11-30 00:00:00 | 2020-12-31 00:00:00
 |  |  |  |  |  | -1e-10 | 1000000 | -918500 | 0 | 0 | 0 | 0 | 0 | -81500 | 0 | 0 | 0 | 0 | 0 | 0 | -19807500 | 0 | 0 | 339000 | 0 | 0 | 0 | 0 | 0 | 80000 | 0 | 117000 | 0 | 0 | 0 | 0 | 0 | 550000 | 0 | 0 | 150000 | -1172587 | 0 | 200000 | 0 | 0 | 204081.63 | 0 | 0 | 300000 | 32561.24789027169 | 38305.58407528761 | 44034.11359471919 | 49748.14120770576 | 55448.95322958087 | 61137.81799553228 | 72723.33079684757 | 78793.82055882933 | 84850.54883624519 | 90894.86060698643 | 96928.08231945992 | 102951.5223649524 | 104391.1862347612 | 105847.4394645726 | 107320.5760895921 | 108810.8905964976 | 110318.6779904914 | 111844.233861483 | 113387.8544494327 | 114949.8367088857 | 116530.4783727162 | 118130.0780151178 | 119748.9351138579 | 121387.3501118268 | 120948.9664629328 | 121023.1368282065 | 121054.1327072306 | 121042.6369775613 | 120989.3158538583 | 120894.8192532284 | 120940.4864053473 | 120942.1929035216 | 120900.6320948683 | 120816.4803945427 | 120690.3976569227 | 120523.0275384613 | 123112.0302567148 | 125638.725690425 | 128104.3568541012 | 130510.1409077701 | 132857.2696925333 | 134934.8214565822 | 136884.5954650973 | 138771.4675185592 | 140596.6923637102 | 142361.4986486455 | 144067.0894634279 | 145714.6428695015 | 147683.0675315908 | 149587.9103940632 | 151430.4396944115 | 153211.8972908698 | 154933.4992088446 | 156596.4361760165 | 158587.1102853357 | 160513.4445854185 | 162376.7224177444 | 164178.2004304872 | 165919.109131452 | 167600.6534295491 | 169616.8784830477 | 171567.9336742547 | 173455.1189349761 | 175279.7071588614 | 177042.9447614805 | 178746.0522287969 | 180790.8702388103 | 182769.6216278646 | 184683.624293781 | 186534.1687228591 | 188322.518557685 | 190049.9111531828 | 192126.1380518547 | 194135.3395106505 | 196078.852672792 | 197957.9868700923 | 199774.0241990522 | 201528.2200850223 | 203638.4752425906 | 205680.688114545 | 207656.2162852669 | 209566.3891030579 | 211412.5082650358 | 213195.848389912 | 215342.5807068426 | 217420.1993580903 | 219430.0834925388 | 221373.5835750377 | 223252.0219805793 | 225066.6935761639 | 227252.2045548397 | 229367.478967566 | 231413.9185885723 | 233392.8960381093 | 235305.7553863614 | 237153.8127448474 | 239380.2768986282 | 241535.3326569578 | 243620.4054265198 | 245636.8909691958 | 247586.1560161527 | 249469.5388691958 | 251739.0217844723 | 253935.8777619798 | 256061.5568019894 | 258117.4787492274 | 260105.0339175426 | 262025.5837016227 | 264340.0580312295 | 266580.642052674 | 268748.8112818526 | 270846.0105492584 | 272873.6546356273 | 274833.128894407 | 277194.4885279274 | 279480.6512456645 | 281693.1189681659 | 283833.3623822898 | 285902.8215882105 | 45001572.19761762
 | Thought Process:
 |  | 10 Yr. | IRR |  | Works perfectly
 | Adjust XIRR to a negative IRR situation:
 |  | 1 Yr. | IRR |  | This should show a negative IRR
 | Researched issue and added a negative guess:
 |  | 1 Yr. | IRR |  | With negative guess it goes to #[NAME]
 | Thought to switch formulas to an annualized IRR formula because cash flows are periodic (but monthly):
 | 10 Yr | Monthly | IRR |  | Wrong
 |  | Annualized | IRR |  | [NAME]
 | Something else interesting - When I grab a different amount of the same set of CF's the regular IRR formula produces the same result:
 |  | Monthly | IRR |  | Wrong