IRR using Offset from different data colom/row

[hollowform]

hello everyone, i need u help

I tried to count IRR from my data with offset function but the problem is the periode-0 data is at another column

Income Periode Periode-0
5,496,544 1 -50,000,000
5,496,544 2
5,496,544 3
5,496,544 4
5,496,544 5
5,496,544 6
5,496,544 7
5,496,544 8
5,496,544 9
5,496,544 10

i have thousand of data like this so i cant cut periode-0 data manually, any suggestion will help, thankyou

 

[joeu2004]

It is unclear why you would use the OFFSET function. Does the following solve your problem?

Book1
	A	B	C	D	E
1	Pmt	Period	Init CF	IRR	NPV
2	5,496,544	1	-50,000,000	1.75959914386279%	-2.29E-08
3	5,496,544	2
4	5,496,544	3
5	5,496,544	4
6	5,496,544	5
7	5,496,544	6
8	5,496,544	7
9	5,496,544	8
10	5,496,544	9
11	5,496,544	10
Sheet1

Formulas:
D2: =IRR( (C2, A2:A11) )
E2: =NPV(D2, C2, A2:A11)

Excel IRR is one of the few functions that allow the "range union" form: (range [,range]...) .

The NPV in E2 is provided as a check. Note that it is not uncommon for NPV to be an infinitesimal amount, not exactly zero. That is because the IRR is estimated by iterations, and Excel IRR only iterates until some internal limits are reached. See the details in the IRR help page .