XIRR with positive cashflow upfront
I have a series of cash flows which has its first element as positive. The XIRR function in excel is giving a result of 0%.
Is it mandatory to have the first the element as negative in order to get the right XIRR?
Can I get the right value by inputting the right "guess" parameters?
Is there any other formula that I do not know of?
Please help!!!
Try this. say your first cash flow is 30-Apr-2012. Add one more cash flow before this one, and make it a small number like (negative) -0.00001 and the date of this cash flow would also be 30-Apr-2012. At times this has worked for me but depends on the cash flow profile.
^Already tried this bro..Gives a NUM error.. :(
Your IRR is probably extremely high (in which case check your cash flows!). One way of finding the IRR is to "guess" the IRR yourself. Use any starting point (say 10%) and find the discounted cash flow for each date ( discount factor for each date [ 1 / (1 + rate)^ ((date - start date)/365) ^n ]). Sum all the discounted cash flows. Then change the starting point discount rate (in this case 10%) using goal seek or manually such that the sum of the DCF is 0. Obviously if XIRR cannot find a solution, this method is likely to give you a very very high number, but it may be taken as the "guess" for the XIRR function.
1st annualize your cashflow and try to run a regular IRR, if this gives you an answer then check your dates on the XIRR. If this does not work, see the next step.
Agree with above you may have an IRR crossover problem (multiple IRRs). If this is correct then you will need to find the discount rate that makes the SUM of the PV of the Cash flows 0. (Text book IRR). Create the discount factor (CF/(1+DR)^((DateN-Date1)/365)) and then changed the discount rate (DR) until the sum of the CFs is 0. Don't try to run a Macro or solver to find the discount rate, you will just get an incorrect number and may bust your model, do it manually.
Magni et molestiae voluptatum atque cupiditate accusantium temporibus quis. Eos in autem maiores voluptatibus sequi aliquid. Harum qui ut earum amet earum quas. Ipsam enim facere quia voluptas.
Voluptatem aut dolore accusamus voluptatem corrupti. Consequatur nulla est ratione debitis rerum quaerat. Sint magnam vero fugit sapiente qui.
Quis aliquam sunt totam sint nemo. Omnis rerum voluptates et voluptatem. Qui tempora nihil facere qui fuga nemo.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...