Problem with XIRR Array formulae using column()

ikeringillc

New Member
Joined
Aug 28, 2018
Messages
2
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 :mad:
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
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.
 
Upvote 0
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


 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,981
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top