XIRR formula for several Loans

[xunda_gunda]

Hello,
I am trying to write one XIRR formula that will calculate IRR for several loans (with different start dates).

Loans/Dates	15.01.2014	15.02.2014	15.03.2014	15.04.2014	15.05.2014	15.06.2014	15.07.2014	15.08.2014	15.09.2014	15.10.2014	15.11.2014	15.12.2014	XIRR Correct	XIRR Incorrect	XIRR Correct Formula	XIRR Incorrect Formula
Loan 1			-50,000				25,000					30,000	18.58%	0.00%	=XIRR(D2:M2,D1:M1)	=XIRR(B2:M2,$B$1:$M$1)
Loan 2	-150,000		50,000				80,000					40,000	29.32%	29.32%	=XIRR(B3:M3,B1:M1)	=XIRR(B3:M3,$B$1:$M$1)
Loan 3					-90,000					20,000		80,000	21.01%	0.00%	=XIRR(F4:M4,F1:M1)	=XIRR(B4:M4,$B$1:$M$1)
Loan 4					-18,000					8,000		11,000	11.06%	0.00%	=XIRR(F5:M5,F1:M1)	=XIRR(B5:M5,$B$1:$M$1)
Loan 5							-15,000			10,000		7,000	47.95%	0.00%	=XIRR(H6:M6,H1:M1)	=XIRR(B6:M6,$B$1:$M$1)

<tbody>
</tbody>

XIRR Correct formula is calculated separately for each loan (first date should be the date when first cash flow (negative) is done)

XIRR Incorrect formula is what I want to use (one formula that will work in every loan).

But problem is - in this case first date for each loan would be 15.01.2014 and IRR will be calculated incorrectly.
I want that for each loan dates range should start from the first cash flow date.

Am I clear? If not, please tell me and I will try to explain more accurately.

Thanks in advance.

 

[xunda_gunda]

Please help

 

[XLearner]

Hi

Assuming the following spreadsheet setup:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Loans/Dates	15/1/14	15/2/14	15/3/14	15/4/14	15/5/14	15/6/14	15/7/14	15/8/14	15/9/14	15/10/14	15/11/14	15/12/14	XIRR
2	Loan 1			-50000<strike></strike>				25000					30000	18.58%
3	Loan 2	-150000<strike></strike>		50000				80000					40000	29.32%
4	Loan 3					-90000<strike></strike>					20000		80000	21.01%
5	Loan 4					-18000					8000		11000	11.06%
6	Loan 5							-15000			10000		7000	47,95%

<tbody>
</tbody>

in N2:

=XIRR(INDIRECT(ADDRESS(ROW(),MATCH(TRUE,ISNUMBER($B2:$M2),0)+1,4)&":M"&ROW()),
INDIRECT(ADDRESS(1,MATCH(TRUE,ISNUMBER($B2:$M2),0)+1,4)&":M1"))

validate with Ctrl+Shift+Enter (array formula) and copy down as necessary

Regards
XLearner

 

[xunda_gunda]

Hi

Assuming the following spreadsheet setup:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Loans/Dates	15/1/14	15/2/14	15/3/14	15/4/14	15/5/14	15/6/14	15/7/14	15/8/14	15/9/14	15/10/14	15/11/14	15/12/14	XIRR
2	Loan 1			-50000<strike></strike>				25000					30000	18.58%
3	Loan 2	-150000<strike></strike>		50000				80000					40000	29.32%
4	Loan 3					-90000<strike></strike>					20000		80000	21.01%
5	Loan 4					-18000					8000		11000	11.06%
6	Loan 5							-15000			10000		7000	47,95%

<tbody>
</tbody>

in N2:

=XIRR(INDIRECT(ADDRESS(ROW(),MATCH(TRUE,ISNUMBER($B2:$M2),0)+1,4)&":M"&ROW()),
INDIRECT(ADDRESS(1,MATCH(TRUE,ISNUMBER($B2:$M2),0)+1,4)&":M1"))

validate with Ctrl+Shift+Enter (array formula) and copy down as necessary

Regards
XLearner

Thank you very very very much

 

[XLearner]

You're welcome & Thanks for posting back. Hopefully this will help someone else sooner or later...