Clicky

  • Welcome to P2P Lending / NFT Lending Forum.
 

ETH.LOAN

News:

This was the original Lend Academy peer-to-peer lending forum, since forensically restored by deBanked and now reintroduced to eth.loan.

To restore access to your user account, email [email protected]. We apologize for errors you may experience during the recovery.

Main Menu
NEW LOANS:   | 804.eth 2.500 Ξ | remoraid.eth 0.299 Ξ | remoraid.eth 0.299 Ξ | ALL

Yearly and overall XIRR spreadsheet example provided

Started by Peter, April 05, 2016, 11:00:00 PM

Previous topic - Next topic

lascott

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/" class="bbc_link" target="_blank">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" class="bbc_link" target="_blank">http://i.imgur.com/ructk7r.png
https://forum.lendacademy.com/proxy.php?request=http%3A%2F%2Fi.imgur.com%2Fructk7r.png&hash=483bf5e6190d611dbb49339b34b787c2" alt="" class="bbc_img" />

https://forum.lendacademy.com/index.php/topic,3365.msg33494.html?PHPSESSID=6d60ea0c0baa48cf9270523b3effefad#msg33494">Quote"> from: BruiserB on April 06, 2016, 11:38:52 AM

RaymondG

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

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

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.





BruiserB

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:



investny

I think ideally we would want to see returns data in a format similar to this
http://screencloud.net/v/1T7P" class="bbc_link" target="_blank">http://screencloud.net/v/1T7P

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

NEW LOANS:   | 804.eth 2.500 Ξ | remoraid.eth 0.299 Ξ | remoraid.eth 0.299 Ξ | ALL