P2P Lending / NFT Lending Forum

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


Quote"> from: BruiserB on April 06, 2016, 11:38:52 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,

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?

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.

from: AnilG on April 07, 2016, 02:25:54 AM

from: RaymondG on April 07, 2016, 01:15:22 AM

from: jz451 on April 07, 2016, 01:24:37 AM

from: lascott on April 07, 2016, 09:23:25 AM

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:

from: jz451 on April 07, 2016, 11:14:37 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.