I am working on a model where i would like to be able to change the functionality of what month i advance funds against (purchase) a pool of cash flowing loans.
The issue I am am having is that when I have advances funds against (purchase) the loans in a month other than month 0 my =XIRR() function gives me an error because there is a non negative value in the begining of the string of values (even if I zero out cash flows in the months prior to purchase it still returns an error.)
What I am wondering is if there is a way to have an =XIRR() calculation that has a dynamic beginning month embedded in the formula that would be tied to the month that I advance funds against (purchase) the pool of loans (it is a very simple fix to take the cash flows from the months prior to the purchase of loans model them going into escrow and amortizing them over x number of months.)
The only work around I have been able to come up with is a series of =IF() statements that make sure the first month in the string of values for the =XIRR() calculation is negative and corresponds to the first advance of funds (purchase.) Because I want the functionality to advance up to month 13 of a program the statement gets very long and ugly.
Any help would be greatly appreciated !
Bookmarks