Put your Money Where your Mouth Is (Calculating XIRR)

 

Hanging out in the doctors lounge, I’ve heard my share of big fish tales about investments.  One guy, the other day, was saying how he killed it on some little know stock he bought last year.  It is great that people are making money on their investments, but the thought I have when I hear a big success story on an individual investment is,

Yeah, but how are all your investments doing?

The few times I have asked these “big winners” what their internal rate of return was for the year, I usually get a glassy-eyed stare and then the question,

What’s that?

Enter the Irregular Cashflow (or Extended) Internal Rate of Return(XIRR). XIRR is a function of Excel that calculates your rate of return based on a starting and ending portfolio value and the contributions or withdrawals (i.e. cash flow) into and out of the portfolio. By calculating the XIRR, we are able to get a truthful snapshot into our investment returns progress.  If we aren’t keeping track of this, then we are just lying to ourselves if we think we have a clear picture of our investment returns.

How does this affect you?

So you are a high income earner. You’ve paid off any bad debt (credit cards, loan sharks, etc) and you are starting to put some money aside for retirment. As you get further along in your investment career, you will inevitably have more and more accounts to keep track of. When you start out in your career, you might have a 401K and a Roth IRA. Then you change jobs. You don’t rollover that earlier 401k, but you have another 401k at your new job now.

Then you start to save a bit more and want to start a taxable account. You open that, but then another broker has a bonus contribution if you deposit a certain amount in their account and you transfer a part of your money to the new account. Don’t forget your spouse. He/She might be going through the same thing.  Before you know it, you have 8-10 accounts that you are tracking.

You could combine those accounts, but that isn’t always possible. How do you keep track of all the accounts and all the investment vehicles in each account?  

How do you keep track of your accounts?

You could use a daily financial tracking site like Personalcapital.com or Mint.com. I definitely recommend that you track your financial accounts on platforms like those. It is impossible to beat the simplicity of having all your accounts in one place.  They do a great job with those programs, but they still have accounts that aren’t followed from time to time. That can prevent you from seeing a complete picture.

The only way I have been able to know exactly what my rate of return is at any give time, is to calculate it myself.  It is pretty easy to do, and I’ve made it even easier for you by offering the XIRR calculator when you sign up for our email list.  The nice thing about XIRR is you don’t have to have regularly timed contributions. You can put more money into three or four accounts one month and only deposit in one or two accounts the next. The XIRR function keeps track of your return. All you need is the dates you made deposits or withdrawals and the contribution amounts. 

Lets go through the calculator.

calculating XIRR

This is the entire calculator.

 

I’ve tried to make it as easy as 1. 2. 3.

  • First, you put in the starting portfolio amount and the day you want to start tracking. In this case I’ve put in the starting value as $50,000 on 1/1/2017.

calculating XIRR

  • Second, you put in the values of your deposits or withdrawals from the portfolio (i.e. cashflow) into the fields below (highlighted in pink). You can update these throughout the year. Just save your file each time so the field values are recorded. In this example I’ve entered a $1,000 contribution on 3/4/17. Then I took out $500 from my overall portfolio on 3/5/17 (that is why it is a negative value) and then I added another $550 on 3/31/17.  

calculating XIRR

  • Third, I entered the value of my overall portfolio added up from personalcapital.com as a negative number on the date I want to calculate the XIRR.  In this example my portfolio was worth $52,000 on 4/2/17. If you don’t use one of the sites like personalcapital.com or mint.com you can add up all your accounts manually. 

calculating XIRR

  • Finally I look at the XIRR and Year-to-Date Return values.

calculating XIRR

  • The XIRR returns is annualized. That means if your portfolio continued on the current rate of return, this is what you could expect over the year.  
  • If you are tracking multiple years, you will still get an annualized rate of return.
  • The Year-to-date value give you the real return as of the date you entered in the Current Value of Portfolio date cell. 
  • In this example my XIRR was 7.81% and the YTD Return was 1.95%

