Function of tilde (~) operator needed for xirr-formula

[stsch]

I am referring to this simple example:

snapg.gif (6.97 KiB) Viewed 6351 times

How can I achive what I indicated in column G?

Regards,
-Steffen-

[acknak]

The tilde operator is supported in the most recent versions of Calc, but most functions are not capable of handling complex input ranges. E.g., =SUM(D4:D8) -> 7000 on your data, and =SUM(D4:D8~E4) gives -1000, so SUM is ok, but your XIRR formula gives error 504, so I guess that the XIRR function does not work with a complex range.

[stsch]

so I guess that the XIRR function does not work with a complex range.

That's also my guess. But how can I achive what I need?

[acknak]

The only way I can think of is to make a simple range that contains references to the input cells and then apply the formula to the simple range.

[stsch]

The only way I can think of is to make a simple range that contains references to the input cells and then apply the formula to the simple range.

And making this simple range is exactly the problem. Moving E6 and E8 one column to left will do it for calculating the first xirr, the xirr of my investment on 31/Dec/2009, but this will not work for all the succeeding dates in December.

[acknak]

Sorry, I was not suggesting that you re-arrange your data; I was suggesting that you make a new range that simply copies the data to a simple rectanglar range.

[stsch]

Sorry, I was not suggesting that you re-arrange your data; I was suggesting that you make a new range that simply copies the data to a simple rectanglar range.

I am sorry, I still don't see how this can help in calculating the xirr.

This is a rather simple example, I am surprised that it seems to be a so complicated to do the calculation with OO Calc. Every month I invest a bit of money and in the end of the year I get a statement from the bank telling me the balance of my investment. I now would like to calculate the xirr for the different years without (!!!) rearranging the data for those years.