XIRR but values are from two different cell ranges

[EthanP]

In my IRR function I wish to include, not just cash flows, equity after loan payoff because that is a cash positive event. For my IRR in cell F80 =XIRR($E$77:E77,$E$3:E3,)... how do I include the equity after loan payoff in the values? What is the syntax? For example, I've tried =XIRR($E$77:E77+F84),$E$3:E3,) or =XIRR(sum($E$77:E77,F84),$E$3:E3,). What do I need to do to include that one extra cell in the "values" of the XIRR?

Experiment Valuator REV B.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
76				Investment		($165,000)
77				Cash Flow	($165,000)	$1,970	$1,983	$1,996	$2,010	$2,023	$2,036	$2,050	$2,063	$2,077	$2,090	$2,104	$2,117
78				Cash on Cash Return		1.19%	2.40%	3.61%	4.82%	6.05%	7.28%	8.53%	9.78%	11.04%	12.30%	13.58%	14.86%
79				Cash Flows plus up front capital (line is needed for IRR)		($165,000)
80				IRR		#N/A	#NUM!	-100.00%	-100.00%	-99.99%	-99.95%	-99.81%	-99.47%	-98.85%	-97.89%	-96.58%	-94.91%
81				Desired Cap Rate (when going to sell)		7.0%	7.0%	7.0%	7.0%	7.0%	7.0%	7.0%	7.0%	7.0%	7.0%	7.0%	7.0%
82				Sale Price		$66,110	$132,408	$198,896	$265,574	$332,442	$399,501	$466,751	$534,193	$601,828	$669,655	$737,676	$805,891
83				Debt left on loan	NOTE: if debt is wrong its because of control shift enter	($411,905)	($411,307)	($410,705)	($410,101)	($409,494)	($408,884)	($408,270)	($407,654)	($407,034)	($406,412)	($405,786)	($405,157)
84				Equity after loan payoff		($345,795)	($278,898)	($211,809)	($144,528)	($77,052)	($9,383)	$58,481	$126,539	$194,793	$263,243	$331,890	$400,734
Monthly Cash Flow

Cell Formulas
Range	Formula
E77,F76	E77	='Projections '!$C$76
F77:Q77	G77	=G72+G74
F78:Q78	G78	=ABS(SUM($F$77:G77)/$F$76)
F79	F79	='Projections '!C79
F80:Q80	F80	=XIRR($E$77:E77,$E$3:E3,)
G81:Q81	G81	=F81
F82	F82	=F72/F81
G82:Q82	G82	=SUM($F$72:G72)/G81
F83:Q83	F83	=-TRANSPOSE('Debt Service Lender 1'!K2:K688)
F84:Q84	F84	=SUM(F82:F83)
Press CTRL+SHIFT+ENTER to enter array formulas.

Experiment Valuator REV B.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1				Month	Acquisition	1	2	3	4	5	6	7	8	9	10	11	12
2				Year	Acquisition	1	1	1	1	1	1	1	1	1	1	1	1
3	Check			Month Ending	1/5/2023	2/28/2023	3/31/2023	4/30/2023	5/31/2023	6/30/2023	7/31/2023	8/31/2023	9/30/2023	10/31/2023	11/30/2023	12/31/2023	1/31/2024
Monthly Cash Flow

Cell Formulas
Range	Formula
G1:Q1	G1	=F1+1
F2:Q2	F2	=ROUNDUP(F1/12,0)
E3	E3	='Summary Sheet'!B7
F3:Q3	F3	=EOMONTH(E3,1)

 

[KRice]

I don't completely understand what the table is doing, but it appears that you want to calculate a dynamic IRR...one that changes each month depending on cash flow. Since IRR is based on cash "flow", I would think that your Equity values cannot be used because they do not represent cash flow. You could argue that the difference in equity represents a "flow", so I've added a row (see the pink cell) showing the change in equity. Caveat: I'm not qualified to comment on the validity of using the change in equity in this calculation. From month to month, the cash flows on row 77 are added to the change in equity on row 85, and that cash flow profile is considered against the dates in row 3 to compute the IRR. Check these numbers carefully to decide if this makes sense.

As a side note, your post includes references to worksheets and rows that are not available, so anyone attempting to use the mini-sheet will encounter a lot of errors. I've extended the worksheet from an earlier post and inserted values for the missing references (see blue cells) so that the formulas produce results.

