Forum Discussion
Backwards Solve For IRR
Hoping this is easy. It is an XIRR question.
Facts:
- I have several cash outflows of different amounts, at different dates over several years.
- I want to know what the dollar amount (cash inflow) needs to be to achieve an IRR of 10% today.
All I have been able to do so far is plug a number for the cash inflow until the XIRR function equals 10%. This obviously is inefficient as it takes a lot of plugging to finally get an exact value. I was thinking the solution might be some kind of XNPV calculation but that doesn't seem to get the proper result.
I am hoping there is an excel formula that can look at the cash outflows, the dates and the expected IRR of 10%, and then return the necessary value for cash inflow.
I tried to post my table of numbers but it doesn't look so good in this message box
I appreciate any help on this.
Dennis
Dennis11175 wrote: ``I was thinking the solution might be some kind of XNPV calculation``
That is correct, as long as the expect IRR is non-negative. Excle XNPV does not work with negative discount rates, for no good reason. (It is defect, IMHO.)
Re: ``All I have been able to do so far is plug a number for the cash inflow until the XIRR function equals 10%.``
Well, you could use Goal Seek or Solver to do that for you. But I will show how calculate it directly using XNPV.
Re: ``I tried to post my table of numbers but it doesn't look so good in this message box``
In this case, a screenshot would have been better than nothing. Alternatively and better, you might click "browse files" to attach an example Excel file that demonstrates the problem. Or you might upload the file to a file-sharing website, and post a download URL.
-----
Consider the following example.
Formula in B8:
=-XNPV(E2,B2:B7, A2:A7) * (1+E2)^((A8-A2) / 365)
2 Replies
Dennis11175 wrote: ``I was thinking the solution might be some kind of XNPV calculation``
That is correct, as long as the expect IRR is non-negative. Excle XNPV does not work with negative discount rates, for no good reason. (It is defect, IMHO.)
Re: ``All I have been able to do so far is plug a number for the cash inflow until the XIRR function equals 10%.``
Well, you could use Goal Seek or Solver to do that for you. But I will show how calculate it directly using XNPV.
Re: ``I tried to post my table of numbers but it doesn't look so good in this message box``
In this case, a screenshot would have been better than nothing. Alternatively and better, you might click "browse files" to attach an example Excel file that demonstrates the problem. Or you might upload the file to a file-sharing website, and post a download URL.
-----
Consider the following example.
Formula in B8:
=-XNPV(E2,B2:B7, A2:A7) * (1+E2)^((A8-A2) / 365)
- This is exactly what I was looking for. I plugged your formula into my table and boom! there it is! Thanks so much for this information.
I am a long time Excel user. In fact, I go all the way back to VisiCalc when it comes to spreadsheets. Excel is the tool that earns me a living and I use it every day. I don't know everything Excel can do, but I know that there is a way for it to do pretty much everything.
And thanks again for this information.
