Excel: XIRR() and Date()

C

CF_AHHHHHHHHH

New member
Joined
Jun 18, 2026
Messages
0
Reaction score
0
I’m trying to use the XIRR () function in excel. However, I’m getting some weird results which are leading me to believe that maybe my dates are formatted incorrectly.
Right now they are in column A, written as: 9/28/2009
The instructions for the XIRR function indicate that it will only work if dates are inputted using the date function, so: =Date(2009,9,28)
I’m hoping this will solve my problem. However, I have over 3,000 rows of data and dates. Does anyone know an easy way to convert 9/28/2009 to =Date(2009,9,28)??
Conversely, maybe this isn’t my problem at all, do any of you know why my XIRR function would, regardless of input data, always return the same value of 0.00000029802%?
Let me know if I need to clarify my problem further.
Thanks!
 
When I’ve used the XIRR function, I’ve used the xx/xx/xxxx date type and it has worked fine. Have you tried testing it with a smaller set of data, like 3 dates and cash flows?
 
Well, thats giving me real values… hmmm
Is there a limit to the number of cash flows you can use? That seems odd?
 
Who uses XIRR functions? Dude, so amateur. Do it in your head! J/K
I have honestly never used the XIRR function so I am adding no value to this thread.
 
You should test to see if 9/28/2009 is a date or a string. If it’s a string you won’t be able to do much. Try multiplying by one in another cell. If you get the date, you’re fine.
Also, what kind of periods are the cash flows? If they are monthly or yearly, I would use a static date as your first date and then use =edate(cell, 1) or (cell, 12) to set up your dates.
I use xirr fairly often, and I can take a look at it later if you’re still having trouble.
 
Thanks for the input… i’m still having trouble though… Going from Steph’s advice I started off small with only a few periods to see at what point it would break down.
It worked fine up until the 37th period… and since they periods are monthly, it worked up until i exceeded 3 years of data. (36/12=3 yrs)
Can anyone comment on whether or not XIRR has some sort of limit?
AlexP, thanks for the tip, but that gave me a NUM error
 
If your dates are coming in wacy just run a T2C on them and it should sort everything out.
 
have you tried using the edate function to make your dates march correctly?
a1 = month/day/year
a2 = edate(a1,1)
a3 =edate (a2,1)
etc
 
XIRR gets confused (since its an iterative process) with a lot of dates, i think you’ll have to put in a guess. any date format (yyyy-mm-dd or mm-dd-yyyy) should work as long its formated as a date.
 
I used XIRR in a VC reporting document for several thousand transaction and it worked, with some hair pulling. Generally the problem is with the date column as you previously discovered. Putting a guess into XIRR can help, but doesn’t always solve the problem. Use the form XIRR(Cfs,Dates,Guess in decimal form). All in all the implementation of XIRR is pretty buggy, sometimes it seems to work great and other times it doesn’t function. For example it will be working in a worksheet, then I will close the file, re-open and it no longer works. Good luck, let us know if you find any fabulous work-around
 
I’ve kind of set up a work around… I’m filtering out any periods that were previously in there that had a zero net cash flow. Taking the zeros out its now spitting out reasonable responses.
But as buggy as its been and with the problems others have had with it… I’m not sure i really want to trust using it. Anyone have any other suggestions on ways to measure the return on irregular cashflows over a long period of time? :)
 
It’s prob. a cash flow problem, xirr gets messy with out of wack numbers b/c remember from Level 1, it’s essentially a reinvesment rate.
Try XNPV and if that works and number is positive (or not drastically negative anyhow) then it’s a cash flow prob. that can’t be (easily) fixed…
 
So I just tried something…
Lets say I set up an XNPV formula as follows:
=XNPV(S730,S2:S719,A2:A719)
and then i run the Solver add-in, trying to make the target cell (the one with the XNPV formula) equal to zero, by changing cell S730… which is the rate input into the XNPV formula (see above).
Wouldn’t the answer I get here be the same as what I should be getting from an XIRR formula?
 
Yes (if the Solver add-in is the same as goal seeking). If that rate is extremely high or way negative or something then it’s your data.
Instead of doing the simple way, you could also use a discount factor for your cash flow stream too…
 
So say my cash flows span 20 years…
The value that Solver spits out… is that a overall return? or a annual return. Thus, x% per year for the 20 years?
(Sorry, I’m so used to using the BAII Plus, that doing these things in spreadsheets is kind of new to me!)
 
Booya, excellent news… I’m gonna be so rich.
haha jk… thanks! I think this work around is getting the job done for me.
 
CF_AHHHH, that really depends on the cash flow schedule. If you assumed it to be annual payments, then the number solver pulls up is the annual yield. If it is monthly, then multiply it by 12.
 
I have worked a lot with it.
The truth is that the XIRR in Excel has errors. Especially if you have multiple values on the same date you may get into problems and weird results.
I build my own XIRR in vba and added a homebuild solver to do the iteration and get the correct internal rate. It works perfectly and works just like a normal excel-function.
 
You must log in or register to reply here.
Back
Top