So I don't usually get stumped in excel but.. here's my question hopefully someone can solve in Excel 2010:
I have a set of cash flows that are regularly periodic (monthly) for 12 years stripped out of a financial model. The first two years are periodic investments that are negative cash flows which occur some months and not others (shown as zero values). Currently I am using an XIRR formula to get the return for the full 12 years of CF's. In a positive IRR situation it works fine. If I grab the first 3-4 years which should produce a negative IRR, the XIRR formula goes to 0%.
Okay, I've researched this issue and it requires you to put a formula in the "Guess" argument of the XIRR formula such as 0.1*SIGN(SUM(A1:A3)) where A1:A3 are the cashflows. As soon as I put in this type of formula my XIRR goes directly to #NUM error. I believe this means that after 100 iterations IRR did not find a solution. I have tested multiple negative guess numbers with no success and I know about the range of the negative IRR it should produce (-2% or something).
At this point I decided that because the CFs are periodic I'll switch this to a regular IRR formula and annualize it. When I plug in an IRR formula it returns 34% which is completely bogus and gets worse when you annualize it. Upon further inspection I realize that no matter what size selection I use for the regular IRR formula of 3+ years it has the same % return. Another obvious error.
Here are some other quirks about the data that might be helpful:
Unfortunately the first value of the data is -0.000001 to negate the issue with XIRR of having a positive first CF value. There are many zero values in between months that have CF values.
Please let me know if you have any further questions and I really would appreciate the help. I have stripped out this issue into a separate excel file as an example but I'm not sure the best place to post it if someone wants to see the exact issue. Thanks again for the help!
Kevin
I have a set of cash flows that are regularly periodic (monthly) for 12 years stripped out of a financial model. The first two years are periodic investments that are negative cash flows which occur some months and not others (shown as zero values). Currently I am using an XIRR formula to get the return for the full 12 years of CF's. In a positive IRR situation it works fine. If I grab the first 3-4 years which should produce a negative IRR, the XIRR formula goes to 0%.
Okay, I've researched this issue and it requires you to put a formula in the "Guess" argument of the XIRR formula such as 0.1*SIGN(SUM(A1:A3)) where A1:A3 are the cashflows. As soon as I put in this type of formula my XIRR goes directly to #NUM error. I believe this means that after 100 iterations IRR did not find a solution. I have tested multiple negative guess numbers with no success and I know about the range of the negative IRR it should produce (-2% or something).
At this point I decided that because the CFs are periodic I'll switch this to a regular IRR formula and annualize it. When I plug in an IRR formula it returns 34% which is completely bogus and gets worse when you annualize it. Upon further inspection I realize that no matter what size selection I use for the regular IRR formula of 3+ years it has the same % return. Another obvious error.
Here are some other quirks about the data that might be helpful:
Unfortunately the first value of the data is -0.000001 to negate the issue with XIRR of having a positive first CF value. There are many zero values in between months that have CF values.
Please let me know if you have any further questions and I really would appreciate the help. I have stripped out this issue into a separate excel file as an example but I'm not sure the best place to post it if someone wants to see the exact issue. Thanks again for the help!
Kevin
Last edited:
