The correct adjustment

My thanks to Anil for bringing this file to my attention.

https://www.dropbox.com/s/0l5tijquoma4k3y/Cumulative%20CO%20Rates%20Projected%20Loss%20Curves%20v15_OCT2015.xlsx?dl=0This is LC loss data which shows for each quarter and every loan grade the monthly losses (hidden lines) and cumulative monthly losses (select particular loan grades or combinations from pivotTable in upper left corner). For a particular loan grade the raw data is hard to work with because for loans that have fully matured (issued more than 3-5 years ago) there are few loans in any particular grade/issue_quarter so the statistics are poor. But included on the sheets “36M Future…” and “60M Future …” do what I believe after filtering and averaging the raw data, likely reflect the expected monthly charge offs for each grade. (Below)

I chose E only grade loans because my portfolio most closely resembles E notes, but the method I will employ will work for any grade or combination of loan grades.

So let’s look at recent E grade performance. My account has a weighted average interest rate of 18.56%, made up predominantly of E grade notes which average of 20.12%. So the analysis that follows uses E grade data only. You can see below LC data for the last 10 quarters (30 months).

https://www.lendingclub.com/info/demand-and-credit-profile.actionWhenever I have a tough question, I run the amortization schedule. Let’s look at the amortization schedule for E loans subject to the monthly losses above.

PLEASE NOTE HIDDEN ROWS AT HORIZONTAL LINE

There are two amortizations at two different interest rate, 20.12% and 9.96% (Orange) from the LC table above. Columns labeled “Multiplier” are the fixed multiplier (1 + decimal monthly interest) that will when applied to the prior monthly account value give you the current value (remember E grade loans). In the second “Value” column we see the amortization of $100 for 36 months at the reported ANAR rate of 9.96%. In the first “Values” column is the amortization at the 20.12% average note rate BUT where the multiplier is adjusted monthly for the charged off loans extracted from the LC charge off file (labeled “Prin Loss”). When adjusted, both these should result in the same final account value after 36 months if LCs figures are correct and you can see that they do (green). But it took more than just that adjustment to get this result which confused me for some time. You actually lose twice. You lose the interest income as soon as the loan stops performing, and then you lose the principal 4-6 months later. Before this realization I could not get the final values to match. When you add together the income loss (advanced 4 month) to the principal loss as it occurs and adjust the multiplier by subtracting both, you can see the values match almost exactly. This set of LC data is at least internally consistent.

So how much would we have to discount our CURRENT notes to get the right correction to our individual data point? That turns out to be easy. The column “Accumulated Loss” shows the running sum of monthly defaults. You can see after 36 months, a total of 15.18% of notes will have defaulted. So we need to discount our CURRENT notes by the fraction of defaults already encounter divided the total defaults to be expected. The second to last column contains this fraction. Clearly, if we had a brand new account with all newly purchased notes, the adjustment would be 10.16% (green, difference column). The last column show the discount that should be applied for each month of average age[ (1-.03)*10.16 = 9.87 ]. Notice current notes in my 13 month average age account should be discounted is 3.81 percent.

That is not quite the end of the story. My account (and most others in the population) is composed of 36 and 60 month loans. So we need to look at the 60 month amortization as well.

The blue dots above are the raw data. I told you it was all over the place. The red line is what LC says the “Future” monthly defaults should look like based on this historical record. The dashed line is a 5th order polynomial fit using least squares to the raw data. As you can see it is a reasonably close approximation to what LC says we should expect. However neither collection of default projections will cause the final values after 60 months of amortization to equal LCs reported ANAR (Es for the last 30 months). The losses had to be higher. The green line represents a corrected default rate 28% higher than reported by LC that does properly account for the difference between average note rate of 20.12% and ANAR of 9.96%. The corrected amortization is below.

PLEASE NOTE HIDDEN ROWS AT HORIZONTAL LINE

The blue line in the chart above is LCs reported default rates, and the red the adjusted rates I had to use to make the amortization of 60 month notes reconcile. So on the surface of it LCs data for 60 month notes is internally inconsistent. Their reported default rate could not adequately account for the difference between the average interest rate on notes and the reported adjusted NAR. There are some possible explanations. The defaults and recoveries are reported in the quarter the notes is issued, not in the quarter when the note actually defaults or what might be a substantial time later when a recovery might be made.

So let me return to the subject of the proper amount to discount our CURRENT notes. My account is made up of 85% 60 month notes and 15% 36 month notes. For 13 month old average age account, 60m notes should be discounted 6.1% and 36m notes 3.81. My composite CURRENT notes should then be discounted by 5.76% (0.85*6.1 + 0.15* 3.81). Remember according to LC, my ANAR was 15.11%. If I add a discount (if I could) to my current notes of 5.76% my real ANAR would be properly reported as 9.35%. That is a 0.38% difference from what my XIRR properly reports at 8.98%.

There is more to say, but I’ll wait for my punishment from the better mathematicians and statisticians on the board for not having a brain in my head. But in the interim, please don't put any confidence in, or make any important decisions based on what LC says you are earning.