Solving for Future Value Given XIRR Target: .GoaSeek VBA; perhaps =LOG()?

[RSTDS]

Hi, clever people.

I am building a financial table to find the payoff value for a financial instrument that solves for the cash flow needed in each future period, on a discrete basis, such that an XIRR target is satisfied. Basically, I want to know what the dollar hurdle is to fully pay-off the instrument in each future period when considering all previous payments and investments. Some considerations that make this a challenge:

• Variable cash flows (both in and out) (see Row 3)
• Variable timing of cash flows (can be at any time during future months) (see Row 4)
• Solving for data in one row with many columns (in the case of my data, 64 columns) (see Row 7)
• Columns are monthly
• A simple .GoalSeek in a VBA script works, except that I'll need to build a full 64x64 matrix by hand in order to isolate for each of the 64 .GoalSeek actions (seems calc. heavy/inelegant)
• Existing functions (IRR, PMT, etc.) won't work since cash flows and timing/periods are variable
• XIRR doesn't allow ranges to be non-contiguous (so you can't add a comma to the range, and things like OFFSET won't work either).

Soooo... I am thinking one of three things:

1. Use a .GoalSeek VBA script across the columns, but add script such that the resulting value in each discrete period is cut-and-pasted to another location before moving the .GoalSeek action to the next column. Without the cut/paste, .GoalSeek will stop at the first column and/or return an error. .GS results cannot be cumulative. What does this script look like?
2. Invert the XIRR function using =LOG(). Am I in error on this, or would one be able to write a log function that solves for the unknown variable?
3. I'm overcomplicating this problem and the solution is far more straightforward.

Thoughts? If so, I'd appreciate guidance on any of 1-3, above. Sample table provided below so you can better see what I'm trying to do.

Thanks so much for your help!

A1	B	C	D	E	F		BN
2	Component	Row Contents	Jan. 2017	Feb. 2017	Mar. 2017	//	Apr. 2022
3	Investment	[Variable]	$(75,000)	$ -	$(5,000)	//	$ -
4	Payments Rec'd	[Variable]	-	14,195	922	//	1,905
5	Net for Period	=SUM(D3:D4)	(75,000)	14,195	(4,788)	//	1,905
6	IRR Target	[Variable]	15%			//
7	Implied Cash Flow Needed in This Period to Hit IRR Target	[Variable] Solve for This -->	-	-	-	//	-
8	Total Implied CF for Period	=D5+D7	$ -	$ -	$ -	//	$ -
9	XIRR Return	=XIRR($D$8:D$8,$D$2:D$2) (carried forward to each period)	- %	- %	- %	//	- %
10	Solved CF Formula?	=LOG(??)

<tbody>
</tbody>

 

[Scott R]

Do you need the implied CF for each period (why have a row 9?), or are you just trying to come up a terminal payment value that hits your goal IRR over all periods?

You could put a payoff amount in BO4, an XIRR formula anywhere XIRR(D6/12,D5:BO5), and then Goal Seek on those two cells.

 

[RSTDS]

Yes, I need an implied CF for each period, as this will be for a balance sheet forecast. It's essentially an accrual, net of payments, where the accrued amounts for P&I are determined by solving for this XIRR target. For further detail, the payment stream is calculated as a variable percentage of revenue. Since we'll be doing a recap at some point that is hopefully before maturity, it'll be good to know what our $ bogey is at each point in time.

Your suggestion of a single period payoff amount in BO4 (or, any period) is a cinch, but I would like to forecast this over a series of 64 months.

