XIRR & non-Contiguous Values?

I know IRR works with non-continguous values, but I can't
get XIRR to work. Here's what I'm doing:

=XIRR((B1,B5), (A1,A5), 0.07)

A B
1/21/2005 -750
-
-
-
12/31/2005 795

I get #VALUE! IRR works on the same cells, minus
the date arguments of course.

Bob

[email protected] wrote:
> =XIRR((B1,B5), (A1,A5), 0.07)

should those have colons between the cells? like B1:B5

In this case I only have two dates and two values which
are not located in adjacent cells in the spreadsheet. This
is supposed to work in IRR, and it does, but I can't get
it to work in XIRR. I can do:

IRR((A1,A5), .07)

Bob

[email protected] wrote:
> In this case I only have two dates and two values which
> are not located in adjacent cells in the spreadsheet.

Sorry, misunderstood the original question. Hmm, doesn't look like XIRR
works with non-contiguous cells.

Hello:

Works for me, either way.

A B
1: -100 3/17/2006
2:
3:
4:
5: 120 3/17/2007
6: 0.200000001
7: 0.200000001

Formula in b6 and b7

6: =XIRR((A1:A5), (B1:B5),0.07)
7: =XIRR(A1:A5,B1:B5,0.07)


Pieter Vandenberg


Bucky <[email protected]> wrote:
: [email protected] wrote:
:> In this case I only have two dates and two values which
:> are not located in adjacent cells in the spreadsheet.
: Sorry, misunderstood the original question. Hmm, doesn't look like XIRR
: works with non-contiguous cells.

It wouldn't work if you put random stuff in A2, A3, B2, B3 etc,
would it? I'm trying to get XIRR to pay attention only to
rows 1 and 5 . . .

vandenberg p wrote:
> Hello:
>
> Works for me, either way.
>
> A B
> 1: -100 3/17/2006
> 2: foo xx
> 3: bar yy
> 4: itsit zz
> 5: 120 3/17/2007
> 6: 0.200000001
> 7: 0.200000001
>
> Formula in b6 and b7
>
> 6: =XIRR((A1:A5), (B1:B5),0.07)
> 7: =XIRR(A1:A5,B1:B5,0.07)
>
>
> Pieter Vandenberg
>
>
> Bucky <[email protected]> wrote:
> : [email protected] wrote:
> :> In this case I only have two dates and two values which
> :> are not located in adjacent cells in the spreadsheet.
> : Sorry, misunderstood the original question. Hmm, doesn't look like XIRR
> : works with non-contiguous cells.

On 17 Mar 2006 13:04:46 -0800, [email protected] wrote:

>I know IRR works with non-continguous values, but I can't
>get XIRR to work. Here's what I'm doing:
>
>=XIRR((B1,B5), (A1,A5), 0.07)
>
> A B
>1/21/2005 -750
>-
>-
>-
>12/31/2005 795
>
>I get #VALUE! IRR works on the same cells, minus
>the date arguments of course.
>
>Bob

XIRR will not work with non-contiguous values for dates or values (unless there
are only blanks between them).

However, Harlan Grove wrote a UDF which will, if you NAME the non-contiguous
ranges.

<alt-F11> opens the VB Editor.

Ensure your project is highlighted in the project explorer window. Then,
Insert/Module and paste the code below into the window that opens.

Read the comments in the UDF.

You can then use it as =myxirr(values, dates, guess)

===========================
Function myxirr( _
v As Variant, _
d As Variant, _
Optional g As Double = 0 _
) As Variant
'-------------------------------------------------------
'this udf requires an explicit reference to APTVBAEN.XLA
'if v and/or d represent non-contiguous ranges, they should be NAME'd
'-------------------------------------------------------
Dim vv As Variant, dd As Variant, X As Variant, i As Long

If TypeOf v Is Range Then
ReDim vv(1 To v.Cells.Count)
i = 0
For Each X In v
i = i + 1
vv(i) = X.Value
Next X
Else
vv = v
End If

If TypeOf d Is Range Then
ReDim dd(1 To d.Cells.Count)
i = 0
For Each X In d
i = i + 1
dd(i) = X.Value
Next X
Else
dd = d
End If

myxirr = IIf(g <> 0, xirr(vv, dd, g), xirr(vv, dd))
End Function
===========================


--ron

Try...

=XIRR(N(OFFSET(B1,{0,4},0)),N(OFFSET(A1,{0,4},0)),0.07)

Hope this helps!

In article <[email protected]>,
[email protected] wrote:

> I know IRR works with non-continguous values, but I can't
> get XIRR to work. Here's what I'm doing:
>
> =XIRR((B1,B5), (A1,A5), 0.07)
>
> A B
> 1/21/2005 -750
> -
> -
> -
> 12/31/2005 795
>
> I get #VALUE! IRR works on the same cells, minus
> the date arguments of course.
>
> Bob

Thanks for the help, Ron and Domenic!

Bob

The real question is why would you expect that to work.

Pieter Vandenberg

[email protected] wrote:
: It wouldn't work if you put random stuff in A2, A3, B2, B3 etc,
: would it? I'm trying to get XIRR to pay attention only to
: rows 1 and 5 . . .

: vandenberg p wrote:
:> Hello:
:>
:> Works for me, either way.
:>
:> A B
:> 1: -100 3/17/2006
:> 2: foo xx
:> 3: bar yy
:> 4: itsit zz
:> 5: 120 3/17/2007
:> 6: 0.200000001
:> 7: 0.200000001
:>
:> Formula in b6 and b7
:>
:> 6: =XIRR((A1:A5), (B1:B5),0.07)
:> 7: =XIRR(A1:A5,B1:B5,0.07)
:>
:>
:> Pieter Vandenberg
:>
:>
:> Bucky <[email protected]> wrote:
:> : [email protected] wrote:
:> :> In this case I only have two dates and two values which
:> :> are not located in adjacent cells in the spreadsheet.
:> : Sorry, misunderstood the original question. Hmm, doesn't look like XIRR
:> : works with non-contiguous cells.

vandenberg p wrote:
> The real question is why would you expect that to work.

Like the OP said in the original post, IRR works with noncontiguous
cells, so why not XIRR?

Domenic - thanks for the great tip! I've been trying to figure that one out for a while. You can still also use array multiplication within the XIRR formula if, like me, you often represent your starting cash flow as positive, eg:

=XIRR({-1,1}*N(OFFSET(C29,{0,-16},{0,3})),N(OFFSET(C4,{0,0},{0,3})),0.7)

with values in C29 and F13 and dates in C4 and F4