Tracking LendingClub Performance With Quicken
I have seen several threads about tracking LendingClub with Quicken, but I haven't seen something on tracking performance. I've been using IntInc to track the interest received on principal.
- Would Dividends or Reinvested Dividends affect the performance data in Quicken better?
- Should the Cost Basis always reflect the Portfolio Value, like it does now ($1 value cost $1)?
- Is there a better Performance chart that would show the performance of my LendingClub investments better?
- The Growth of $10,000 chart looks absurd as it is counting my Xins as "growth", so am I tracking something wrong? Is there a way to treat the influx of cash so it isn't considered a performance bump?
In a nutshell, I'm tracking LendingClub "Shares" worth $1 each using Bought, Sold, and Removed (sold-off losses), along with miscellaneous income and expenses to track fees, corrections, etc.
Comments
-
I track LC the same way, with $1 shares. I have 4 entries each month: Bought, Sold, Interest, Misc. Exp. Defaulted loans go in as sold @ $0.
Go to Reports, Investing, Investment Performance, then set your date range and subtotal by account. You will get your investment return without the additions bumping it out of whack.0 -
Vetta, thanks for the quick response. in the meantime, I've gotten very frustrated with trying to interpret the statements. For the longest time, the starting (end of last month's) Outstanding Principal + Buy (notes bought) - Sell (principal recovered) = (end of this month's) Outstanding Principal. However, I can't get them to line up, now. Do you have a different formula to follow, or do you put in adjustment/correction entries to make Quicken match the statements?
I tried the report you mentioned, looking only at LendingClub, and I'm not sure I understand how the Avg. Annual Return is calculated; I can't get the Quicken value running my own numbers. I did notice that removing shares (written-off loans) showed up in the Returns column with a positive value, so maybe I need to sell at $0, like you do? Do you feel like the IRR it reports is accurate?
Do you know of any ways to make the default reports on the Investment tab look better? I.e., Growth of $10k?
0 -
If you use remove shares instead of sold at $0 your returns will be all off because you have told Quicken to ignore the cost basis.
As for the formula:
starting (end of last month's) Outstanding Principal + Buy (notes
bought) - Sell (principal recovered) - Notes sold or transferred out + Notes bought or transferred in - Losses(charged off loans) {This one is on the first page) = (end of this month's) Outstanding
Principal
Of course you can remove the transfers if you are not buying/selling on Folio (which I don't recommend, people on there will not buy the notes for what they are worth).
This will not always result in an exact number because of the nature of the splitting of notes between all the investors, but it should be within about 0.01. These will be adjusted over time. As in one time you will be short 0.01 the next you will get 0.01 extra. For these I do use Add/Remove shares to adjust the balance.
It is my belief that it is impossible to get a graph like Growth of $10K to work right with Lending Club Notes. Graphs like that take into account when securities are bought and sold, and you have not provided an accurate account of that.
In theory if you wanted to track Lending Club Notes accurately in Quicken every note would have to be kept separate. And you would be removing principal from them and adding interest to them.
Given that there can be thousands of notice in one Lending Club account, this is not possible.
So what you are doing is putting them all in one lot, and using FIFO on the sells. This doesn't describe what is really happening. I have not dug into all the details to prove that this is what is causing the really inaccurate graphs, but I think it probably is.
What's more graphs like Growth of $10K are mostly for securities that go up and down in comparison to the cost basis. This doesn't happen with Lending Club Notes. They are always either 1 to 1, or they are worthless.
Personally my entries are a bit complicated because I like to see the different income and expenses separately.
Main ones from second page on the statement:
Funds invested: Buy
Principal Received: Sell
Interest received: MiscInc set to separate category
Late fees received: MiscInc set to separate category
Recoveries: MiscInc set to separate category
Recovery Fees: MisExp set to separate category
Service Fees: MisExp set to separate category
Collection Fees: MisExp set to separate category
From first page:
Losses (charged off loans) I enter three transactions because I want my gains to be in a separate category.
Sold, number of shares at $0.
MiscInc, value/number of shares with a category of Rlzdgain (zeros the loss out of this category)
MiscExp, value/number of shares with my category I use for Charge Offs.
I notice a really weird behavior in the Investment Performance report.
If I select just the accounts in question, with all securities selected, it doesn't include the buys and sells, only the cash transactions. If I select just the Lending Club Note security, it includes them and gives the "right" number.
Note that Lending Club is using a completely different way to calculate this so even though I find this number to be in the general "ball park", there is not real exact comparison between the two.
In general I find Lending Clubs numbers to be "accurate", but you have to take into account the nature of Lending Club Notes and what "phase" your account(s) are in.
For instance at times it has taken up to a 18 months for notes to be written off from the time they were issued/went bad. I believe they are doing this much faster, but it is still a big lag. 30 days and they aren't even "late". 90 days before they even consider it for default, more days to go from default to charged off. Not to mention that it might not be until later in payment schedule that people get behind on their payments. This long period means that people/new money starting out looks like it is will earn a lot more than it will in the long run.
If you are constantly taking money in and out the calculations to get the right return number are extremely complex. And in fact there probably isn't a "right answer".
If on the other hand if you haven't put any new money in for a long time or taken any out, then just looking at the balances from two time periods and calculating the percentage difference works pretty well.0 -
QPW said:
If you use remove shares instead of sold at $0 your returns will be all off because you have told Quicken to ignore the cost basis.
As for the formula:
starting (end of last month's) Outstanding Principal + Buy (notes
bought) - Sell (principal recovered) - Notes sold or transferred out + Notes bought or transferred in - Losses(charged off loans) {This one is on the first page) = (end of this month's) Outstanding
Principal
Of course you can remove the transfers if you are not buying/selling on Folio (which I don't recommend, people on there will not buy the notes for what they are worth).
This will not always result in an exact number because of the nature of the splitting of notes between all the investors, but it should be within about 0.01. These will be adjusted over time. As in one time you will be short 0.01 the next you will get 0.01 extra. For these I do use Add/Remove shares to adjust the balance.
It is my belief that it is impossible to get a graph like Growth of $10K to work right with Lending Club Notes. Graphs like that take into account when securities are bought and sold, and you have not provided an accurate account of that.
In theory if you wanted to track Lending Club Notes accurately in Quicken every note would have to be kept separate. And you would be removing principal from them and adding interest to them.
Given that there can be thousands of notice in one Lending Club account, this is not possible.
So what you are doing is putting them all in one lot, and using FIFO on the sells. This doesn't describe what is really happening. I have not dug into all the details to prove that this is what is causing the really inaccurate graphs, but I think it probably is.
What's more graphs like Growth of $10K are mostly for securities that go up and down in comparison to the cost basis. This doesn't happen with Lending Club Notes. They are always either 1 to 1, or they are worthless.
Personally my entries are a bit complicated because I like to see the different income and expenses separately.
Main ones from second page on the statement:
Funds invested: Buy
Principal Received: Sell
Interest received: MiscInc set to separate category
Late fees received: MiscInc set to separate category
Recoveries: MiscInc set to separate category
Recovery Fees: MisExp set to separate category
Service Fees: MisExp set to separate category
Collection Fees: MisExp set to separate category
From first page:
Losses (charged off loans) I enter three transactions because I want my gains to be in a separate category.
Sold, number of shares at $0.
MiscInc, value/number of shares with a category of Rlzdgain (zeros the loss out of this category)
MiscExp, value/number of shares with my category I use for Charge Offs.
I notice a really weird behavior in the Investment Performance report.
If I select just the accounts in question, with all securities selected, it doesn't include the buys and sells, only the cash transactions. If I select just the Lending Club Note security, it includes them and gives the "right" number.
Note that Lending Club is using a completely different way to calculate this so even though I find this number to be in the general "ball park", there is not real exact comparison between the two.
In general I find Lending Clubs numbers to be "accurate", but you have to take into account the nature of Lending Club Notes and what "phase" your account(s) are in.
For instance at times it has taken up to a 18 months for notes to be written off from the time they were issued/went bad. I believe they are doing this much faster, but it is still a big lag. 30 days and they aren't even "late". 90 days before they even consider it for default, more days to go from default to charged off. Not to mention that it might not be until later in payment schedule that people get behind on their payments. This long period means that people/new money starting out looks like it is will earn a lot more than it will in the long run.
If you are constantly taking money in and out the calculations to get the right return number are extremely complex. And in fact there probably isn't a "right answer".
If on the other hand if you haven't put any new money in for a long time or taken any out, then just looking at the balances from two time periods and calculating the percentage difference works pretty well.I notice a really weird behavior in the Investment Performance report.
As I understand the Investment Performance Report / IRR calculation, how you have the calculation sub-totaled or grouped or securities included is a key variable on the transactions presented.
If I select just the accounts in question, with all securities selected, it doesn't include the buys and sells, only the cash transactions. If I select just the Lending Club Note security, it includes them and gives the "right" number.
Consider the case of a Dividend paid. On a security level (subtotal), the dividend is part of the return and so is listed as a return. On an account level, the dividend stays in the account as cash. It is effectively a transfer within that subtotal from security to cash, so it does not get listed.
The same rationale applies to a sell or a buy. If the cash is coming from or staying in the account, the account subtotal will not show the transaction as you are just changing the form of the asset within that subtotal. But at a security level (subtotal), those transactions do represent added return or investment in that security.
At least, I think that is the basics of what is going on.0 -
What a detailed message. In summary, I think I need to change my hopes and expectations. I mainly want to: represent LendingClub assets in my asset allocations and have an indicator of performance trend (that I can trust) outside of LendingClub's, and have meaningful performance details ($, %) that I can compare to other assets. I think I can get there, but I'll need to try some things and re-evaluate. One of those things, it sounds like, is to change my written-off losses to $0 sales (instead of simply removing shares).
From first page:
Losses (charged off loans) I enter three transactions because I want my gains to be in a separate category.
Sold, number of shares at $0.
MiscInc, value/number of shares with a category of Rlzdgain (zeros the loss out of this category)
MiscExp, value/number of shares with my category I use for Charge Offs.I'm not quite following the Misc entries. If you only "sold" x shares of LendingClub (purchased at $1 per unit) at $0 per unit, then there is no loss category. However, you use the Misc entries and a Charge Off category to allow you to track and quantify the losses; am I getting that right?
If there was a way to share a "safe" (personal/financial details removed?) report/graph image that illustrates how you look at things and what you're looking for, that would be pretty valuable as well.
0 -
As was pointed out, you are not going to get the same return reports with LC because the share price never varies. All you're really tracking are the interest payments and how your return is affected by defaults.
Yes, you need to show write-offs as Sold @ $0. I do not use any misc. categories here, only the 4 from my earlier post. When running a report from either the Reports tab or the Investing tab (from the very top of the screen) I get an accurate % return value.
Also, you may have closing amounts that are off by a penny or two from the statement, which is normal. I have found that this problem self-corrects after a few months (.01 one month, -.01 the next) and comes from the rounding of amounts among investors.
If your Q account varies more than a few cents it is probably "committed cash," (notes you have bought that are not yet issued) and will always be a multiple of 25.
Does this answer your questions?0 -
MJ said:
What a detailed message. In summary, I think I need to change my hopes and expectations. I mainly want to: represent LendingClub assets in my asset allocations and have an indicator of performance trend (that I can trust) outside of LendingClub's, and have meaningful performance details ($, %) that I can compare to other assets. I think I can get there, but I'll need to try some things and re-evaluate. One of those things, it sounds like, is to change my written-off losses to $0 sales (instead of simply removing shares).
From first page:
Losses (charged off loans) I enter three transactions because I want my gains to be in a separate category.
Sold, number of shares at $0.
MiscInc, value/number of shares with a category of Rlzdgain (zeros the loss out of this category)
MiscExp, value/number of shares with my category I use for Charge Offs.I'm not quite following the Misc entries. If you only "sold" x shares of LendingClub (purchased at $1 per unit) at $0 per unit, then there is no loss category. However, you use the Misc entries and a Charge Off category to allow you to track and quantify the losses; am I getting that right?
If there was a way to share a "safe" (personal/financial details removed?) report/graph image that illustrates how you look at things and what you're looking for, that would be pretty valuable as well.
Even though it might not seem like a sold transaction is connected to a category, it actually is. Every sell transaction generates a value in _Rlzdgain (I missed the _ in the description above.) _Rlzdgain is a hidden category Quicken uses to track your realized gains. In the case of all the other transactions you have in the Lending Club accounts there is no gain/loss because the value of a share of the security is always $1. So you won't see those.
Here is an example of the three charged off transactions.
And an example of what my Income/Expense report looks like:
0 -
Vetta said:
As was pointed out, you are not going to get the same return reports with LC because the share price never varies. All you're really tracking are the interest payments and how your return is affected by defaults.
Yes, you need to show write-offs as Sold @ $0. I do not use any misc. categories here, only the 4 from my earlier post. When running a report from either the Reports tab or the Investing tab (from the very top of the screen) I get an accurate % return value.
Also, you may have closing amounts that are off by a penny or two from the statement, which is normal. I have found that this problem self-corrects after a few months (.01 one month, -.01 the next) and comes from the rounding of amounts among investors.
If your Q account varies more than a few cents it is probably "committed cash," (notes you have bought that are not yet issued) and will always be a multiple of 25.
Does this answer your questions?Yep, getting a lot of good responses.
What do you think about QPW's 3 transactions per charged-off loss "sale"?
0 -
I don't participate in LC, but reading this brings a question to my mind: Why not just treat the constant $1 shares as if they were shares of a Money Market Fund, which also has a constant $1 value?Vetta said:As was pointed out, you are not going to get the same return reports with LC because the share price never varies. All you're really tracking are the interest payments and how your return is affected by defaults.
Yes, you need to show write-offs as Sold @ $0. I do not use any misc. categories here, only the 4 from my earlier post. When running a report from either the Reports tab or the Investing tab (from the very top of the screen) I get an accurate % return value.
Also, you may have closing amounts that are off by a penny or two from the statement, which is normal. I have found that this problem self-corrects after a few months (.01 one month, -.01 the next) and comes from the rounding of amounts among investors.
If your Q account varies more than a few cents it is probably "committed cash," (notes you have bought that are not yet issued) and will always be a multiple of 25.
Does this answer your questions?Q user since February, 1990. DOS Version 4
Now running Quicken Windows Subscription, Business & Personal
Retired "Certified Information Systems Auditor" & Bank Audit VP0 -
Vetta said:
As was pointed out, you are not going to get the same return reports with LC because the share price never varies. All you're really tracking are the interest payments and how your return is affected by defaults.
Yes, you need to show write-offs as Sold @ $0. I do not use any misc. categories here, only the 4 from my earlier post. When running a report from either the Reports tab or the Investing tab (from the very top of the screen) I get an accurate % return value.
Also, you may have closing amounts that are off by a penny or two from the statement, which is normal. I have found that this problem self-corrects after a few months (.01 one month, -.01 the next) and comes from the rounding of amounts among investors.
If your Q account varies more than a few cents it is probably "committed cash," (notes you have bought that are not yet issued) and will always be a multiple of 25.
Does this answer your questions?
NotACPA, that is exactly what people do (treat the "shares" as a constant $1). The bigger questions is how to treat the interest, misc income, and fees so that you can get any reasonable kind of performance values.
But there are limits on what you can get out of Quicken since it really doesn't allow for exactly tracking this kind of security.
For instance if you have a Money Market Fund with a constant $1 value, what do you think you would get out of the "Growth of $10,000" graph?
For instance here is a graph of one such fund.
Very impressive until you check and find the starting value is $3.70, and the ending value is $243.86. And probably didn't get more than a penny or so interest.
The only reports that make any sense at all are ones like the Investment Performance report and such that use IRR.
One of the reasons some of the other calculations don't work is because there is a "time value" in them. If you were really tracking these for real what you would do is use a buy for every Lending Club Note.
A note is your part of a bigger loan. As in the loan is for $10,000, but your note is for $100 (This can be as low as $25). So you would do a buy for $100. Now as the principal is paid off you would sell that amount of shares, and you put the interest into something like misc income, and you subtract out the fees with misc expense. Charged off loans would be a sell with a value of $0.
But no one buys just one or two notes. You need at least hundreds to reduce the risk from the charged off loans.
Even if you were willing to do it, Quicken can't take that many transactions.
So instead you lump them together.
Your "lots" are say a buy for all the notes that month, and your sell lots are for all the principal (or charged off principal) for that month. That means that the buy lots and the sell lots are not line up at all. So any calculation that Quicken performs that require the lots to be properly line up will be wrong.0 -
Vetta said:
As was pointed out, you are not going to get the same return reports with LC because the share price never varies. All you're really tracking are the interest payments and how your return is affected by defaults.
Yes, you need to show write-offs as Sold @ $0. I do not use any misc. categories here, only the 4 from my earlier post. When running a report from either the Reports tab or the Investing tab (from the very top of the screen) I get an accurate % return value.
Also, you may have closing amounts that are off by a penny or two from the statement, which is normal. I have found that this problem self-corrects after a few months (.01 one month, -.01 the next) and comes from the rounding of amounts among investors.
If your Q account varies more than a few cents it is probably "committed cash," (notes you have bought that are not yet issued) and will always be a multiple of 25.
Does this answer your questions?If your Q account varies more than a few cents it is probably "committed
The committed cash is just include in the case balance of the account.
cash," (notes you have bought that are not yet issued) and will always
be a multiple of 25.
As in: non committed cash + committed cash = cash balance.
I don't know of anyone that tries to track the committed cash separately.
You just add them together when doing the reconcile.
For people interested. "committed cash", once you "buy a note" you can't back out of it. But it takes some time for the loan to fund, and can even not go through. So your cash is committed until the load is funded or falls through.
Not that pennies of shares or cash come about because you have a part of loan. As in 100/ 10,000 = 0.01. So if $101.20 of interest is paid your share is $101 * .01 = $1.012, but of course they can only give you $1.01 or $1.02. So what Lending club does is get the "round off" to some of the investors in the note, and remembers that. And on the next monthly payment they give the round off to other investors. Which eventually should even out over a lot of notes/months.0
