Peer To Peer Lending Returns Look Terrible

I recently came across a dataset on Kaggle from a popular peer to peer lending company with data up to 2/2019–the start date was in 2007 but there weren’t that many loans from the early years. I’ve been reading other FIRE/personal finance bloggers touting more non-traditional investments, and I was a little bit interested to see the overall average performance of these kinds of instruments. Sometimes you never know if the bloggers are just pushing a product because it’s good or if they’re after ad money. So to find out, I decided to fire up R and run some quick analysis on the returns and my goodness they are very terrible.

36 Month Loans

Looking at 36 month (3 year) loans that have had at least 3 years between the start date and the data set’s end date, the returns are terrible. The only thing nice I can say is that at least it looks like you average a slightly positive return.

Grade# of Loans# BadTotal ReturnAvg Int Rate
A1557625.5%107.1%7.2%
B23083511.3%108.8%10.8%
C17555018.2%108.4%13.9%
D8128623.7%107.7%17.2%
E2342529.1%105.8%19.8%
F482933.7%104.2%23.4%
G76639.3%102.1%24.4%
A 7% return in 3 years?!?! What a terrible club to be in.

That total return is the total amount paid out over the life of the loan. A total return of 107.1% is basically an interest rate of 2.28% per year compounded each year. That’s pretty close to what an Ally CD would yield for the same period, which is pretty sad considering the amount of risk you’d be taking on to give out these loans to people. % Bad is the percentage of the loans that have negative statuses like charged off, grace period, or late.

How does this number work?

Screenshot from this company’s page. Captured 7/27/2020

Okay so I was thinking maybe I made a calculation mistake or the data on Kaggle was bad, so I went over to this company’s website to get the official public stats. They don’t make sense to me either. This company only offers loans of 36 months (3 years) or 60 months (5 years). So I picked issue dates up to 2014, so all the loans are paid off or charged off–nothing is outstanding.

Looking Grade A loans, their website has total payments of $740,746,894 = $669,115,583 (principal payments) + $71,631,311 (interest payments).

They issued $686,112,100 in loans. Simple division puts this return at $740,746,894 / $686,112,100 = 1.0796 meaning they got paid 7.96% more for loans that took 3 to 5 years to complete.

How in the heck do you get an annualized return of 5.14% over 3 to 5 years when the total return is 7.96%? None of this makes any sense to me, but maybe my untrained eye has just grossly misunderstood the basic figures on the dashboard.

Dropping Microsoft Office At Home

I recently got a new computer, and decided to go back to the Microsoft Home Use Program to get a new key to get Excel installed on my new computer. Much to my surprise they’ve completely gutted that program and the package now costs $70 / year for one license or $100 / year for 6 licenses. This is a huge increase when it was only $20 for a permanent license as recently as 2018. Now my old computer is completely gone, so I kind of gave up on rescuing my old license some how.

Free Alternatives : LibreOffice, Rstudio, and Google Sheets

As you know from this website, my typical data analysis toolkit is Excel, R and PHP/Javascript, so I’m currently exploring the open source options. So far, I’m really getting used to LibreOffice. LibreOffice is related to OpenOffice, but has been kept more up to date than OpenOffice. It looks like OpenOffice has basically been frozen in time for like 5+ years and really isn’t a great option at this point. A lot of the functions are the same as in Excel, but the only bad part about LibreOffice is that it feels a lot like using Excel 2003 from the UI perspective. It just isn’t as slick and fluid as the newer versions of Excel, but it does seem to be getting the job done. Pivot tables work, and the structure is very similar to how they are used in Google Sheets.

libreoffice screenshot
LibreOffice looking very 2005.

I’ve also started to lean a little bit more heavily on R (specifically RStudio) to do exploration via coding vs spreadsheet style. It certainly has been a learning curve, but I also feel like R is a marketable skill. I’ve also been using Google Sheets quite a bit for smaller spreadsheets, but Google Sheets feels a little clunky if you try to use a sheet that’s large.

Is the Microsoft Home Use Program worth it for some people?

Yes, it is worth it if you are going to use the 1 terra byte in free cloud back up. If you’re a light user of Docs, Excel and PowerPoint, LibreOffice or Google Docs should cover you pretty well for free.

