Backing in to an XIRR calc

[Spennyd76]

Let's say I have the following inputs:

	A​	B​
1​	2/22/2017​	($363,000.00)​
2​	8/7/2017​	(43,372.12)​
3​	2/11/2019​	(10,000.00)​
4​	1/6/2021​	(14,000.00)​
5​	2/28/2021​	$577,499.46​
NET IRR​	8.00% [=xirr(B1:B5,A1:A5)]​

​

The XIRR formula above returns 8%. I had to manually guess at the value to plug in to B5 in order to achieve an 8% return. It's a manual guessing game every month. Is there any way to reverse the XIRR calculation, as it were, to tell me what the figure must be on 2/28/21 in order to result in an 8% return? In other words, I need to forecast what the future value must be in order to achieve a certain XIRR.

I hope that makes sense.

 

[Spennyd76]

Sorry...the table adjusted after I posted it. the last row should be shifted over appropriately. Sorry for any confusion.

 

[joeu2004]

Book1
	A	B	C
1	2/22/2017	-$363,000.00
2	8/7/2017	-$43,372.12
3	2/11/2019	-$10,000.00
4	1/6/2021	-$14,000.00
5	2/28/2021	$577,514.465065212	using XNPV
6		$577,514.465065212	using SUMPRODUCT
7
8
9		actual	goal
10	IRR	7.99999982118607%	8.00%
11		7.99926906824112%	using B5=577,499.46
Sheet1

Formulas:
B5: =-XNPV(C10, B1:B4, A1:A4) * (1+C10)^((A5-A1)/365)
B6: =-SUMPRODUCT(B1:B4 / (1+C10)^((A1:A4-A1)/365)) * (1+C10)^((A5-A1)/365)
B10: =XIRR(B1:B5, A1:A5)

B11 is the XIRR result when we substitute your estimate in B5. Note that it is not quite as good as B10, although they both round to 8.00%.

We can use XNPV in B5 when we expect a positive IRR (C10). But sadly, XNPV does not allow negative discount rates, for no good reason. (A defect, IMHO.)

More generally, we can use a SUMPRODUCT expression, which is demonstrated in B6.

The formulas in B5 and B6 are derived from the mathematical formulas for the (X)NPV in the XIRR help page.

 

[Spennyd76]

This worked. Thank you!