Excel IRR: can I use it referencing a combination of cells and fixed numbers? IRR({-10,11+A2})

Question

Most Excel users are probably familiar with the syntax of Excel's IRR formula: https://support.office.com/en-us/article/irr-function-64925eaa-9988-495b-b290-3ad0c163c1bc

I can use IRR using only fixed numbers, and without referencing cells, e.g.

=IRR({-100,110}) will return 10%. This works regardless of whether I enter it as an array formula (CTRL+SHIFT+ENTER).

However, what I cannot seem to do is using a combination of fixed numbers and cells, e.g.

=IRR({-100,110+a2})

doesn't work: regardless of whether I enter it as an array formula or not, I get the same error message:

we found an error with this formula.

Thoughts?

Answer 1

Use this:

=IRR(CHOOSE({1,2},-100,110+A2))

Answer 2

If you try to do to math inside {}, for example ={1,2+5} excel does not know what to do. Does also apply inside formulas.

Try to do the array as a reference i.e =IRR(B1:C1) and then do math etc inside the cells referenced.