Giving Cell Reference as an array in IRR formula

[vidyanand]

Hello Friends,

I want to calculate the IRR of an Insurance policy where you pay the premium in the initial years & then wait for some years & then you will start getting regular cash flow for a certain number of years with your initial premiums also returned back to you with last cash flow. So for example

1 Premium Payment Years - 10
2 Premium - 100000
3 Waiting Period - 2
4 Income Years - 25
5 Income - 115000

So how do you put it in one formula where last cash flow in this example would be 100000 * 10 +115000

 

[vidyanand]

I have tried this formula

{=IRR(IF(IFNA(ROW(INDIRECT("1:"&H2+H3+H4))=ROW(INDIRECT("1:"&H2)),FALSE),-H1,

IF(IFNA((ROW(INDIRECT("1:"&H3+H4))=ROW(INDIRECT("1:"&H3))),FALSE),0,

IF(IFNA((ROW(INDIRECT("1:"&H4))=ROW(INDIRECT("1:"&H4-1))),FALSE),H5,H1*H2+H5))))}

Where H1 = 100000, H2 = 10, H3 = 2, H4 = 25, H5 = 115000

But this is giving me #Value! error.