Author Topic: Back Testing Traded Notes  (Read 2166 times)

CircleT009

  • Jr. Member
  • **
  • Posts: 91
    • View Profile
    • Email
Back Testing Traded Notes
« on: October 24, 2016, 12:40:23 PM »
All,

I am wanting to back test my traded notes.  Meaning, I want to see if I made a good decision selling the notes I have sold on FolioFN.  I have downloaded the traded notes csv file and trimmed it down to a more manageable file size.  I have sold approx 3,700 notes (some are duplicates, for a while I would buy 4 or 8 $25 loans as opposed to 1 $100 or $200 loan).  I have tried downloading the info I need to do a vlookup from LC's website, but my Macbook can not handle it and excel crashes.  I also do not want to click on 3,700 url addresses to look them up individually. 

Basically I want to know if a note that I sold charged off or went into default and when. 

If someone has the ability/wants to do this for me, I can email you my traded notes csv file.  I am sure we can come up with a way for me to say thank you for your time.

Or if anyone knows of an easier way of doing this I would appreciate the help.

Thanks

rawraw

  • Hero Member
  • *****
  • Posts: 2746
    • View Profile
Back Testing Traded Notes
« Reply #1 on: October 24, 2016, 12:56:11 PM »
Ha you've sold more loans than I've bought! Interest Radar used to do this for users. Not sure if it's still around post merger

Fred

  • Hero Member
  • *****
  • Posts: 1421
    • View Profile
Re: Back Testing Traded Notes
« Reply #2 on: October 24, 2016, 12:59:04 PM »
Have considered loading your traded notes and "the info I need to do a vlookup from LC's website" into DB tables, and then do SQL joins to your heart's content?

brycemason

  • Hero Member
  • *****
  • Posts: 801
    • View Profile
    • P2P-Picks.com
    • Email
Re: Back Testing Traded Notes
« Reply #3 on: October 24, 2016, 01:07:40 PM »
If your csv has date of sale, you could download the payment history file, keep all cash flows after the date of sale for each loan, calculate a present value at the time of each sale using a discount rate, sum them all up and compare to your proceeds from sale. If proceeds exceeds the present value, you won. The payment history file is over 4GB, so you would need a proper statistical package or load it into a database.

CircleT009

  • Jr. Member
  • **
  • Posts: 91
    • View Profile
    • Email
Re: Back Testing Traded Notes
« Reply #4 on: October 24, 2016, 01:40:53 PM »
rawraw - I will try interest radar.

Fred and brycemason - My computer will just not handle the raw file I need to download from LC.  I only have excel.  I know how to run the analysis and formulas I need, but just can not get to the raw data.


CircleT009

  • Jr. Member
  • **
  • Posts: 91
    • View Profile
    • Email
Re: Back Testing Traded Notes
« Reply #5 on: October 24, 2016, 01:46:56 PM »
Ha you've sold more loans than I've bought! Interest Radar used to do this for users. Not sure if it's still around post merger

Nope.  Just another website that will not support how big my csv is.  Upload failed.

brycemason

  • Hero Member
  • *****
  • Posts: 801
    • View Profile
    • P2P-Picks.com
    • Email
Re: Back Testing Traded Notes
« Reply #6 on: October 24, 2016, 01:50:14 PM »
Here's an idea. Make a file with the loan IDs you want to extract from the payment history file. Download the payment history file. Use grep and a redirect to a new file that will extract only those rows with one of the IDs matching your list. This would not require very much in the way of computing resources as grep just reads in the file line by line. Then you could easily open the new file in Excel.

CircleT009

  • Jr. Member
  • **
  • Posts: 91
    • View Profile
    • Email
Re: Back Testing Traded Notes
« Reply #7 on: October 24, 2016, 04:19:44 PM »
Ok, thanks to all who helped.  I thought some people might be interested to see the results.

I was able to do my analysis (3-4 hours of manual excel manipulation), oh well.

Here is a summary of the numbers.

I have sold 3,762 loans. 

Total loss at time of sale (transaction price - transaction fee - o/s prin - accrued int) = $44,112

Total cash loss (payments + transaction price - transaction fee - investment) = $26,457

Loss at time of sale on notes that became current or fully paid = $5,142 (should not have sold these notes)

Cash loss on notes that became current or fully paid = $448

Loss at time of sale on notes that charged off or defaulted = $34,796

Cash loss on notes that charged off or defaulted = $23,529

Net cash Proceeds on notes that charged off or defaulted = $18,260

I did not include amounts relating to loans that have been sold that are currently in grace period, late 16-30 or late 31-120.

My conclusion is that I cost myself $5,142, by selling notes I should not have sold.  But I saved myself $18,260, by selling loans prior to charge off or default.  So over a 7 year period I have increased my return by a new amount of $13,118.  I would have also made a bit more interest on the loans I sold that I should not have, but does not look like it would have been a big amount.

So my hunch was correct.  The extra work is worth it.  I always figured the extra work was worth it as my returns have always been better than the LC averages.

Thanks again for the ideas and help.




Fred93

  • Hero Member
  • *****
  • Posts: 2063
    • View Profile
Re: Back Testing Traded Notes
« Reply #8 on: October 24, 2016, 04:25:13 PM »
What is your note sell strategy?

CircleT009

  • Jr. Member
  • **
  • Posts: 91
    • View Profile
    • Email
Re: Back Testing Traded Notes
« Reply #9 on: October 24, 2016, 04:41:09 PM »
What is your note sell strategy?

Once a week I review every note in my portfolio that is not current.  Try and determine the ones that are bad and sell them at a discount.  Don't sell ones that look ok.  I increase the discount as they move from in grace period to 16-30 to 31-120.  Simple but takes time.

I have never used an automated tool to purchase my loans and I have never used an automated tool to sell my loans. 

Fred

  • Hero Member
  • *****
  • Posts: 1421
    • View Profile
Re: Back Testing Traded Notes
« Reply #10 on: October 25, 2016, 01:49:23 AM »
I have never used an automated tool to purchase my loans and I have never used an automated tool to sell my loans.

You seem to have a proven method of selling notes, why not automate to amplify the volume (and profit)?

CircleT009

  • Jr. Member
  • **
  • Posts: 91
    • View Profile
    • Email
Re: Back Testing Traded Notes
« Reply #11 on: October 25, 2016, 09:17:59 AM »
I have never used an automated tool to purchase my loans and I have never used an automated tool to sell my loans.

You seem to have a proven method of selling notes, why not automate to amplify the volume (and profit)?

I do not sell loans at a profit.  I only sell loans that are "going bad".

Emmanuel

  • Full Member
  • ***
  • Posts: 157
    • View Profile
    • LendingRobot
Re: Back Testing Traded Notes
« Reply #12 on: October 31, 2016, 11:58:53 AM »
Still, you could automate the selling of loans going bad.