By Dr. Jim Dahle, WCI Founder
As you invest, it is important to know about how you are doing. It is easy to see how your individual investments are doing each year. They report to you their time-weighted return. Of course, that isn't the return you generate. You get a dollar-weighted return. It turns out that the dollar-weighted return for most investors is far lower than the time-weighted return of their investments. This occurs mostly due to performance chasing and the buying high/selling low phenomenon that results from it.
While I don't think you need to look at your investments every day, or even every month, from time to time you ought to check in and see how you are doing. If you don't know what returns you have been getting, it is hard to gauge how well you are progressing toward your goals. It is also easier to get sucked into investments that promise a high return, but don't actually deliver. In short, being able to calculate your own return empowers you as an investor.
The best way to calculate your return is to use the Excel XIRR function (also available with other spreadsheets and financial calculators). This gives you a dollar-weighted return because it takes into account the timing and amount of your cash flows into and out of your retirement funds. It is surprisingly easy to calculate. All you need to know is the amounts you have put in or taken out of the account and the dates on which you did that. Here's a quick tutorial:
Put the amounts of your cash flows into column A. Amounts you contributed to retirement and other investment accounts are positive. Amounts you took out are negative. The last entry should be the current amount you have, as a negative number. Put the dates of the cash flows into column B. You need to use the excel DATE function to do this. It looks like this: =DATE(2004,8,16) where 2004 is the year, 8 is the month, and 16 is the date.
Now, in another cell, put in the XIRR function. It looks like this: =XIRR(A1:A10, B1:B10, 5%) where your cash flows are in cells A1 to A10, your date functions are in cells B1 to B10, and 5% is your estimated return. (If left blank, it defaults to 10%.)
Now for an example:
If you want, click on the button in the lower right and follow the directions if you want to download this file to play with it.
Remember that 9.75% is an annualized return, so it means that between 7/11/07 and 5/19/11 this investment returned 9.75% PER YEAR. If your period of time is less than one year, it will also annualize the return. For example, if your period is 6 months, and your return is 5%, then XIRR would return 10%.
If you would like to calculate a year to date return and/or calculate out your return for each calendar year you have had the investment, it only gets a little more complicated. First, you'll need to add in the value of the investment at the end of each year. I use two entries, the first negative and the second positive, both with the date of 12/31 of the given year. Then, run the XIRR function from the positive entry on 12/31 of one year, to the negative entry on 12/31 of the next. See the next spreadsheet for details of how to calculate returns for partial years, full years, and the year to date.
Play around with it for a few minutes and you'll figure it out. If you're a spreadsheet junkie, you can also break it down for each retirement account, or even each individual investment. All you need are your inflows and outflows, and the corresponding dates. If you want to calculate the yearly returns or the year to date return, you'll also need the year end values of the investments. But you don't have to take into account fees, commissions, or any dividends or capital gains that are reinvested. Of course, if you don't reinvest dividends, those should be considered withdrawals from the account, just like fees paid from a separate account, should be considered contributions to the account. XIRR is a powerful function that will allow you to calculate your portfolio's overall returns, no matter how many different retirement accounts you have.
Thanks for the info. I played around with XIRR function, but I have a question: how does one deal with dividends?
That’s one of the beauties of XIRR. You don’t have to deal with reinvested dividends. All you need is the beginning value and ending value. If you’re spending the dividends, count them as withdrawals from the account.
Sorry to “reply” to a post, but I couldn’t find a way to simply make a comment.
I am hoping you can help me. I am having a debate with a colleague about XIRR. We work in private equity with real estate investments and use the XIRR function to report our realized investment returns. Our typical deal stretches over several years and involves a rather large up front investment, several smaller returns from operational cash flows, and a large final return from the sale of the asset. However, we often have a holdback distribution that can take several years to come back and is usually a very small cash flow.
I have assured my colleague that the holdback distribution, no matter how small and no matter how much time passes, will not serve to lower the XIRR that was calculated at the time of sale. I have gone so far as to show him an extreme example of a holdback distribution that takes 250 years to come back, and the XIRR simply remains unchanged. He argues this is illogical and quotes exactly what you have in this blog, “Remember that 9.75% is an annualized return, so it means that between 7/11/07 and 5/19/11 this investment returned 9.75% PER YEAR.” Therefore, if the final distribution occurs in the year 2261, the XIRR should be diluted to account for the 254 year investment time frame. However, it is not.
I argue that XIRR is actually an “annualized” return, not an “annual” return. The distinction being that an annual return does tell you the rate of return for every year during the entire life of the investment. An annualized return take into account both the size and timing of the investment and condenses them over the yield curve as if the entire investment occurred over a one year period. Therefor, the final sale proceeds (which are the largest monies distributed) have the largest weighting, and a small holdback distribution many years later has such little impact as the curve expands to infinite, that it results in no change whatsoever to the XIRR. (Only if the final holdback distribution were extremely large would it impact the XIRR, and in that case it would only serve to increase the XIRR, never to decrease the XIRR.)
I would appreciate if you would weigh in and help us to determine what is actually occuring with the XIRR when the investment period becomes significantly lengthened by the final holdback distribution. Thank you.
I agree with you. It’s really a dollar weighted return, not a time-weighted return. So if you invest $1000, get $2000 back in year 10, and then get $10 back in year 100, that $10 just isn’t going to affect the XIRR significantly.
Two questions,
Why do you have to “guess” a 10% or 5% return? What is that doing in the formula?
Do you have to put in the date 2012,10,23, or could you put just the cell numbers that the date range is in?
Thanks, really appreciate the info.
The guess is initiating a search. Lots of higher level mathematics have no closed form solution (eg an equation that gives you a definite answer in a finite number of steps) instead they have answers that either involve an infinite amount of steps or can only be approximated. Other answers are too difficult to exactly solve so using an approximate solution requires much fewer calculations than a “true” answer.
The branch of mathematics that deals with efficient search algorithms is known as numerical analysis. Inputting a reasonable guess will either save machine calculations or allow an actual answer. If your guess is too unreasonable the algorithm may not converge to an answer. With what you are using it for this is unlikely even with a really bad initial guess; however the underlying algorithm is known as “solver” in excel and this is one of the functions that calls solver up. In multidimensional searches etc… the starting guess would be much more important. Hope that helps.
FWIW,
Regarding the 5% or 10% guess discussion, I used the YTD formula from the author’s row 28 example and played around with the different guess return numbers. I used, 5%, 10%, 100%, 1000%, or used the formula with no guess percentage. For all results, including not entering a guess percentage in the YTD formula, all results were exactly the same.
Brian
nice!
I’m not sure I’m enough of a math whiz to answer your first question. It has to do with the algorithm the function follows. You do have to use the “date” function, but if you put in the cell number and a date function is in the cell that’s okay.
Those who know a little about investing know that 9% consistent returns over 2 years is better than 18% one year and 0% the next, still with an average of 9%. I used this function to help figure out what the equivalent “consistent return” on a mutual fund might be. You know, they will advertise the better of the 2 numbers: The average annual performance. So I took a Vanguard fund (big Vanguard fan) that had 9.02% average annual performance over 10 years, and I used the XIRR function to see what would be the equivalent CONSISTENT return over the 10 years. Turns out it was 7.2%. In other words, a non-volatile fixed investment of 7.2% would return the same as this fund that was volatile but averaged 9% annually. Really hammered home this idea that the stock market has NOT returned 10-11% over time ….. This is the average of its yearly returns, which is not the number we really want when we’re estimating how much our money will grow with time.
An excellent point. The difference between the arithmetic mean and the geometric mean and the significance of it is pretty depressing.
I’ve just discovered XIRR and came across this, which was by far the clearest article on the subject. Thank you!
I know I don’t have to enter reinvested dividends and that makes sense because it’s not “my” money going in as a contribution and is internal to the account. It’s not clear to me why, then, dividends not reinvested should be removed, but I honestly don’t care about that scenario as all stocks in my portfolio are in DRIPs.
The article mentions about not having to take fees into account. Consider this: I send in $100. They take $3.10 in fees, etc., actually investing $96.90 for me. Do I enter $96.90 for the amount of that transaction, or $100? My initial thought was $96.90, because that’s what actually went towards the purchase and I continued onward, but I am now second-guessing myself.
I disagree. You should be putting in $100. That’s what it cost you. Otherwise you’re calculating your return without regard to fees, which isn’t really what you should care about.
Dividends not reinvested don’t stay in the account, that’s why you treat them as withdrawals. As long as they’re reinvested you can ignore them for the calculation.
You make a nice explanation of the XIRR function, but I have a few questions since I’m not a math or Excel whiz, but would love to test it on my DRIP’s and mutual funds. So…my questions:
1. When explaining above the first spreadsheet, you say “The last entry should be the current amount you have, as a negative number.” What’s the current amount? Is it the market value of my investment on a certain day (e.g. 100 share x $105/sh.on 10/15/12 = $10,500 as a negative value for XIRR funcion)?
2. PP kind of explained why dividends reinvestments should be excluded from the data input, but even though he/she doesn’t care to know why not reinvested dividends have to be showed as cash out-flows, I would like to know. Why reinvested dividends need to be excluded from the data input, but if I choose to not reinvest, it has to be entered as a negative number (what date to use for it?)?
. How to calculate a XIRR of a portfolio consisting of say 5 DRIP’s & 3 mutual funds, when weekly and monthly investments occur? Do I have to combine all contibutions in one file in chronological order? What’s the current amount of all 5 investments? Add market values of all?
I think I’ve got more q. but it’s enough for starters :-)).
1. Yes. Exactly.
2. If you reinvest the dividend it isn’t money taken out of the investment, so you don’t have to include it. If you don’t reinvest it, it’s money taken out, so you need to include it as a negative number. Use the dividend date.
3. Yes, you have to include all contributions with their relevant dates. I don’t think they actually have to be in order, but I keep mine in order. The current amount is the market value of all. I keep track of my entire portfolio that way. A separate XIRR for each investment and a grand total XIRR for the total.
Is the grand total XIRR just an average? A weighted average?
It’s the annualized dollar weighted return. When you say Average, what do you mean?
Whoops, should have been more clear. I was referring to calculating a grand total XIRR across multiple accounts.
So, to rephrase the question:
Say one has three different accounts, each with a different XIRR. HOw does one calculate a “grand total” XIRR, or a return that takes into consideration all of the accounts? Would it be a weighted average the XIRRs of all three accounts?
I keep a running total of all additions and withdrawals to my entire portfolio (and the dates) in a separate column, and XIRR it.
Ah – genius. Thanks!
If you want to get really technical about it you can count the dividends as contributions. In that scenario you’re picking apart the returns, so you can really see how much capital growth you’ve got as compared to passive income.
I personally like to just lump it all together because I have no need to manipulate the returns that I should (as asset managers would). For me it makes the most sense to see what I put in VS what is it now, and to account for the timing of investments.
You can only count the dividends as contributions if you first count them as withdrawals. As you might imagine, not counting them at all does the exact same thing.
How do you account for changing asset allocations? Thanks.
It’s all one pot of money. Run the numbers on the whole pot no matter what investments you’re using.
Thank you for your reply!
I’ve been a stock picker (value large cap) for 12 years now investing in both canada/us markets with AA varying from 100% to 70% equities. After calculating my IRR, what benchmark would be relevant to evaluating my performance since benchmarks are usually all equity? Thanks again.
I suppose I’d run it against a benchmark composed of a large cap value index fund 85% or so and 15% in whatever else you put your money into when it’s not in large cap value stocks.
Great summary. I have not kept up with the dates of my prior contributions over the years, and I don’t want to go back and do that. If I start the list with my total account amount and today’s date, then add contributions as I make them, will the formula work to calculate returns from here on out? Thank you!
Yes. Your first date will be today and the first value will be your total account value today. It would probably be relatively easy to at least go back to the first of the year.
I’m still not clear about a few things.
1. Should the XIRR cell formatted as Percentage? My XIRR project has been put on the back burner for the time being, but yesterday I decided to test on one of my DRIP accounts and I got an EXTREMELY SAD result 🙂 (that it’s really making me laugh instead…I’m not on dope though). After I entered =XIRR(A1:A10, B1:B10, 5%)I got something like 0.13, I think. I changed 5% to 0.05, same answer. While driving to work today, I started thinking that maybe the cell is formatted as a General number or Accounting, and I should re-format to percentage. Could be the case here? But if it was formatted as a percentage cell, then my DRIP won’t be funny anymore.
2. I’d like some clarification from your exchange with the FutureMedStudent in June’13 about the grand total XIRR.
If I understand you correctly, you calculate a XIRR for each investment (in its own worksheet perhaps) and then then you have a combo worksheet containing ALL your investment accounts (all taxable investments, all IRA’s and/or Roth IRA’s, 401k’s etc.) in chronological order and calculate the ‘grand’ XIRR in that way. Is this correct? I’m asking because I’ve read somewhere people calculating a XIRR of many XIRR’s which I found quite confounding.
3. I’m also curious how you do yourself and if it makes sense or not to do it. So, with regards to the question 2, do you calculate yearly XIRR and keep it for your future reference (to see how your investments did well or bad in hindsight) or after each passing year you delete prior year’s XIRR and calculate a new cumulative XIRR for a new period. E.g. Let’s I started investing in Oct’05, so it’s a XIRR for 2005. Then I continue investing in 2006, 2007, 2008. Does it make sense to keep XIRR of 2005, 2006, 2007 and 2008, or one XIRR for Oct’08 through 2008 suffices to keep my sanity since it’s annualized return. Your thoughts?
Thanks so much. I think your XIRR explanation is the best out there.
I’ve sent you a copy of an XIRR worksheet I used years ago that probably answers your questions.
1) I do format the actual XIRR cell as a percentage.
2) Exactly. It doesn’t even have to be chronological order (although mine is). Look at the example I sent you by email.
3) I do both. Again, look at my worksheet I sent you. Feel free to adapt it to your own use. You can make your own as complicated or as simple as you like.
Can you email me a copy of this XIRR worksheet? Hoping to avoid recreating the wheel. Thanks.
What worksheet are you referring to? I’ve emailed something I put together years ago to share, but it’s almost surely not what you’re asking for.
Oh – I was referring to the XIRR worksheet mentioned in the comment above.
So you’re good with what I sent you then?
Thanks for your prompt answer and I appreciate the worksheet. I’ll check it out at home. I’ll get back if I have more questions.
Re your response to my 3rd question, I tend to see the big picture of a matter at a very beginning, but as soon as I start ‘drilling’ I’m prone to losing the sight as if I’m seeing the trees only and not the forest anymore :o).
Hi,
Last few questions I hope (after a quick review of your personal spreadsheet). I noticed you had an MM account. Not sure it was included in the total portfolio column along with other investment or not, because I couldn’t tie totals by their dates. Anyway, you don’t really need to answer this, but just noticing the MM account on your spreadsheet, it got me contemplating what a person has to account for his/her CD’s/I-bonds/Saving accounts. They don’t really have transaction dates. So, all I have to do is enter their beginning values when a CD or I-bond was started (as a positive number?) and then their values at the end of the year as negative, correct?
Just now I came up with a situation.
Your article says “If you don’t reinvest dividends, those should be considered withdrawals from the account, just like fees paid from a separate account, should be considered contributions to the account.” So, with regards to taxable accounts, working people in accumulation phase pay yearly taxes on dividends, cap. gains, and interest from their working income, should those taxes be included as withdrawals on the total portfolio at the YE? Or is this going way too nitty gritty? It would involve preparation 1040 taxes twice: with passive income and without to learn the difference in taxes. After this I should be all set. Thanks so much.
I don’t try to include taxes because of the hassle but mostly because the vast majority of my portfolio is tax-protected. I tried it once a decade ago and it just wasn’t worth the hassle.
While collecting raw data of my Roth, I got another question. How to treat exchanges between funds? Technically I didn’t get money out, but it seems like a withdrawal to me.
If I wanted to XIRR each fund separately, shouldn’t I show Exchanges/transfers out of fund as withdrawals (negative numbers) and inflows as positive numbers into a new fund? If not, why?
If I wanted to XIRR my Roth as a whole pot, it seems that maybe it’s not necessary to show exchanges but a gut feeling says I should just to account for timing of money since you say it’s time AND money weighted annualized return. Thanks again.
Yes, and yes. I think you’ve got a good handle on it.
I calculated one account, but not sure how to interpret the result. The yearly statement from the TRPrice says 6.82% “Your account Return since 8/31/04” (through 12/31/12). I guessing the firm calculates time-weighted annualized return. My calculated XIRR says 7.35%. So, which result is correct or would it imply that they’re both right because the %-ages are pretty close? In my mind, if it’s ANNUALIZED return, than either I did 0.53% better or worse.
The above leads to my next question: how or where could I learn how SP500,Wilshire5000, etc. did for that period or any other chosen period of time for that matter? It wouldn’t really reflect a correct benchmark in this case, but still. This account consists of TRRDX and a portion of small stock trust.
Sorry to bug you will my questions, but I hope other readers can benefit as well.
The TR Price return is probably time-weighted. The XIRR return is dollar-weighted. I find dollar-weighted more useful and thus “more accurate.” When I want to compare returns, I usually use Vanguard’s index funds for that time period. A quicker way is to plug Vanguard’s funds into Yahoo or Google financial, but keep in mind those financial sites usually don’t count the dividends.
Hello, thanks for the informative post. I tried it out myself and it works.
Here’s a tip for Mint users: you can export your transactions from Mint into Excel (or Google spreadsheet which is what I use) and calculate the XIRR. In Mint, go to the “Transactions” tab and click each investment account on the left vertical menu, one by one, to view the transactions, and export them.
Mint outputs the transaction with the date, amount, a debit/credit called “Transaction “Type”, the fund, and some other information. Mint will label each transaction as a “debit” (sell) or “credit” (buy) in the Transaction Type field, and the “Amount” column will (in my experience) always be a positive number. So, to make the “buys” be positive and the “sells” be negative, what I did was create a new column called “Adjusted Amount” and make it a formula to multiply the amount by -1 if “sell” and leave the number alone if “buy”. For example, if Column E contains the debit/credit, and Column D has the amounts, then in your new “Adjusted Amount” column put this:
=if(E2 = “debit”, D2*-1, D2)
Also, Google has the same XIRR function but Google has you put the Amount range in the first parameter and the Date range in the second parameter, which is the opposite of how Excel does it.
So, after exporting all my Mint transactions, I manually added rows for my starting balances of my various accounts at the beginning of the date period (I started at 1/1/13) and the ending balances at the end of the period. The starting balances I indicated as a “credit” so they would be positive, and the end balances I indicated as a “debit” so they would be negative.
After doing this, I ran the XIRR function and it worked (after I realized and corrected the fact that Google and Excel do the parameters in reverse). At least I assume it worked. My overall portfolio is fairly close to a particular Vanguard target retirement date fund and I found that my calculated YTD return was very close to the Vanguard fund’s YTD return, so I think the number is right.
Actually, a correction to my post. I was wrong about the XIRR function and the parameter order. Both Google and Excel put the numbers first and the dates second. I was mistaken.
Hello Brother,
XIRR DOESN’T SEEM TO WORK. I TRIED THE SAME EXAMPLE AS OF THE SNAP SHOT OT GAVE ME AN ANSWER AS 0.115. CAN U PLEASE GUIDE ME.THANKS
You’re going to have to be a little more specific. Perhaps you have a typo. Try inputting exactly as instructed. It seems to have worked for many other people.
XIRR works fine with me when I have a positive annualized gain. But when I play around with the numbers to try to get a negative annualized gain, it gives me “#NUM!”. any reason for this? thanks,
Adam
I have found that at times you need to change the “guess”. Try -5% for it and it may work out for you.
I’ve been messing around a bit with the function and I was wondering if there is any way you can incorporate the inflation rate into the annualized return rate? In my mind it seems like you can look up the inflation rate of that particular year and subtract that amount from the second Dec 31st entry (positive number) and run the formula as described above. However, just wanted to see if this is possible or correct. Love the site, thanks!
What I do to correct for inflation is I first calculate my XIRR. Then I go to inflationdata.com, get the CPI-U numbers, and calculate out the inflation rate for that time period. Then I subtract it from the return. Voila-after inflation returns. If you just want a ball park figure, use 2% inflation for the recent past, and 3% for the long run.
Ok, I have done that for the annual returns, but is it possible for the annualized return rate? Or am I confused and that is what you mean above?
You can either figure it out as an annualized number or as an annual number. When I have done that exercise, I did it annualized. For example, if you wanted to know the rate of inflation from January 2005 to January 2014, you go here: http://inflationdata.com/Inflation/Inflation_Calculators/Cumulative_Inflation_Calculator.aspx and see that the cumulative inflation is 22.66%. Since that is a period of 9 years, you need to solve for X. 122.66=X^9 It comes out to about 1.023, or 2.3% inflation. Subtract that from your annualized return (XIRR). So if your XIRR was 8%, your after-inflation value is 5.7%.
Can you expand on the calculation to work out the inflation figure please?
I have a cumulative inflation figure of say 16.39% over the last 3 years and 2 months. How do I work out the annual?
I would have done (16.39/38)*12
This is a good place to use the “XIRR” calculation from Excel. Here are your values:
1/1/2010 100
3/1/2013 -116.39
Here’s the calculation: =XIRR(Q7:Q8,P7:P8,4%) = 4.91%
So the annualized inflation figure is 4.91% per year. Your proposed equation would have given 5.18%. Not too far off, but not accurate.
Brilliant thanks. I was just a bit confused when you mentioned “the cumulative inflation is 22.66%. Since that is a period of 9 years, you need to solve for X. 122.66=X^9 It comes out to about 1.023, or 2.3% inflation.”
I get what you’re saying now.
I took a bit of a shortcut. Probably should have XIRRed the inflation too.
Also, I assume you count employee match contributions as regular inflows, correct?
Depends. I could understand an argument either way. Depends on if you look at the match as part of your salary or part of your investment return.
I used to exclude matching funds because I liked seeing the huge number. At one point I even calculated what the difference in take-home pay was and used that instead. The returns were HUGE! But I found that at the end of the day it’s not very helpful because there’s no benchmark you can compare it to.
How to calculate single annualized return for a mixed period within 2 calender year say Oct 2013 to April 2014?
Just use XIRR. It doesn’t matter that you don’t start at the calendar year beginning.
Thanks for yet another awesome post!
Couple of Questions:
1) in your second example you had a more complicated use of XIRR for the partial year of 2007 (and also for the YTD 2011). Is this to show a time-weighted return? The example:
$5000 – 7/11/2007 (acct was opened with $5K on this date)
(5544) – 12/31/2007 (year-end value with no additional contributions)
Your example lists the 2007 return as 10.89% – but to keep the annual returns comparable isn’t it more appropriate to just use the XIRR function which shows 24.36%? Just checking to make sure I’m not missing something….
2) Do you try to also group returns by asset class or account? Or just 1 sheet per account + the grand total sheet? I’m wrestling with how to set this up for my use.
Thanks!
1) The 24.36% is the annualized return. The 10.89% is the actual return I earned. If it had been 12 months at that rate, it would have been 24%.
2) I don’t do it by account, nor asset class. I have each individual investment, plus the retirement/HSA/529 totals, plus the everything total. You can do whatever you want, of course.
Hello,
Thanks for creating such an easy to understand method for tracking investments. I think I have most of this figured out but I do have a couple of questions/clarifications.
1. Is there a formula to figure a better overall actual return for an investment that is started sometime during a year and the current YTD? For example say I started an investment on July 22, 2013 and I want to know what my actual return is as of the current day, say May 8, 2014. I know you could use a straight XIRR calculation, but it seems like there should be a more precise one as you use a different XIRR calculation to determine the total return for 2013 and YTD of 2014 due to those being less than one year. Doesn’t the same philosophy apply in this situation?
2. After setting up a spreadsheet for my different accounts and an overall total, I enjoy it so much I want to set one up for each individual investment within each of those accounts. My question is in regards to moving or rebalancing monies from one investment to another, but still within the same account (410(k)). Here is what I have based of my Quicken Transaction:
Transferred out 2.77 shares at $58.217, for a total of $100.12. Then there is a realized gain of $57.06 which between the two total $157.18.
I figure I need to account for the withdrawal of $100.12, but do I need to-and if so-how, figure in this realized gain for this individual investment when I am moving the monies from this individual investment to another within my 401(k)? And then if so how does this affect, if at all, the investment it was moved to?
Hope that makes sense, I’m new to all this.
Matt
After thinking about it I am pretty sure the answer to #2 is that I count the realized gains as a withdrawal correct?
1) Yes
2) No, you don’t need to figure the realized gain. The calculation figures all that out. All you need is money in and the date and money out and the date. It doesn’t affect the investment it was moved to.
Thank you for the quick response.
So if I wanted to figure a precise XIRR, for example, from the dates March 21, 2008 to May 10, 2014 would the formula look like this:
=SUM(((1+XIRR(D42:D181,E42:E181,10%))^(((DATE(2014,5,10)-(DATE(2008,3,21)))/2555))-1))
I figure you need to change the number of days from 365 to 2555 (number of days in 7 years, NOT figuring in leap years at this point) for this to work? Am I correct in this?
Thanks again,
Matt
XIRR is an annualized return. What kind of return are you trying to get? If you want an XIRR for those dates, use the simple formula:
=XIRR(D42:D181,E42:E181,10%
The only time I mess with the formula is if I want a YTD return or some period less than one year.
Bear with me as I am new to all this stuff and here is the way I am thinking about it:
If you adjust for YTD or holding an investment for a time shorter than a year, why would you not want to adjust for the “longer” picture as well?
For example, let’s say I have an investment that I have held since 9/10/09 and I want to know what my rate of return as of this point today, 5/10/14 is. If I did a simple XIRR calculation then I would come up with a percentage that could be considerably than using my “adjusted” formula. Using the basic XIRR calculation, does it not figure that I held this investment for an additional 252+129=381 days, which would skew the numbers as it would appear I have help this investment for an additional year?
I am probably totally of base here, so hopefully you can straighten me out!!
So you want the total return, not an annualized return, right? I’m not sure why, but I think you’re doing it the right way.