3

I'm using LibreOffice 5.1.4.2 Calc and need to calculate the internal rate of return for a certain payment (say, in cell A1), a certain number of times (say, 100).

If I had the value repeated 100 times (say, in A1:A100), I could do:

=IRR(A1:A100)

But it seems odd (what if it's 100,000 times?).

The problem is, the function IIR expects “an array containing the values”.

How can I pass along to IRR an array of the value in A1 repeated 100 times?

1
  • I believe I've been confused what you were after here, sorry about that. In your example above do the cells A1:A100 all contain a single fixed payment? That is, the spreadsheet equivalent of { 7 | 7 | 7 | 7 ... 95x ... 7 }. Is your question "What if I needed { 7 | 7 | 7 | 7 ... 100,995x ... 7 } instead"? Now that I read more carefully, that appears to be how Jim DeLaHunt has interpreted the question.
    – kwutchak
    Nov 23, 2018 at 19:48

3 Answers 3

2

I think you are asking Libreoffice Calc to do something it cannot do.

In LibreOffice Calc, an array is a "a linked range of cells on a spreadsheet containing values" (per Help article Array Functions). There is such a thing as an "Inline Array Constant", but that's for putting values into an array. IRR() is not an array function, it is a single-valued function which takes an array as an argument.

I think LibreOffice Calc wants to you allocate those 100 (or 100,000) cells with the same value.

You can populate the cells in various ways. I would put the value in the first cell, then give the second cell a formula that read from the first cell with an absolute reference, then fill that second cell down 100 (or 100,000) times to get the array I needed. All I have to change is the value in the first cell, and it gets propagated through the array.

If you really want to type a concise formula to calculate the Internal Rate of Return for 100,000 payments of the same amount, maybe LibreOffice isn't the best tool for the job. It's a spreadsheet. A programming language, like the Python language and the numpy.irr() function, might work better.

1
  • Thank you, @jim-delahunt. I still find it odd that there's no elegant way to do what I need (without actually using n cells), so I'm not accepting your answer as the good one just yet, in case someone else weighs in on this.
    – tripu
    Jul 7, 2017 at 13:45
1

Yes, it is possible to write the array directly in your formula.
Calc refers to this as an "inline array constant".

A simple 3x2 example is

{1;2;3|"a";"b";"c"}.

Calc's Documentation has full details...

1
  • Again, my problem is that I may need to use a very long array. Imagine 300x2,000 instead. This syntax doesn't help with that.
    – tripu
    Nov 20, 2018 at 23:22
0

Write a function which creates a repeating array based on cells in the spreadsheet.

I haven't written any code for Calc, so I'm not familiar with the syntax used, but in pseudo-code:

function repeat( value, amount ) -> { value | value | value ... value } 

... then call this code from IRR.

Alternatively, create a function that does everything:

function repeatIrr( value, amount ) -> value

... and call that from the spreadsheet.

2
  • @tripu I assume this isn't what you are personally after (feel free to correct me on that!). I'm adding this answer in as it would be a solution, and may be acceptable to someone in a similar situation.
    – kwutchak
    Nov 23, 2018 at 20:05
  • ... personally if somebody else is interested in filling in the implementation details for Calc in this solution I'd love to see them!
    – kwutchak
    Nov 23, 2018 at 20:07

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .