Author Topic: Yearly and overall XIRR spreadsheet example provided  (Read 5510 times)

lascott

  • Hero Member
  • *****
  • Posts: 1430
    • View Profile
    • Appreciate my post and want to try LendingRobot? URL below
Yearly and overall XIRR spreadsheet example provided
« on: April 06, 2016, 10:31:56 PM »
I've been using XIRR to calculate my LendingClub investment in a couple of accounts. I was calculating an overall (from inception) value. The quote at the end of this post made me interested in a yearly value but I wanted to use the same spreadsheet. Found a great example (and downloads) of this at: http://whitecoatinvestor.com/how-to-calculate-your-return-the-excel-xirr-function/

Coloring is mine and grey colored boxes are my comments.
Image: http://i.imgur.com/ructk7r.png


<snip>
I added my 2016 XIRR number....my own calculation of year to date yield, because I don't think the other numbers really show the whole picture of what's going on.  I have seen a real downturn in performance...particularly in my IRA.  The XIRR actually fluctuates a fair amount depending on whether notes in default have been charged off or not.  That is on the 14th and 29th of the month it will be higher, and then plummet on the 15th and 30th/31st when the notes actually charge off.

But all of our ANAR numbers are since the inception of our accounts....not a YTD number, so they will vary depending on how long we've each had accounts.  My standard account has been open since 2010 and my IRA since 2013, so both have several years of good performance.  So I'm not sure how valuable it is to compare ANARs.  It would be better to compare YTD XIRRs, but since each of us would have to individually calculate it there's a lot more room for error.
<snip>
« Last Edit: April 07, 2016, 09:13:19 AM by lascott »
Tools I use: (main) BlueVestment: https://www.bluevestment.com/app/pricing + https://www.interestradar.com/ , (others) Lending Robot referral link: https://www.lendingrobot.com/ref/scott473/  & Peercube referral code: DFVA9Y

RaymondG

  • Full Member
  • ***
  • Posts: 247
    • View Profile
    • Email
Re: Yearly and overall XIRR spreadsheet example provided
« Reply #1 on: April 07, 2016, 01:15:22 AM »
Comments on lascott's example: the numbers between during a year are net withdrawals and deposits. In the setup of this example, withdrawals are negative and deposits are positive. By the way,

jz451

  • Full Member
  • ***
  • Posts: 118
    • View Profile
Re: Yearly and overall XIRR spreadsheet example provided
« Reply #2 on: April 07, 2016, 01:24:37 AM »
Wouldn't it be easier to create an automated moving average each month for say 3,6,12 months to get a better representation of when returns are increasing, decreasing, or changes in strategy?

AnilG

  • Hero Member
  • *****
  • Posts: 1100
    • View Profile
    • PeerCube
Re: Yearly and overall XIRR spreadsheet example provided
« Reply #3 on: April 07, 2016, 02:25:54 AM »
Why not just calculate returns on monthly basis? As /u/jz451 mentioned, you can calculate moving average as well as monthly, quarterly, yearly or any period returns that you want from monthly returns. By using month, you will also be able to avoid 365/366 day per year issue that your current spreadsheet has. This is the way we calculate yearly and monthly returns at PeerCube using data from monthly statements.
« Last Edit: April 07, 2016, 02:30:56 AM by AnilG »
---
Anil Gupta
PeerCube Thoughts blog https://www.peercube.com/blog
PeerCube https://www.peercube.com

dompazz

  • Full Member
  • ***
  • Posts: 224
    • View Profile
Re: Yearly and overall XIRR spreadsheet example provided
« Reply #4 on: April 07, 2016, 08:51:56 AM »
Why not just calculate returns on monthly basis? As /u/jz451 mentioned, you can calculate moving average as well as monthly, quarterly, yearly or any period returns that you want from monthly returns. By using month, you will also be able to avoid 365/366 day per year issue that your current spreadsheet has. This is the way we calculate yearly and monthly returns at PeerCube using data from monthly statements.
I agree but would take it a step further.  This is what I have on my list to do for my portfolio.

Long term, IRR provides you a good sense of how your money has performed -- ie it is a money weighted return.  This is the return that your money actually saw.

If you are looking to see how you/the market is doing through time, you really want a time weighted return (TWR).  You probably want to calculate a monthly return using something like Modified Dietz (https://en.wikipedia.org/wiki/Modified_Dietz_method).  This is a hybrid return calculation that takes into account timing of cash flows but is closer to a pure TWR (without cash flows, it is a twr).

Calculate monthly performance with modified Dietz.  Chain them together with your typical (1+r1)(1+r2)...  You can plot the chart of cumulative returns.  That is going to give you something like looks like a typical stock chart and help you understand where down turns occurred (always because of the market, never because we picked bad notes).

On a side note, if you are looking to launch a fund to institutional investors they will probably want a return in compliance with GIPS (http://www.gipsstandards.org/Pages/index.aspx).  GIPS is going to require you to do something like the above.

lascott

  • Hero Member
  • *****
  • Posts: 1430
    • View Profile
    • Appreciate my post and want to try LendingRobot? URL below
Re: Yearly and overall XIRR spreadsheet example provided
« Reply #5 on: April 07, 2016, 09:14:44 AM »
Comments on lascott's example: the numbers between during a year are net withdrawals and deposits. In the setup of this example, withdrawals are negative and deposits are positive. By the way,
The author set it up that way because that is what the XIRR function expects. My color coding was supposed to show the range of cells that went into XIRR formula for each year.
Tools I use: (main) BlueVestment: https://www.bluevestment.com/app/pricing + https://www.interestradar.com/ , (others) Lending Robot referral link: https://www.lendingrobot.com/ref/scott473/  & Peercube referral code: DFVA9Y

lascott

  • Hero Member
  • *****
  • Posts: 1430
    • View Profile
    • Appreciate my post and want to try LendingRobot? URL below
Re: Yearly and overall XIRR spreadsheet example provided
« Reply #6 on: April 07, 2016, 09:23:25 AM »
Wouldn't it be easier to create an automated moving average each month for say 3,6,12 months to get a better representation of when returns are increasing, decreasing, or changes in strategy?
I do like a monthly view BUT I was just showing a handy yearly XIRR example that I found and wanted to share with others to save them time. Hope that helps explain.

Why not just calculate returns on monthly basis? As /u/jz451 mentioned, you can calculate moving average as well as monthly, quarterly, yearly or any period returns that you want from monthly returns. By using month, you will also be able to avoid 365/366 day per year issue that your current spreadsheet has. This is the way we calculate yearly and monthly returns at PeerCube using data from monthly statements.
I would like that view in peercube as well as an annualized one.  I tried using it a while back but when I made a mistake it was too cumbersome to clean up since I could just delete errored entries.  I know you have other prioritize and hope you get back to that interface in the future.  If entries are deleted just unload and reload the data behind the scenes to recalculate. There are only dozens of rows/records.

I do keep my monthly data in a spreadsheet but it is pretty crude and was mainly looking at charge off percentages when Rob L had a few post on them and patterns. I'd be happy to play with other excel/sheets examples that folks have (which are shown/sent sanitized)

Image: http://i.imgur.com/x62cxcs.png
« Last Edit: April 07, 2016, 09:59:20 AM by lascott »
Tools I use: (main) BlueVestment: https://www.bluevestment.com/app/pricing + https://www.interestradar.com/ , (others) Lending Robot referral link: https://www.lendingrobot.com/ref/scott473/  & Peercube referral code: DFVA9Y

jz451

  • Full Member
  • ***
  • Posts: 118
    • View Profile
Re: Yearly and overall XIRR spreadsheet example provided
« Reply #7 on: April 07, 2016, 11:14:37 AM »

Image: http://i.imgur.com/x62cxcs.png


Am I reading this right where it shows a whopping 53% charged off last December?

BruiserB

  • Sr. Member
  • ****
  • Posts: 418
    • View Profile
Re: Yearly and overall XIRR spreadsheet example provided
« Reply #8 on: April 07, 2016, 01:38:15 PM »
I actually calculate my returns using the EXACT method you posted.  I can't remember how I originally came up with the spreadsheet, but likely I came across the same example you posted here.  I did find it through this forum.  EDIT: Actually I just noted one difference...what I call YTD return on my spreadsheet and results below is what is referred to as the current year's Annualized Return on the example....it's my effective return rate as of today so far this year.  I don't see any reason to do the calculation done in the example as their YTD number.

One could do monthly simply by putting in a withdrawl and deposit of end of month balance at the end of each month.  Up until now I found it sufficient to just calculate yearly as the returns were quite consistent.  But now I have had a lot more defaults the last few months, so perhaps more frequent analysis is warranted.  I also plan to use NickelSteamroller's tools to see which of my investment methods are struggling.  I save each "rule" I use to invest to a separate portfolio so I can see which portfolios are struggling.  I just haven't had the time to look closely in a while.  When I look at my list of loans in default, it's not obvious that any one portfolio is having the biggest issue so with just a quick glance, it seems across the board.

Here are my account XIRRs:

« Last Edit: April 07, 2016, 01:51:48 PM by BruiserB »

lascott

  • Hero Member
  • *****
  • Posts: 1430
    • View Profile
    • Appreciate my post and want to try LendingRobot? URL below
Re: Yearly and overall XIRR spreadsheet example provided
« Reply #9 on: April 07, 2016, 04:35:36 PM »
Image: http://i.imgur.com/x62cxcs.png
Am I reading this right where it shows a whopping 53% charged off last December?
That is correct. I just double checked. I think I invested a bunch of money in Jan and last summer so I had many notes at roughly the same age. Or some bad luck. Others numbers look OK tho.

Here is my ROTH IRA account:
Image: http://i.imgur.com/LUFUuOv.png

« Last Edit: April 07, 2016, 04:47:49 PM by lascott »
Tools I use: (main) BlueVestment: https://www.bluevestment.com/app/pricing + https://www.interestradar.com/ , (others) Lending Robot referral link: https://www.lendingrobot.com/ref/scott473/  & Peercube referral code: DFVA9Y

investny

  • Jr. Member
  • **
  • Posts: 90
    • View Profile
Re: Yearly and overall XIRR spreadsheet example provided
« Reply #10 on: April 28, 2016, 01:48:47 AM »
I think ideally we would want to see returns data in a format similar to this
http://screencloud.net/v/1T7P

Not sure why LC cannot just code something like this into their site.