Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Unexplainable XIRR #NUM! Error
I have this great model to calculate project returns, really flexible in lots
of ways. I have an amortization table for project loans and I can change the amount of the loan and the interest rate on an input tab and see how the IRR changes. I use the XIRR function which uses a line of cash flows and the corresponding dates (calculated using an EDATE function). There is no guess because the IRR could be anything. This model works really well. As I slowly ramp up the amount of the loan the IRR increases just as it should. For example 15% leverage with a 7% interest rate gives an IRR of 8.22%. 20% leverage at the same rate gives a 17.88% IRR. Then I go to 21% with the same interest rate and blammo! #NUM! error. I have analyzed the cash flows for the 20% and 21% scenario and cannot see any reason why the IRR would be so dramatically different. I have replicated this issue over and over again with this model. Depending on the project parameters there seems to always be a point where slightly increasing the amount of my loan takes a reasonable IRR and turns it into a #NUM! error. This is an xlsx file so I don't think the EDATE should be the issue, plus it works so well until the error that I just can't figure it out. I've even tried to put in a guess that I know should be really close and it still returns the error. Please help!!! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Unexplainable XIRR #NUM! Error
You may have two IRRS. I tried repeatedly to share an example of two IRRs
(on a free file hosting site). I couldnt upload the file, but look for some info on problems that occur with two IRRs. That may answer your question. Good luck, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Jay" wrote: I have this great model to calculate project returns, really flexible in lots of ways. I have an amortization table for project loans and I can change the amount of the loan and the interest rate on an input tab and see how the IRR changes. I use the XIRR function which uses a line of cash flows and the corresponding dates (calculated using an EDATE function). There is no guess because the IRR could be anything. This model works really well. As I slowly ramp up the amount of the loan the IRR increases just as it should. For example 15% leverage with a 7% interest rate gives an IRR of 8.22%. 20% leverage at the same rate gives a 17.88% IRR. Then I go to 21% with the same interest rate and blammo! #NUM! error. I have analyzed the cash flows for the 20% and 21% scenario and cannot see any reason why the IRR would be so dramatically different. I have replicated this issue over and over again with this model. Depending on the project parameters there seems to always be a point where slightly increasing the amount of my loan takes a reasonable IRR and turns it into a #NUM! error. This is an xlsx file so I don't think the EDATE should be the issue, plus it works so well until the error that I just can't figure it out. I've even tried to put in a guess that I know should be really close and it still returns the error. Please help!!! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Unexplainable XIRR #NUM! Error
Hi,
If you so wish, you may mail me the file at ask(at)ashishmathur(dot).com. Please explain the problem very clearly. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Jay" wrote in message ... I have this great model to calculate project returns, really flexible in lots of ways. I have an amortization table for project loans and I can change the amount of the loan and the interest rate on an input tab and see how the IRR changes. I use the XIRR function which uses a line of cash flows and the corresponding dates (calculated using an EDATE function). There is no guess because the IRR could be anything. This model works really well. As I slowly ramp up the amount of the loan the IRR increases just as it should. For example 15% leverage with a 7% interest rate gives an IRR of 8.22%. 20% leverage at the same rate gives a 17.88% IRR. Then I go to 21% with the same interest rate and blammo! #NUM! error. I have analyzed the cash flows for the 20% and 21% scenario and cannot see any reason why the IRR would be so dramatically different. I have replicated this issue over and over again with this model. Depending on the project parameters there seems to always be a point where slightly increasing the amount of my loan takes a reasonable IRR and turns it into a #NUM! error. This is an xlsx file so I don't think the EDATE should be the issue, plus it works so well until the error that I just can't figure it out. I've even tried to put in a guess that I know should be really close and it still returns the error. Please help!!! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Unexplainable XIRR #NUM! Error
While it's commonplace for XIRR to return #Num with the default guess of
10%, it's very unusual for XIRR to continue to fail with a decent guess. Are you sure you are entering the guess properly? (For example, it needs to be 21% or 0.21, not 21). If your cash flows are consistently periodic (ie monthly), then another option you have is to use the IRR function. It sometimes behaves better. If you want me to take a look at it, you can e-mail it to: fred dot smith at shaw dot ca. Regards, Fred. "Jay" wrote in message ... I have this great model to calculate project returns, really flexible in lots of ways. I have an amortization table for project loans and I can change the amount of the loan and the interest rate on an input tab and see how the IRR changes. I use the XIRR function which uses a line of cash flows and the corresponding dates (calculated using an EDATE function). There is no guess because the IRR could be anything. This model works really well. As I slowly ramp up the amount of the loan the IRR increases just as it should. For example 15% leverage with a 7% interest rate gives an IRR of 8.22%. 20% leverage at the same rate gives a 17.88% IRR. Then I go to 21% with the same interest rate and blammo! #NUM! error. I have analyzed the cash flows for the 20% and 21% scenario and cannot see any reason why the IRR would be so dramatically different. I have replicated this issue over and over again with this model. Depending on the project parameters there seems to always be a point where slightly increasing the amount of my loan takes a reasonable IRR and turns it into a #NUM! error. This is an xlsx file so I don't think the EDATE should be the issue, plus it works so well until the error that I just can't figure it out. I've even tried to put in a guess that I know should be really close and it still returns the error. Please help!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
XIRR Error | Excel Worksheet Functions | |||
XIRR Function - #NUM error | Excel Worksheet Functions | |||
xirr value error | Excel Worksheet Functions | |||
#VALUE error with XIRR | Excel Discussion (Misc queries) | |||
XIRR Function Error | Excel Worksheet Functions |