Hello James
Could you please send me this formula? I am stuck with the same problem...
Thanks in advance!
JamesBuis wrote:
RE: Using XIRR in an array formula
28-Okt-08
It seems my previous reply went in blank!! The site is a bit quirky
I replied on teh 26 that I'd figured out that the problem was with xirr and
not the array formula because XIRR needs to have a -ve value to start with
and thus only the first entry works
The problem with indirect or even offset is taht it required all teh
cashflows for a single asset to be contiguous which means always having the
data properly sorted
Actually what I did was to write a vba function that will collect the
cashflows and dates for the asset id that is passed in the function, load
them into an array, sort it by date and then compute the IRRa nd return the
result. This works a treat and I don't even need the cashflows or assets in
any order. I use the find to get all the entries and its very flexible. I can
pass the column for the asset IDs, Cashflows and Dates and pop out a result
so its a pretty generic function
Initially I hoped not to have to do that but it took be much of yesterday
and was worth it
Many thanks for your input. If you want a copy of the function, let me know
Jame
:
Previous Posts In This Thread:
On Sonntag, 26. Oktober 2008 08:49
JamesBuis wrote:
Using XIRR in an array formula
I have a sheet with different assets and their cashflows and the dates of
those cashflows sucked out of a database. I need to return an IRR for each
individual asset and feel that this should be possible with an array formula
If I were simply summing or getting an average it is very simple. However, I
can't figure out how to handle a function that requires two parameters in an
array
If I were summing I would use this
={SUM(IF($B$3:$B$13 = G3,$C$3:$C$13,0))} wher
$B$3:$B$13 holds the asset Ids and $C$3:$C$13 holds the cashflows The real
list is several thousand rows). I could use the IRR function by replacing SUM
with IRR. However that doesn???t take account of the dates of the cashflows and
assumes that each entry is a fixed period apart (like months). Mine are not
so I need to use XIRR (in the analysis tool pack). However, that requires a
range parameter for the cashflows as well as one for the dates. The same
could hold true for other functions that require 2 parameter inputs
I???d rather not write a special function in VBA if I can help it and am sure
there must be a clever way of doing this using an array formula but I can't
seem to get it
Many thanks
On Sonntag, 26. Oktober 2008 09:07
JamesBuis wrote:
RE: Using XIRR in an array formula
:
On Sonntag, 26. Oktober 2008 09:22
JamesBuis wrote:
RE: Using XIRR in an array formula
Sorry, I forgot to say this was my attemp
={XIRR(IF($B$3:$B$13=G3,$C$3:$C$13,0),$A$3:$A$13)} where $B$3:$B$13 is the
asset ids and G3 is the asset ID of the asset I need the IRR for (G4 would be
the next asset id and G5 the one after that etc, $C$3:$C$13 are the cashflows
which are set to zero if the asset ID doesn???t match and $A$3:$A$13 are the
dates of those cashflows. The logic seems fine and the zero cashflows don???t
affect the result when I test it manually using XIRR on the whole range. The
formulas computes the correct IRR for the first asset in the list but then
returns zero for all the remaining assets in the arra
I???m puzzled why it doesn???t wor
Also, apologies for the empty post above. The site refused to accept this
reply first time and so I went back and tried again
:
On Sonntag, 26. Oktober 2008 18:25
ShaneDevenshir wrote:
RE: Using XIRR in an array formula
Hi
Well it works for me. i teste
A B
Values Date
-10,000 4 1-Jan-0
2,750 4 1-Mar-0
4,250 4 30-Oct-0
3,250 4 15-Feb-0
2,750 4 1-Apr-0
In G3 I entered
The formula
=XIRR(IF(B2:B6=G3,A2:A6,0),C2:C6
which is just a modification of yours returned
0.37336253523826
Which is the correct answer
--
Thanks,
Shane Devenshire
:
On Sonntag, 26. Oktober 2008 20:11
JamesBuis wrote:
RE: Using XIRR in an array formula
I said it worked for the first item only. Add
in column B 4 more entries with code 5 against them instead of code 4 and
put 5 in G4 and copy down teh array formula. You will get 0.
I do now know that the porblem is not with teh array folrmula but with xirr.
It requires teh first entry to be a negative cashflow and thus will not
accept a zero value in the first item. Thus it will only work for a single
asset thus making it not compatible with teh array formula. For a single
asset, I don't need the array formala.
:
RE: Using XIRR in an array formula
Been working all day, first chance to get back to this.
={XIRR(IF($B$3:$B$13=G3,$C$3:$C$13,0),$A$3:$A$13)}
It looks to me as though you probably have numbers or names in column B3:B13
which are set to one of several value? When you enter a value in G3 this
causes the formula to use a specific subset of the values in the range. Of
course you didn't supply any data so its all guesswork. Now suppose that G3
contain the value of the code for the first set of data, most likely the
first cells of the first set of data, cell A3:C3, contain the initial
starting value for the function -10,000 for example. Now keep in mind the
formula is returning, an array that looks something like this
-10,000
2,750
4,250
3,250
2,750
0
0
0
0
0
0
The fact that the last cells are 0 is no problem for XIRR. But now consider
what happens when you change the code in G3 (I realize you want to copy the
formula down so it will use G4, but changing G3 produces the same effect),
the resulting internal array looks like this to the function (I'm not showing
the dates)
0
0
0
0
0
-10,000
2,750
4,250
3,250
2,750
Which of course means that the starting value is 0.
Now again, you didn't give us the details, but suppose you want to pick
different groups for the function by changing the reference from G3 to G4 by
copying, then the new range can't begin with 0. So instead of referencing
the whole range what you may need to do is have the entire range change when
you change the value in G3 or copy the formula down. But again, without
sample data to see what you have in column B it's impossible to make any
constructive suggestions.
Note that the reason you need to enter this formula as an array has nothing
to do with XIRR, it has everything to do with the IF function.
Here is one way:
=XIRR(INDIRECT(G3),INDIRECT(H3))
Where G3 contains the cell addresses A1:A5
and H3 contains the cell address C1:C5
--
Thanks,
Shane Devenshire
:
RE: Using XIRR in an array formula
1. The need to use of an array has nothing to do with the XIRR function it
is entirely because of the IF function.
2. The internal result of entering the first value in G3 is to give you
something like
-10,000
2,750
4,250
3,250
2,750
0
0
0
0
0
By changing this to G4 it probably gives something like
0
0
0
0
0
-20,000
4,750
8,250
6,250
4,750
XIRR expects the first entry it uses to be the -20,000 but its 0.
Now keep in mind you have supplied us with no samples of your data, so this
is purely speculation. But it does tell you why no one else responded,
without the data we are shooting in the dark. What's in column A, and B and
C and G3...?
You will definitely get better answers when you supply that kind of info.
--
Thanks,
Shane Devenshire
:
On Dienstag, 28. Oktober 2008 01:46
JamesBuis wrote:
RE: Using XIRR in an array formula
It seems my previous reply went in blank!! The site is a bit quirky.
I replied on teh 26 that I'd figured out that the problem was with xirr and
not the array formula because XIRR needs to have a -ve value to start with
and thus only the first entry works.
The problem with indirect or even offset is taht it required all teh
cashflows for a single asset to be contiguous which means always having the
data properly sorted.
Actually what I did was to write a vba function that will collect the
cashflows and dates for the asset id that is passed in the function, load
them into an array, sort it by date and then compute the IRRa nd return the
result. This works a treat and I don't even need the cashflows or assets in
any order. I use the find to get all the entries and its very flexible. I can
pass the column for the asset IDs, Cashflows and Dates and pop out a result
so its a pretty generic function.
Initially I hoped not to have to do that but it took be much of yesterday
and was worth it.
Many thanks for your input. If you want a copy of the function, let me know.
James
:
On Mittwoch, 20. Mai 2009 22:02
Justin Gibbons wrote:
Nested XIRR IF statement
Hi James,
May I please have a copy of the function? I'm not able to make the following formula work: ={XIRR(IF($A$8:A121=A121,$K118:K121,0),$C$8:C121)}where $A$8:A121=A121 is text containing the names of ten different investment securities; $K118:K121 is cashflows (starting with negative numbers); and $C$8:C121 are dates
Thanks,
Justin
On Donnerstag, 21. Mai 2009 05:39
Marco wrote:
Nested XIRR IF statement - Justin Gibbons
You are required to be a member to post replies. After logging in or becoming a member, you will be redirected back to this page.
On Mittwoch, 7. Oktober 2009 05:15
Saket Bagade wrote:
Using XIRR in an array formula
James, can you please send me this function?
EggHeadCafe - Software Developer Portal of Choice
Save XAML Files In Any Image Format
http://www.eggheadcafe.com/tutorial...5-5717898a0e55/save-xaml-files-in-any-im.aspx