This article describes how I track and reconcile my LendingClub investments, using the iBank personal finance software. This method should be equally valid for people using Quicken.In iBank, I set up my LendingClub account as if it were a brokerage account in which I’m purchasing securities; in this case, I’m purchasing a security I’ve named LendingClub (equivalent to a stock, e.g. AAPL). From there, tracking the investment simply involves mapping line items from the LendingClub statements to concepts normally involved in securities investment tracking, through the process of monthly reconciliation.
From the latest LendingClub statement, all values except one come from the Cash Details section of the statement, with the one additional needed value coming from the Earnings Summary section.
Cash Details Statement Section
- Funds Lent — Each dollar of Funds Lent maps to the purchase of one share of the security “LendingClub”, priced at $1 per share.
- Principal Received — Maps to the sale of one share of the security “LendingClub”, priced at $1 per share.
- Loan Interest — Maps to the category “Interest Income”.
- Late Fees Received — Maps to the category “Miscellaneous Income”.
- Recoveries — Treated equivalently to Principal Received.
- Collection Fees — Maps to the category “Fees:Collection”.
- Service Fees — Maps to the category “Fees:Service”.
- Adjustments/Credits — Not sure about this one, but so far I’ve never had a value in this field.
- Deposits/Withdrawals — Maps to “Transfers” (between my LendingClub account and the source/destination account of the transferred funds.)
- In Funding Loans — I treat this as cash (see below).
Fields which are not relevant to me include Referral Bonus, Charity, FOLIOfn transfers and Trading fees.
Earnings Summary Statement Section
- Losses (charged off loans) — Each dollar maps to the sale of one share of the “LendingClub” security at $0 per share. (That’s right, zero dollars; this is how I track a complete loss.)
Reconciliation
To reconcile the investment account each month, I enter all the relevant transactions from the statement as described above. I then make sure the Starting and Ending balances are correct, and that involves the special treatment of “In Funding Loans”.
The Starting Balance each month is the Ending Balance of the previous month, as recorded in iBank.
The Ending Balance is sum of the Ending Balance as reported on the LendingClub statement, PLUS any amount reported in “In Funding Loans”. Since “In Funding Loans” are funds that have not yet been lent, they remain for the purpose of reconciliation as cash in the account (even though it’s not cash available for immediate withdrawal.)
Performance
One the LendingClub account is tracked in the manner described above, I can then generate performance reports (including, in version 5.1 of iBank, Internal Rate of Return (IRR))
Thank you for this… I’ve been trying to figure out how I’m going to record my Lending Club investment in iBank–this will work well.
Thanks! This worked 🙂
I’m about one month into using Lending Club and really appreciated this post. I use iBank as well so it was even more useful! It sounds like you are tabulating all the loan purchases and principal repayments from each monthly statement and then purchasing/selling, in aggregate, everything at the end of the month. Does this mean that all the transactions (other than transfers into and out of your Lending Club account) are recorded as taking place at the end of the month, March 31, most recently? Thanks for the clarification?
Scott
Scott, that’s all correct, except that as a habit I’ve recorded them all happening in the middle of the month, on the 15th.
Super helpful. Thanks for the post!
Thanks a bunch for posting this – maybe someday Intuit will support Lending Club and Prosper for Quicken, but until then, this method works.
Once you start receiving payments, it’s a whole new bag of worms. Lending Club will make one entry in your account activity list which aggregates all payments made on a given date. Click on that, and you’ll get a pop-up windows that lists all the individual principal, interest and fees amounts that make up the total – if you are doing a lot of lending, it could be dozens of lines, and there’s no column totals.
Since I’m not tracking individual loan numbers, I just want the totals for each of the columns. So here’s what I do:
Highlight all the data in the popup, including the header row. Highlight the “L” in “Loan ID”, which is first column label, then scroll the body of the window to the bottom of the list, hold down the shift key, and click immediately to the right of the last number in the last column. That will highlight everything. CTRL+C to copy.
Open a blank spreadsheet, place the cursor in the first cel, and paste (CTRL+V). With OpenOffice Calc this will “smart” paste the data from Lending Club, preserving columns and data types. Should be possible to do the same with Excel, though I have not tried it.
Highlight the cells at the bottom of the columns you want to total, then click the SUM shortcut on the formulas toolbar to get a total for each column, You can now record those values per the guide above.
A bit of work, but a lot easier than adding each column by hand or recording each note individually.
Since LendingClub issue consolidated statements, and have for as long as I can remember, I’m unsure why you need to do any processing of the raw data.
I’m working off transaction activity in the web interface. I’ve just started with Lending Club and only have one statement thusfar – it does not contain the information I’ve described in my post. Perhaps subsequent statements will. Just trying to be helpful.
LendingClub statements consolidate the reporting of principal, interest, fees, everything that I listed above in the bullet points— and they always have. (I’ve been a LendingClub investor since 2009.) The process you’ve described above is unnecessary.
Thanks for the post. I have a similar setup, but I am running into a fundamental problem. I’m using automated investing, so the cash that I receive from principal and interest will automatically go into the purchase of a new share. As you do, I treat Interest Received as Interest Income, and Principal Received as a Sold share. The problem is, the way Quicken handles the transactions is that it considers newly purchased shares(reinvestments) as part of the cost basis, even if you did not put additional cash into the account. The net effect is that when you go to the Investing tab, the Portfolio Value and Cost Basis will always be the same. Ideally, the cost basis should only consist of the cash actually transferred to Lending Club so that Quicken can demonstrate that growth is a result of the investment, not simply that I just kept pumping money into it.
Do you have this problem? Can you think of a way around it?
In Banktivity (which is the new name of iBank), I would handle reinvestments as two separate transactions — one in which I record the earned interest, and a second one in which that amount of interest is used to buy new loan shares.
Your cost basis SHOULD include the earned interest that is reinvested, and not just the cash that you transfer to LendingClub. That auto-reinvestment is just a convenience on the part of LendingClub — from an accounting and taxation point of view, it’s as if you received the interest (income), deposited it into your bank account, and then transferred it back into LendingClub to purchase new loan shares.
Thanks for replying. You’re absolutely right. I suppose the view I wanted was to see how much the account value went up because of interest vs how much I put into it. Quicken provides the two values in the same graph, for example. I can see in my 401k that my growth has increased the value beyond my contributions, even though the total account value is going up every month due to both growth and contributions.
Hi, did you figure out a good way to view the change in value Quicken?
Great article! One note, it my experience, Recoveries should not be treated equivalently to Principal Received, because in my experience these amounts are recovered from Charged-off Loans and thus have already been sold (as a loss). So you’re taking a double hit to your outstanding principal.
I just treat them as a deposit to the account. I categorize it as Other Income, which is debatable.
Regards,
Brian
Very helpful article. Wondering if you have an update for Banktivity (6)? I have it set to auto-import Lending club transactions, and because of their rounding, it never quite tallies with the monthly statements. Plus, if I enter the note purchases as “buy” transactions, and the principal payments as “sell” transactions then I have to exclude them from the statement reconciliation or it gets really messy. Any tips?
I actually stopped investing at LendingClub a long time ago. The default rate as notes got older grew to a point that my annual return was dropping dramatically. Apart from that, I don’t use Banktivity’s reconcile feature for investment accounts. Each month, I would just enter all the transactions, and then make a small “Account balance” update to get the cash level correct (due to, as you mentioned, their rounding issues.)