# XIRR issue with non negative beginning month values

1. ## XIRR issue with non negative beginning month values

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 !

2. ## Re: XIRR issue with non negative beginning month values

The first cash flow in Excel XIRR function does not have to be negative, you may have cash flows where the return is small and Excel is not able to find the rate using the default guess rate of 10%

You could try using a guess such as 1% or if the returns are negative then a guess rate of -10% should be tried

If you still face difficulty in finding internal rate of return for irregular cash flows, then 3rd party alternative add-in programs such as TADXL offer financial functions of their own that enhance the functionality of Excel functions

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1