A formula to dynamically calculate the required terminal value to achieve a specific IRR

[VIG]

For example, let's say there are these annual cash flows.

-21, 0 , 0, 3, 14, X

What is the formula that will allow me to calculate what X needs to be for an IRR that I can set (and sensitize), for example 10%

Thank you

 

[VIG]

It needs to be dynamic, so goal seek isn't a solution

 

[DRSteele]

As you may know, the IRR is the discount rate that sets NPV to zero. The problem is that NPV(r)=0 is a continuous function and therefore cannot be solved algebraically (or 'formulaically' in Excel). Initial guesses must be made and then refined using a method like the secant method - which is how Excel's functions IRR, MIRR and XIRR get it done.

 

[VIG]

As you may know, the IRR is the discount rate that sets NPV to zero. The problem is that NPV(r)=0 is a continuous function and therefore cannot be solved algebraically (or 'formulaically' in Excel). Initial guesses must be made and then refined using a method like the secant method - which is how Excel's functions IRR, MIRR and XIRR get it done.

i found the solution - =-SUMPRODUCT((1+P19)^((MAX($Q$11:$U$11)-$Q$11:$U$11)/365)*Q19:U19)

where P is the IRR hurdle
row 11 is the dates
row 19 is the cashflows

 

[DRSteele]

Please provide a mock-up to show us how it works.

 

[joeu2004]

-SUMPRODUCT((1+P19)^((MAX($Q$11:$U$11)-$Q$11:$U$11)/365)*Q19:U19)

First, that is incorrect. Confirm with =XIRR(P19, Q19:V19, Q11:V11), assuming your formula is in V19.

The correct formula of that formula is (in V19):

=-SUMPRODUCT(Q19:U19 / (1+P19)^(($Q$11:$U$11 - $Q$11) / 365)) * (1+P19)^(($V$11-$Q$11)/365)

Second, as noted, that formula emulates XIRR, not Excel IRR. Your original posting said nothing about dates. And XIRR might introduce "too much" precision, since it relies on exact differences between dates.

If you want a value in V19 that works with Excel IRR, try either of the following in V19:

=-SUMPRODUCT(Q19:U19 / (1+P19)^(COLUMN(Q19:U19) - COLUMN(Q19))) * (1+P19)^COLUMNS(Q19:U19)
or
=-NPV(P19, Q19:U19) * (1+P19)^COLUMNS(Q19:V19)

Of course, the latter is simpler.

 

[joeu2004]

(Sorry for the incessant postings. I was interrupted and posted an incomplete response.)

-SUMPRODUCT((1+P19)^((MAX($Q$11:$U$11)-$Q$11:$U$11)/365)*Q19:U19)

[....]
=-SUMPRODUCT(Q19:U19 / (1+P19)^(($Q$11:$U$11 - $Q$11) / 365)) * (1+P19)^(($V$11-$Q$11)/365)
[....]
=-SUMPRODUCT(Q19:U19 / (1+P19)^(COLUMN(Q19:U19) - COLUMN(Q19))) * (1+P19)^COLUMNS(Q19:U19)
or
=-NPV(P19, Q19:U19) * (1+P19)^COLUMNS(Q19:V19)

I presume the original design is something like the following:

last cf for irr.xlsx
	P	Q	R	S	T	U	V	W	X	Y	Z
11	Goal IRR	1/1/2020	1/1/2021	1/1/2022	1/1/2023	1/1/2024	1/1/2025	IRR/XIRR	W=P?	W-P
19	10.00%	-21	0	0	3	14	13.454130	8.97%	FALSE	-1.03%	wrong (original)
20	10.00%	-21	0	0	3	14	14.803408	10.00%	FALSE	-5.96E-09	okay (corrected)
21	10.00%	-21	0	0	3	14	14.790710	10.00%	TRUE	0.00E+00	okay (alternative 1)
22	10.00%	-21	0	0	3	14	14.790710	10.00%	TRUE	0.00E+00	okay (alternative 2)
Sheet1

Click on each cell to see the formulas.

The infinitesimal difference in Y20 (-5.96E-09) is to be expected. In fact, the exact results in W21 and W22 are coincidence.

Both Excel XIRR and Excel IRR use an iterative algorithm to estimate the IRR. The algorithms have internal tolerances that (usually) impact that accuracy of the result.


-----
PS.... The form of the original formula would have worked with the following corrections and with the same results as my formula in row 20:

=-SUMPRODUCT((1+P24)^((MIN($Q$11:$U$11)-$Q$11:$U$11)/365), Q24:U24) * (1+P24)^(($V$11-$Q$11)/365)
or
=-SUMPRODUCT((1+P24)^(($Q$11-$Q$11:$U$11)/365), Q24:U24) * (1+P24)^(($V$11-$Q$11)/365)

Note that Excel XIRR assumes that the first date/value pair in the series is the earliest date. So using MIN is unnecessary. (Although I would agree that it can be an improvement in some cases.)