XIRR Reverse lookup?

[Paris0022]

Hi,

I have a target goal of getting a 20% XIRR return on 3/31/2018. As you can see. My current IRR is -97%. What formula would tell me that I need X amount for 3/31/2018 to reach my goal of getting a exact 20% XIRR?


[TABLE="width: 1240"]
<colgroup><col span="11"><col><col span="4"><col><col span="2"></colgroup><tbody>[TR]
[TD="align: right"]2/1/2017[/TD]
[TD="align: right"]2/28/2017[/TD]
[TD="align: right"]3/31/2017[/TD]
[TD="align: right"]4/30/2017[/TD]
[TD="align: right"]5/31/2017[/TD]
[TD="align: right"]6/30/2017[/TD]
[TD="align: right"]7/31/2017[/TD]
[TD="align: right"]8/31/2017[/TD]
[TD="align: right"]9/30/2017[/TD]
[TD="align: center"]########[/TD]
[TD="align: center"]########[/TD]
[TD="align: right"]12/31/2017[/TD]
[TD="align: right"]1/31/2018[/TD]
[TD="align: right"]2/28/2018[/TD]
[TD="align: right"]3/31/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]-$150,000[/TD]
[TD="align: right"]$700[/TD]
[TD="align: right"]$700[/TD]
[TD="align: right"]$700[/TD]
[TD="align: right"]$700[/TD]
[TD="align: right"]$700[/TD]
[TD="align: right"]$700[/TD]
[TD="align: right"]$700[/TD]
[TD="align: right"]$700[/TD]
[TD="align: right"]$700[/TD]
[TD="align: right"]$700[/TD]
[TD="align: right"]$700[/TD]
[TD="align: right"]$1,300[/TD]
[TD="align: right"]$1,300[/TD]
[TD][/TD]
[TD][/TD]
[TD]Current XIRR[/TD]
[TD]XIRR Goal[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]-97%[/TD]
[TD="align: right"]20%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
Thanks![/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]


[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[JoeMo]

Try Data>Data Tools>What If> Goal Seek

Your value for 3/31/2018 would have to be $173,927.71 to bring your XIRR to 20%.

 

[Paris0022]

Once in Goal Seek, what do I enter?

Is there a excel formula to do this?

 

[JoeMo]

It's not a formula. Once in Goal Seek the cell to be changed is the one with the XIRR formula in it. The goal is 20% (enter it just like that), and the changing cell is the one below the last date. Note that the XIRR formula must include the last date cell and the one below it.

 

[joeu2004]

What formula would tell me that I need X amount for 3/31/2018 to reach my goal of getting a exact 20% XIRR?

=-SUMPRODUCT(A2:N2/(1+A4)^((A1:N1-A1)/365)) * (1+A4)^((O1-A1)/365)

if your values are in A2:O2, your dates are in A1:O1, and A4 is your goal (20%).

The result is 173928.130873074 (+ 2.62E-10).

 

[Paris0022]

Thank you! That worked perfectly.