Getting exit value from Fixed IRR%. Any Help Appreciated.

[NikunjThakkar]

I am calculating IRR for a series of cash flows using the XIRR formula.

Please see attached excel. I invest 500 on 12/31/2011, earn dividends on 12/31/2012, 2013 and 2014 and finally exit the investment for 600 on 12/31/2015. This gives me an IRR of 6.2% using the XIRR formula.

Now i have a fixed desired IRR of 10%. How can i calculate the value at which to exit the investment to obtain the desired IRR?

I can do this using the goal seek function here, but the actual implementation of this concept has multiple conditions and scenarios attached, so goal seek is not a practical options, as I would have to run a goal seek each time the scenario or any condition was changed.

Has anyone ever used this before? Any help is appreciated, as always.

[MoneyMaker]

Hello

As you suggested, Goal Seek is one of the options that you do not wish to use. Other than this, Excel offers no functions of its own to solve problem such as the one you have presented.

But there is always good news, that if by using 3rd party Excel add-in program such as tadXL, you can easily find the target value given that you know your XIRR value.

The function that I will use to find your target amount at which you seek to exit the investment is called tadMCFXIRR, the MCF stands for missing cash flow. Thus if you know the XIRR you wish to have for your investment such as 10% then all you would need is provide this function with all of your cash flows and placing the letter X in the cell that contains your target amount at which you seek to exit the investment. You would also enter the date schedule as required including the date for the missing cash flow.

Please see the following two screen shots. The first screen shot uses the XIRR value of 6.2% that you had calculated for your investment by using Excel XIRR function. The tadMCFXIRR correctly reports the missing cash flow as $600

missing_cash_flow_xirr_6.2%.PNG


The second image shows you the calculation of missing cash flow when we enter our own value of XIRR such as 10% or any other target XIRR, and the tadMCFXIRR function finds the missing cash flow in amount of $694.62

missing_cash_flow_xirr_10%.PNG

Please also check the following Excel workbook with the tadMCFXIRR formula in it

missing_cash_flow_xirr.xlsx

-500	10	11	10	X
12/31/2011	12/31/2012	12/31/2013	12/31/2014	12/31/2015
-500	10	11	10	600
IRR	6.152%	missing cash flow	=tadMCFXIRR(G3:K3,G4:K4,H7)
IRR	6.152%	missing cash flow	600.00
IRR	10%	missing cash flow	=tadMCFXIRR(G3:K3,G4:K4,H8)
IRR	10%	missing cash flow	694.62

[NikunjThakkar]

Thanks for this money maker, but i was looking for an 'Excel' solution, if possible. May be using NPVs and FVs to back-calculate. I don't have money to be spent on this!

I know the way is to use IRR as the rate and come to the FV using the NPV. Or something like that. I just cant come up with the formulae for this.

[MoneyMaker]

Thanks for this money maker, but i was looking for an 'Excel' solution, if possible. May be using NPVs and FVs to back-calculate. I don't have money to be spent on this!

I know the way is to use IRR as the rate and come to the FV using the NPV. Or something like that. I just cant come up with the formulae for this.

As you wish my friend, if you like an Excel solution then at first find the net present value of non periodic cash flows using the XNPV function with the IRR as the discount rate and a value of 0 for money amount for the cash flow that you are trying to find

Next step is to find the FVIF or future value of $1 for the time period of that particular cash flow that is missing

The last step will be to multiply the results of XNPV (negation) and the FVIF which will give you the exit value of investment or for that matter any other missing cash flow

See the table below

-500	10	11	10	0
12/31/2011	12/31/2012	12/31/2013	12/31/2014	12/31/2015
-500	10	11	10	600

IRR	6.15204%	NPV	=-XNPV(B5,A1:E1,A2:E2)	FVIF	=FV(B5,(E2-A2)/365,0,-1)	Missing CF	=D5*F5
IRR	6.15204%	NPV	472.46	FVIF	1.26994	Missing CF	600
IRR	10%	NPV	=-XNPV(B6,A1:E1,A2:E2)	FVIF	=FV(B6,(E2-A2)/365,0,-1)	Missing CF	=D6*F6
IRR	10%	NPV	474.31	FVIF	1.46448	Missing CF	694.62

Please also download the attached worksheet showing all the formulas

Regards

[MoneyMaker]

You can eliminate the last step of multiplication if providing the FV function with XNPV value as follows

IRR	6.15204%	NPV	=-XNPV(B5,A1:E1,A2:E2)	FV	=FV(B5,(E2-A2)/365,0,-D5)
IRR	6.15204%	NPV	472.46	FV	600
IRR	10%	NPV	=-XNPV(B6,A1:E1,A2:E2)	FV	=FV(B6,(E2-A2)/365,0,-D6)
IRR	10%	NPV	474.31	FV	694.62

[NikunjThakkar]

This is exactly what i was struggling with. My concept clarity is also to blame for this.

Thanks a ton MoneyMaker, this is extremely helpful.

[MoneyMaker]

This is exactly what i was struggling with. My concept clarity is also to blame for this.

Thanks a ton MoneyMaker, this is extremely helpful.

No problem mate

Glad that it worked out

[driverguy]

This is great, but at the moment my IRR is negative, so it's returning an error...How do I get around that?

[joeu2004]

[.... deleted by me ....]

[joeu2004]

This is great, but at the moment my IRR is negative, so it's returning an error...How do I get around that?

I presume you mean that XNPV returns an error when IRR is negative. One of my pet peeves. ;-)

Replace XNPV(B5,A1:E1,A2:E2) with SUMPRODUCT(A1:E1 / (1+B5)^((A2:E2-A2)/365)).