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
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
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?
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.
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.
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.
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.
What is your note sell strategy?
Still, you could automate the selling of loans going bad.