• Quicken 2016, 2017 or 2018 desktop software for Mac or for Windows: Starter Edition, Deluxe, Premier, Home & Business, or Rental Property Manager is required. • For Canadian customers, the Quicken Mobile Companion App, will work only with Quicken 2017 and 2018. As background, Quicken Loans is the largest online and second largest, retail residential mortgage lender in the United States. We have been in business for nearly 30 years. Quicken Loans is also a Fannie Mae and Freddie Mac approved Seller/Servicer and a FHA and VA approved mortgagee.
This article describes how I track and reconcile my investments, using the 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. 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.
![How How](/uploads/1/2/5/4/125463987/514037420.png)
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)). 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?
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. 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.
![How to track a loan in quicken How to track a loan in quicken](/uploads/1/2/5/4/125463987/774367502.png)
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.