If you’re trying to run a business from home, I could also see it being useful in situations where you’re going to be sharing files with clients or contractors. I can see it being important to make sure that your files are formatted perfectly across computers. For me, since my hobby data analysis work isn’t really shared via Excel, I don’t think that it’s worth an extra $70 / year for that.

How much extra does Instacart delivery cost versus Kroger pickup?

With all the coronavirus stuff, I’ve started to use Instacart more to shop at my local grocery store Marianos which is owned and run by Kroger.

With my latest cart, I was curious how much Instacart was charging versus the store. There’s a little vague note that I’m getting the Everyday Store Price, but I’m not sure what that actually means in practice. Instacart defines it as:

Standard store pricing. Loyalty cards not accepted. Most in-store sales, promotions, offers, coupons and discounts do not apply. Instacart specific coupons, promotions and discounts available. Price as displayed. 

The Instacart total
The Marianos / Kroger total.

So let’s get started, this is what my final Instacart receipt looked like with Everyday Store Pricing. $201.65 on products and tax, and then the remainder $44.26 on service, tip, and delivery fees.

Compare this to my Kroger pick up total of $194.45 for products and taxes. I added every single thing–including replacements–from my finalized Instacart order to the Kroger Clicklist order. Kroger pickup will give you the sale prices which is the cause of the difference between the Kroger subtotal and the Instacart subtotal. Because of this, I missed out on some BOGO bread, which I’m a little sad about now that I know about it.

There’s something odd about the tax total that I don’t understand how the Instacart value has lower taxes with a higher subtotal compared to the Kroger tax total on a lower subtotal. I’d imagine that there’s probably something wrong going on with Instacart’s algorithm that they’ll get in trouble over in the future.

So because Instacart charges about $10 more for the same products, I am in essence paying an even bigger premium for the grocery delivery than I originally expected.

In total adding up everything I basically paid $51.46 or a premium of almost 20% for someone else to deliver for me. versus if I had a Kroger employee shop for me and I picked up. Granted I could have shorted the tip a little bit (from 15%), but that seemed rude to short change some when I personally didn’t want to risk getting infected by going shopping myself. But you know, there’s definitely a lot of cost savings to be had if you’re willing to do the pick up.

Edit 4/26: We just got an Instacart from Aldi, and the shopper left the receipt. The total from Aldi was $188 with tax, and Instacart charged $207 for the products + tax, so the hidden Instacart price inflation ray was about 10%.

The data & privacy extract from Intuit’s Mint is mostly useless

Months ago, I requested a copy of my Intuit data to see what’s been tracked through my Mint account the last 12 years I’ve had it open. Well they weren’t kidding that it’d take up to 45 days to process the request. I requested it in early Feb and I finally got the download link in mid-March! amazing over a month to make a data dump!

What was inside?

After over a month of waiting, I ripped open the download to find that it’s a bunch of text files saved in JSON format. There wasn’t anything useful inside of the files. The budget data was unlabeled, and transaction data was unlabeled. Here’s an example of it looks like after I formatted the extracts in R so I could actually read them.

I think this is transactions but it’s really light on details, and IDK how I would have so many.

There’s a lot data in the files, but a lot of it is actually 100% unlabeled. I’m not sure what Intuit was thinking, but the data is completely unusable and doesn’t even match the “Export All Transactions” button that’s on Mint.com on the transaction list button.

How to read your Data.

If you’re familiar with R, I basically used the step below to read thru most of the data.

The main file that looked like it had some meat was 10+mb in my extract and located in the Mint sub-folder and had a name like mind_data_78438432894932482339jdjfd.txt. Here’s the code I used to read the data in R:

library(rjson)

#update the working directory and mint_data_
setwd("Your working directory")
con <- file("mint_data_YOUR FILE NAME.txt",open="r")
#read in the file, there might be an error
line <- readLines(con) 
#the main file has a bunch of JSON objects, mine had 22, So this step splits them up 
split_vars <- strsplit(line, "\\]")
#22 sub objects, I basically just went thru each one by updating X and re-running to see what was in it
x <- 1
mint_data <- as.data.frame(do.call(rbind, fromJSON(paste0(split_vars[[1]][x],"]"))))

