Problem with XIRR Array formulae using column()

[ikeringillc]

Hi there, hope someone can help me with this. I've searched previous posts and can find loads of really useful information, but I can;t seem to find a solution to this one

Basically I have data in a sheet that is based on dates across the columns and then rows with values that correspond to those dates. I then use XIRR in an array formula to calculate rates of return between two dates with a formula like this one ...

=XIRR(IF(COLUMN(J9:O9)=COLUMN(J29),-J9,IF(COLUMN(J9:O9)=COLUMN(O9),O9-O29,-J29:O29)),J1:O1)

The above example is calculating the return based on 6 months (J:0) of data. The challenge is to make this period variable (i.e have the number of months as an input) and make it dynamic. I thought this would be relatively straightforward by a combination of address() and column() like this ...

=XIRR(IF(COLUMN(INDIRECT(ADDRESS(9,COLUMN()-5,4)&":"&ADDRESS(9,COLUMN())))=COLUMN(INDIRECT(ADDRESS(9,COLUMN()-5,4))),-1*OFFSET(O9,0,-5),IF(COLUMN(INDIRECT(ADDRESS(9,COLUMN()-5,4)&":"&ADDRESS(9,COLUMN())))=COLUMN(O9),O9-O29,-J29:O29)),J1:O1)

This all seems to work ok until I try and use the exact same principle to then change the reference in RED at follows, at which point I always get a #Value error.

INDIRECT(ADDRESS(9,COLUMN()-5,4)&":"&ADDRESS(9,COLUMN())))).

If I remove the '-' then I no longer get the error, although clearly the formula will return the wrong result as these values need to be -ve for XIRR to calculate correctly! If I simply test this formula in isolation it seems to work fine, but as part of the formula above it always gets a #Value error.

Any ideas on how I can use this technique and make the values -ve (*-1) of their current value?

Many thanks as I've spent far far far too many hours on this one

 

[joeu2004]

I must admit: I have not parsed your use of INDIRECT and ADDRESS carefully.

But off-hand, does the following meet your need much more easily?

=XIRR(IF(COLUMN($J$9:O9)=COLUMN(J29),-J9,IF(COLUMN($J$9:O9)=COLUMN(O9),O9-O29,-$J$29:O29)),$J$1:O1)

The use of $J$9, $J$29 and $J$1 "anchor" the beginning of the ranges to column J as you copy the formula across the columns.

 

[ikeringillc]

I must admit: I have not parsed your use of INDIRECT and ADDRESS carefully.

But off-hand, does the following meet your need much more easily?

=XIRR(IF(COLUMN($J$9:O9)=COLUMN(J29),-J9,IF(COLUMN($J$9:O9)=COLUMN(O9),O9-O29,-$J$29:O29)),$J$1:O1)

The use of $J$9, $J$29 and $J$1 "anchor" the beginning of the ranges to column J as you copy the formula across the columns.

Hi, thanks for the response, however this is pretty much where I started from but I now need to change the date range to be variable ratherthan from a fixed point. I never found asolution to why I get a #Value error when trying to make the values -ve, however Ifound a workaround by simply reversing the +ve/-ve values passed to XIRR (it doesn’tseem to care so long as you have both +ve and -ve) and thus bypassing the issueas follows ….

=XIRR(IF(COLUMN(INDIRECT(ADDRESS(9,COLUMN()-RPMonths+1,4)&":"&ADDRESS(9,COLUMN())))=COLUMN(INDIRECT(ADDRESS(9,COLUMN()-RPMonths+1,4))),1*OFFSET(HW9,0,-RPMonths+1),IF(COLUMN(INDIRECT(ADDRESS(9,COLUMN()-RPMonths+1,4)&":"&ADDRESS(9,COLUMN())))=COLUMN(HW9),-1*(HW9-HW29),(INDIRECT(ADDRESS(29,COLUMN()-RPMonths+1,4)&":"&ADDRESS(29,COLUMN()))))),INDIRECT(ADDRESS(1,COLUMN()-RPMonths+1,4)&":"&ADDRESS(1,COLUMN())))

Thanks