Forum Discussion
Backwards Solve For IRR
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)
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)
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.