1

I am trying to do an IRR formula by group. However my formula keeps giving an error.

=IF(A2=A1," ",+XIRR(G:G,D:D,-0.1))

Can you please assist?enter image description here

James
  • 489
  • 4
  • 13
  • 33

2 Answers2

0

Update: After the PO added his data:

The XIRR range parameters should be something like C2:C12 and B2:B12, not an entire column.

Try the formula below: 

=IF(A2=A1," ",XIRR(C2:C12,B2:B12,-0.1))

B.T.W - in your title and your post you mention IRR function, but in your code you have XIRR, so which one are you trying to use ?

Shai Rado
  • 33,032
  • 6
  • 29
  • 51
  • Apologies XIRR. Column A has the name of the Investments, Column G has the cashflows of these and Column D has the dates when the cashflows occurried. I am trying to get the IRR for each group of investments. – James Feb 28 '17 at 09:35
  • @James the IRR of each group ? – Shai Rado Feb 28 '17 at 09:36
  • @James sorry, it's hard without seeing the data. Can you upload a screen0shot fo your data ? manipulate the values and names, so it will be hard to understand what it is – Shai Rado Feb 28 '17 at 09:39
  • I have uploaded sample data . Not sure if you can see it . If not i can forward file to necessary person. – James Feb 28 '17 at 09:54
  • @James have you tried my edited Formula ? is it working as you intended ? – Shai Rado Feb 28 '17 at 11:04
0

Some guesses:

  • Are you expecting a positive IRR? (That is the usual sign, so give a positive number as the last parameter)
  • Are the dates in date format? (As opposed to text - Check that by pressing Ctrl+Shift+1 you can see numbers)
  • Are the value numbers or empty?

The best would be, however, to see a screenshot of your worksheet and what exactly the the error is (e.g. #VALUE!, #REF!).

Update

You have the title row (Valuta and CCY conv) inside the referred range, these text data cause the problem, remove them by referring to C2:C12 and B2:B12 instead of C:C and C2:C12.

Update 2

I was experimenting how you could get a #NUMBER! error if you referred to C2:c12 instead of C:C.

Please make sure that the first number in the row is negative (I think the business logic behind this rule is that it is considered to be the initial investment value).

z32a7ul
  • 3,695
  • 3
  • 21
  • 45