Pretty easy right? If you didn’t follow, try to read it again. If this is still too crazy to follow, then feel free to email me, and I will gladly explain it until you understand. 

Internalizing it 

I use personal capital to keep track of the values of all my accounts because it is a one-stop-shop. They are pretty accurate, but sometimes there is data delay by a day or so. For some reason they can’t access my HSA account, and I include that in my investment portfolio since I’m saving that until retirement.  I have to access this account on my own, but it sure beats having to login to ten different platforms to see my account balances.  

You don’t need to do this everyday. I would say once a month, or once every other month is fine as long as you can remember your deposits and withdrawals to all of your account. 

Does this relate to our children?

We, the parents, have to get our own finances in order if we are to teach are children how to do it right. If we aren’t keeping track of our investment activities and calculating our return in this way, then we have no idea if we are meeting our goals.  How can we teach our kids to invest and meet financial goals if we don’t have any idea where we are?

It is easy to lie to ourselves about our financial wellbeing. Sometimes we need to take a good hard look at where we are. We need to look at the amount we spend on eating out, gasoline, groceries, coffee and entertainment. We need to go through the exercise of calculating the cost of each meal we eat at a restaurant and what that meal would have cost if we cooked it at home periodically.  Doing things like this give us a bit of a wake up call and keeps us accountable to ourselves financially.

Take Home

It is pretty easy to track your XIRR once you set up the calculator. To get the calculator, just join the email list by filling in the form at the end of the page. The download link with be in the final welcome email.

Once a month, or every other month, enter your contributions and the dates. Add up what the portfolio is worth and the calculator does the rest. 

Now sit down sometime this week and try out the calculator and figure out your XIRR for last year. If you don’t have all the numbers, it is pretty early in the year. You can make this year your first time to track XIRR. 

How do you keep track of your investments? Is there anything you do that helps keep you accountable? How are you going to teach your kids to keep track of their financial progress?

Did the calculator help? How can I improve it? Let me know in the comments and if you don’t understand anything, please email me. We can walk through it together. 

Start tracking your expenses, deposits, and investments with Personal Capital. It’s a great way to keep track of all your accounts in one easy platform. I use it to track my investment returns and overall net worth. Just click the link to the right and start your account today. The best part is it’s all free. There is nothing else on the market today that is as powerful and easy to use. 

Tom
Tom is a doctor, husband and father of five with a passion for parenting and finance. When he isn't skateboarding, riding BMX, or jumping on the trampoline with his kids, he is reading and writing about personal finance. He helps high income parents educate and mentor their kids to become financially, emotionally, and intellectually self sufficient.

6 Responses to “Put your Money Where your Mouth Is (Calculating XIRR)

  • Nice post. I’ll admit, I actually don’t really keep track of my investment returns. Sure, Fidelity gives me a number when I login, but I’m not sure how accurate it is. I’m confident in my asset allocation, and it really doesn’t matter to me if it does really well one year or really poorly. If you are trading though, tracking your return is a must.

    • That is a good point. If you are comfortable with your allocation and wouldn’t change anyway then I guess you don’t need to know the number. I like to do it to make sure that fidelity or personal capital or whoever you are using matches up with my numbers though. Plus sometimes my online trackers have missed data and I don’t get a true picture. Like when I moved a big portion over to Merrill to get a $600 bonus, I lost about a weeks worth of data then on personal capital and mint.

  • chris
    7 months ago

    I registered but don’t see a download link

    • Did you get a final welcome email? It should have been a link in that email. If not, I will email it to you personally. Thank you for registering.

  • chris
    7 months ago

    Got it thanks. Put it in my junk mail. I confirmed as was able to download it.

    • Okay, awesome. Sorry it went to junk mail. Sometimes those filters are sensitive. Let me know if you have any questions. Have a great week.

Follow on Feedly