CODEWITHSUNDEEP
excelvbairr
James
James

Reputation: 499

XIRR Calculation in Excel

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

Upvotes: 1

Views: 890

Answers (2)

z32a7ul
z32a7ul

Reputation: 3787

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).

Upvotes: 0

Shai Rado
Shai Rado

Reputation: 33692

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 ?

Upvotes: 0

Related Questions