I could also do this where I manually calculate the discounted values in these future periods, but I again run into the need to build the 64x64 matrix. (I also can't seem to match results from manual calculation of PV (i.e. just math, not the Excel formula) with the XIRR output; the former is always much larger than the latter). I feel like there's a way to do this programmatically (including the potential algebraic solution).

 

[joeu2004]

.
Does this do want you intended?

I read a lot between the lines of your specifications and made a lot of assumptions. Be sure to read the explanation that follows the table.

	B	D	E	F	G	H	I		BJ	BK	BL	BM	BN	BO
2		Jan-17	Feb-17	Mar-17	Apr-17	May-17	Jun-17		Nov-21	Dec-21	Jan-22	Feb-22	Mar-22	Apr-22
3	Investment	-75,000	0	-5,000	0	0	-36,246		0	-36,921	-22,025	-65,121	-57,205	0
4	Payment		14,195	922	37,388	14,740	38,642		19,952	9,070	40,849	72,045	59,877	1,905
5	Net CF	-75,000	-60,805	-64,883	-27,495	-12,755	-10,359		-2,474	-30,325	-11,501	-4,577	-1,905	0
6	Target IRR	15.00%
7	Addl CF		136,701	203,057	232,977	248,423	261,749		2,904,122	2,967,999	3,014,941	3,055,519	3,090,360	3,127,262
8	Implied CF		75,896	138,174	205,482	235,668	251,390		2,901,648	2,937,674	3,003,440	3,050,942	3,088,455	3,127,262
9	IRR		15.00%	15.00%	15.00%	15.00%	15.00%		15.00%	15.00%	15.00%	15.00%	15.00%	15.00%

<tbody>
</tbody>

Formulas:
D2: 1/1/2016
E2: =DATE(D2,1)
    copy E2 into F2:BO2
D5: =SUM($D$3:D4)
    copy D5 into E5:BO5
E7: =(-SUMPRODUCT($D$5:D5/(1+$D$6)^(($D$2:D2-$D$2)/365))
      - E5/(1+$D$6)^((E2-$D$2)/365)) * (1+$D$6)^((E2-$D$2)/365)
E8: =E5+E7
E9: { =XIRR(IF(COLUMN($D$5:E5)=COLUMN(E5),E8,$D$5:E5), $D$2:E2) }
    copy E7:E9 into F7:F9 through BO7:BO9

Formulas to generate random data:
G3: =IF(AND(RAND()<2/3, COUNTIF(E3:F3,0)<2), 0, RANDBETWEEN(-75000,-5000))
    copy G3 into E3:BN3
G4: =IF(AND(RAND()<1/3, COUNTIF(E4:F4,0)<2), 0,
        RANDBETWEEN(MAX(0,MIN(922,-SUM($D$3:G3,$D$4:F4,$BO$4))),
                    MAX(0,-SUM($D$3:G3,$D$4:F4,$BO$4))))
    copy G4 into E4:BM4
BN4: =-SUM(D3:BN3,D4:BM4,BO4)

As in E9, formulas displayed with curly brackets {...} are array-entered, to wit: type the formulas without the curly brackets, then press ctrl+shift+Enter instead of just Enter. Excel displays the curly brackets in the Formula Bar to indicate that the formula is array-entered.

You can also download the Excel file "cfs for const irr.xls" (click here) [2]. Ignore the preview (unreadable!).

Note that values displayed in rows 7 through 9 are rounded. For example, E7 is about 136700.569201246; E8 is about 75895.5692012464; and E9 is about 15.0000005960465%.

-----

The formulas in G3:BM4 and BN4 are not directly related to the problem you want to solve. They generate random test data. I explain them in footnote [1], in case you would like to make use of something like them for your own purposes.

-----

The key formulas are in E7 and E9.

The formula in E7 calculates the additional cash flow that is added to the last net cash flow (row 5) in the same column to calculate the "implied last cash flow" in E8 such that, when combined with the previous net cash flows in row 5, derives the (X)IRR in E9 that matches the required IRR in D6.

If you want to allow for variable target IRRs in row 6, copy D6 into E6:BO6, and replace $D$6 with D6 in E7.

The formula in E7 is derived from the mathematical definition of the IRR, namely the rate "r" such that the NPV calculated as follows is zero:

0 = Sigma(CF/(1+r)^((d-d[0])/365), i=0,...,n)

In your case, CF[n] = E5+E7. So the formula becomes:

0 = Sigma(CF/(1+r)^((d-d[0])/365), i=0,...,n-1) + (E5+E7)/(1+r)^((d[n]-d[0])/365)

-Sigma(CF/(1+r)^((d-d[0])/365), i=0,...,n-1) - E5/(1+r)^((d[n]-d[0])/365) = E7/(1+r)^((d[n]-d[0])/365)

(-Sigma(CF/(1+r)^((d-d[0])/365), i=0,...,n-1) - E5/(1+r)^((d[n]-d[0])/365)) * (1+r)^((d[n]-d[0])/365) = E7

Compare the last mathematical formula with the Excel formula in E7.

-----

The formula in E9 calculates the (X)IRR using the previous actual net cash flows (row 5) and the calculated implied last cash flow in E8.

The array-entered formula demonstrates how to put non-contiguous cells together into one array; for example, D5, E5 (actual net cash flows), and F8 (implied last net cash flow).



-----

[1] The formulas in G3 and G4 generate random data representing additional "variable cash flows" (investments and payments).

The parameters of the formulas are arbitrary. They are derived from the limited data included in the original posting.

In particular, I assume that periodic investments are between -75,000 and -5,000, if they occur at all. Arbitrarily, I assume that investments occur 1/3 of the time, and at least every third period.

Also, I assume that periodic payments, if they occur at all, are between 922 and the remaining investment (sum of the investments minus previous payments), ensuring that 1905 is left to be paid in the last period (BO4). Arbitrarily, I assume that payments occur 2/3 of the time, and at least every third period.

The second-to-last payment (BN4) retires enough of the remaining investment to ensure that the last payment is 1905, per the example in the original posting.

-----

[2] https://app.box.com/s/x4e7e2lg01auea2u4onrvxwwchfd2xu3

 

[joeu2004]

.
Be sure to see my previous response, posting #4.

I also can't seem to match results from manual calculation of PV (i.e. just math, not the Excel formula) with the XIRR output; the former is always much larger than the latter

If you would like an explanation, post a concrete example: the XIRR and PV formulas that you use, together with the values of their parameters.

Show values with 15 significant digits. One way to do that is to highlight each parameter in the Formula Bar. When done, be sure to press Esc to retain the original formula.

 

[joeu2004]

.
Improvement and errata, too late to edit....

E7: =(-SUMPRODUCT($D$5:D5/(1+$D$6)^(($D$2:D2-$D$2)/365))
      - E5/(1+$D$6)^((E2-$D$2)/365)) * (1+$D$6)^((E2-$D$2)/365)

That could be simplified, to wit:

=-SUMPRODUCT($D$5:D5/(1+$D$6)^(($D$2:D2-$D$2)/365))*(1+$D$6)^((E2-$D$2)/365) - E5

If you want to allow for variable target IRRs in row 6, copy D6 into E6:BO6, and replace $D$6 with D6 in E7.

Replace $D$6 with E6.

 

[RSTDS]

This works. Truly outstanding - thank you so much for such a detailed and thourough response. I wasn't expecting to have the code fully written for me, but then, I'd do the same if the Q&A was reversed.

I'll dig in a little more later, but I understand the math on this, and I can identify a few areas where I erred:
- SUMPRODUCT is a better formula for calculating the zero-PV values of the variable historical streams
- Non-contiguous ranges in XIRR do work. I did try various iterations of array formulae with XIRR and non-contiguous ranges, but it never seemed to work

Thanks again, joeu2004.

 

[joeu2004]

E2: =DATE(D2,1)

Thanks again

You're welcome! Just noticed another typo in the posting that obviously is not in the Excel file: DATE should be EDATE.

Usually, I copy-and-paste from the Formula Bar in order to avoid such mistakes. Obviously, I didn't in this case. Mea culpa!

 

[joeu2004]

D5: =SUM($D$3:D4)
copy D5 into E5:BO5

This works.

Not really!

I just noticed a typo that leads to a huge error in the calculations. Sigh, I never did a sanity check of the numbers. And the math lends itself to garbage-in/garbage-out (GIGO).

The formula in D5 should be =SUM(D3:D4), with a relative reference as you wrote originally.

All of the other formulas remain unchanged. (Hopefully! )

But that "minor" change makes a huge difference in the magnitude of the "implicit last CF" derived in rows 7 and 8.

Using the same numbers as in my posting #4, the corrected table is (I also updated the uploaded example Excel file):

	B	C	D	E	F	G	H	I	//	BJ	BK	BL	BM	BN	BO
2			Jan-17	Feb-17	Mar-17	Apr-17	May-17	Jun-17	//	Nov-21	Dec-21	Jan-22	Feb-22	Mar-22	Apr-22
3	Investment		-75,000	0	-5,000	0	0	-36,246	//	0	-36,921	-22,025	-65,121	-57,205	0
4	Payment			14,195	922	37,388	14,740	38,642	//	19,952	9,070	40,849	72,045	59,877	1,905
5	Net		-75,000	14,195	-4,078	37,388	14,740	2,396	//	19,952	-27,851	18,824	6,924	2,672	1,905
6	Target IRR		15.00%						//
7	Addl CF			61,701	66,444	29,849	15,454	13,242	//	36,021	64,288	46,232	39,860	37,618	36,162
8	Implied CF			75,896	62,366	67,237	30,194	15,638	//	55,973	36,437	65,056	46,784	40,290	38,067
9	IRR			15.00%	15.00%	15.00%	15.00%	15.00%	//	15.00%	15.00%	15.00%	15.00%	15.00%	15.00%

<tbody>
</tbody>

As proof, consider the following amortization table (worksheet "proof" in the uploaded Excel file):

	B	C	D	E	F	G	H	I	//	BJ	BK	BL	BM	BN	BO
2			Jan-17	Feb-17	Mar-17	Apr-17	May-17	Jun-17	//	Nov-21	Dec-21	Jan-22	Feb-22	Mar-22	Apr-22
3	Investment		-75,000	0	-5,000	0	0	-36,246	//	0	-36,921	-22,025	-65,121	-57,205	0
4	Payment			14,195	922	37,388	14,740	38,642	//	19,952	9,070	40,849	72,045	59,877	1,905
5	Net		-75,000	14,195	-4,078	37,388	14,740	2,396	//	19,952	-27,851	18,824	6,924	2,672	38,067
6	Impld IRR		15.00%						//
7	Implied Int			-896	-665	-793	-345	-185	//	-660	-416	-768	-552	-430	-449
8	Implied Bal		-75,000	-61,701	-66,444	-29,849	-15,454	-13,242	//	-36,021	-64,288	-46,232	-39,860	-37,618	0

<tbody>
</tbody>

Formulas:
D5: =SUM(D3:D4)
    copy D5 into E5:BO5
D8: =D5
E2: =EDATE(D2,1)
    copy E2 into F2:BO2
E7: =D8*(1+$D$6)^((E2-D2)/365) - D8
E8: =D8+E5+E7
    copy E7:E8 into F7:F8 through BO7:BO8

The implied last cash flow in BO5 is the value pasted from BO8 of the previous table.

Ostensibly, the implied IRR in D6 is the value derived by XIRR in BO9 of the previous, which is about 15.0000005960465%.

However, with that rate, BO8 is actually about -0.00188942959357519. Although that is "good enough for government work" , I was curious why the arithmetic error is so large.

The answer is: it is due to the estimation error that is inherent in the Excel XIRR algorithm. If we enter the actual target IRR (15%), BO8 is about -2.18E-10, which is the magnitude of the arithmetic error that I would expect due to the internal binary representation.