For my data extract, these were what I think I saw in the extract:

  • #1 Personal Info: Most of it was wrong though
  • #2 Opt ins
  • #3 User actions?
  • #4 IDK what this is
  • #5 Something about banks mostly empty
  • #6 Something about stocks has my old stocks
  • #7 Stocks
  • #8 Budget, but unlabeled
  • #9 Goals, I don’t use these but the data looked clean
  • #10 Transaction types
  • #11 Transactions
  • #12 Account information
  • #13 Loans
  • #14 Something about credit card balances
  • #15 ?
  • #16 Currency
  • #17 Account ids
  • #18 Currency?
  • #19 Currency?
  • #20 Credit cards?
  • #21 Currency?
  • #22 Accounts and status

How to get a copy of your Data

If you’re still interested to see if maybe you’ll have better luck than me, here’s how you request a copy of the data they have. You can navigate to this page from Mint by clicking on Settings > Intuit Account > Data & Privacy > Request an extract of your data

Yay I have data to download.

Historical Ally 12 Month CD Rates 2010-2020

Graph of the Ally Bank CD rates (APY) vs the Federal Funds Rate and National Average CD Rate.
Graph of the Ally Bank CD rates (APY) vs the Federal Funds Rate and National Average CD Rate.

I included the Federal Funds Rate (source: St Louis Fed, Federal Funds Rate) as a comparison as this is the rate that banks lend to each other. You can see the spike in Ally Bank rates generally following increases and decreases with changes to the Federal Funds Rate. So if you’re on the fence about figuring out when is a good time to lock in a rate with Ally Bank on a CD, just look at how the Federal Funds rate has been trending.

In addition, I included the national average for the 12 month CD rates (source: St Louis Fed Avg 12 Month CD). This average seems to be less responsive to the Federal Funds rate, but still shows some similar movement.

Table of Ally 12 Month CD Rates 2010-2020

DateAPY Source
6/6/20101.49%Web Archive
7/17/20101.48%Web Archive
12/5/20101.29%Web Archive
12/26/20101.28%Web Archive
4/26/20111.23%Web Archive
6/22/20111.20%Web Archive
9/25/20111.12%Web Archive
11/30/20111.01%Web Archive
12/31/20110.99%Web Archive
4/21/20121.05%Web Archive
5/4/20121.04%Web Archive
7/25/20121.02%Web Archive
10/5/20121.04%Web Archive
11/27/20121.04%Web Archive
12/28/20121.04%Web Archive
2/9/20130.99%Web Archive
6/3/20130.94%Web Archive
7/30/20130.94%Web Archive
8/8/20130.94%Web Archive
1/22/20141.01%Web Archive
2/14/20141.00%Web Archive
7/9/20140.95%Web Archive
9/3/20141.00%Web Archive
12/3/20141.00%Web Archive
1/29/20151.05%Web Archive
4/26/20161.05%Web Archive
11/29/20161.05%Web Archive
5/3/20171.30%Web Archive
12/1/20171.35%Bankrate
12/20/20172.00%Fat Piggy
2/15/20182.75%Web Archive
7/9/20192.55%Web Archive
12/13/20192.00%Hustler Money Blog
1/26/20202.00%I pulled this

Note for anyone else, I had some trouble with the Web Archive providing inconsistent results on the pages I was using to get the interest rates from. This is why for some of the more recent recordings, I used blog posts to get the rates. I think there is some kind of issue with how the data is saved since it looks like the data is loaded by javascript I think my browser is caching the javascript file with information from a previous snapshot.

Current Ally Rates

Recorded 2/1/2020

  • 9 Month: 1.25%
  • 12 Month: 2%
  • 18 Month:
    • Less than $5k Deposit : 1.9%
    • $5k – $25k Deposit : 2.0%
    • Greater than $25k Deposit: 2.05%
  • 3 Year:
    • Less than $5k Deposit : 1.95%
    • $5k – $25k Deposit : 2.05%
    • Greater than $25k Deposit: 2.10%
  • 5 Year: 2.15%