MrExcel_20220912.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Start Date	1/5/2023		Month		1	2	3	4	5	6	7	8	9	10	11	12		Rate of Annual Increase	3%
2				Year		1	1	1	1	1	1	1	1	1	1	1	1		Monthly factor	1.00246627
3				Month Ending	1/5/2023	2/28/2023	3/31/2023	4/30/2023	5/31/2023	6/30/2023	7/31/2023	8/31/2023	9/30/2023	10/31/2023	11/30/2023	12/31/2023	1/31/2024		Number intervals	11
4	Check	Inputs: Yearly Total	Sum of monthly amts	Income		Calculate starting amt													Sum of Finite Geometric Series	11.16411941
70				Projections '!$C$76		$ (165,000)
71				Projections '!$C$79		$ (165,000)
72				Entries on row 72? (inferred from others & hardwired)		$ 4,628	$ 4,641	$ 4,654	$ 4,667	$ 4,681	$ 4,694	$ 4,708	$ 4,721	$ 4,734	$ 4,748	$ 4,761	$ 4,775		Debt Service Lender 1'!K2:K688
73																			$ 411,905
74				Entries on row 74? (inferred from others & hardwired)		$ (2,658)	$ (7,286)	$ (11,927)	$ (16,580)	$ (21,248)	$ (25,929)	$ (30,623)	$ (35,331)	$ (40,051)	$ (44,786)	$ (49,533)	$ (54,295)		$ 411,307
75																			$ 410,705
76				Investment		$ (165,000)													$ 410,101
77				Cash Flow	$ (165,000)	$ 1,970	$ (2,645)	$ (7,273)	$ (11,913)	$ (16,567)	$ (21,235)	$ (25,915)	$ (30,610)	$ (35,317)	$ (40,038)	$ (44,772)	$ (49,520)		$ 409,494
78				Cash on Cash Return		1.19%	0.41%	4.82%	12.04%	22.08%	34.95%	50.65%	69.20%	90.61%	114.87%	142.01%	172.02%		$ 408,884
79				Cash Flows plus up front capital (line is needed for IRR)		$ (165,000)													$ 408,270
80				IRR		#NUM!	0.0%	0.0%	-99.3%	-95.5%	-87.3%	-75.6%	-62.4%	-49.3%	-37.4%	-27.3%	-19.1%		$ 407,654
81				Desired Cap Rate (when going to sell)		7.0%	7.0%	7.0%	7.0%	7.0%	7.0%	7.0%	7.0%	7.0%	7.0%	7.0%	7.0%		$ 407,034
82				Sale Price		$ 66,110	$ 132,408	$ 198,896	$ 265,574	$ 332,442	$ 399,501	$ 466,751	$ 534,193	$ 601,828	$ 669,655	$ 737,676	$ 805,891		$ 406,412
83				Debt left on loan		$ (411,905)	$ (411,307)	$ (410,705)	$ (410,101)	$ (409,494)	$ (408,884)	$ (408,270)	$ (407,654)	$ (407,034)	$ (406,412)	$ (405,786)	$ (405,157)		$ 405,786
84				Equity after loan payoff		$ (345,795)	$ (278,899)	$ (211,809)	$ (144,527)	$ (77,052)	$ (9,383)	$ 58,481	$ 126,539	$ 194,794	$ 263,243	$ 331,890	$ 400,734		$ 405,157
85				Change in Equity		$ (345,795)	$ 66,896	$ 67,090	$ 67,282	$ 67,475	$ 67,669	$ 67,864	$ 68,058	$ 68,255	$ 68,449	$ 68,647	$ 68,844
Sheet2 (2)

Cell Formulas
Range	Formula
F1:Q1	F1	=COLUMNS($F:F)
F2:Q2	F2	=ROUNDUP(F1/12,0)
E3	E3	=B1
F3:Q3	F3	=EOMONTH($E3,F1)
T2	T2	=(1+$T$1)^(1/12)
T3	T3	=COUNT(F3:Q3)-1
T4	T4	=T2*(1-T2^T3)/(1-T2)
E77	E77	=F70
F77:Q77	G77	=G72+G74
F78:Q78	G78	=ABS(SUM($F$77:G77)/$F$76)
F76	F76	=F70
F79	F79	=F71
F80:Q80	F80	=XIRR($E77:F77+$E85:F85,$E3:F3,)
G81:Q81	G81	=F81
F82:Q82	G82	=SUM($F$72:G72)/G81
F83:Q83	F83	=-TRANSPOSE(S73:S84)
F84:Q84	F84	=SUM(F82:F83)
F85:Q85	F85	=F84-E84
Dynamic array formulas.

 

[EthanP]

Is there a way I can send you the whole work book? So you can have the whole picture.