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 '18 at 19:48
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 '17 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 '18 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 '18 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 '18 at 20:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.