Tracking Lending Club Notes

RMW
RMW Member ✭✭✭
edited October 2018 in Investing (Mac)
For those who use Quicken to track Lending Club transactions: How to enter Charge offs (Losses)?

Comments

  • NotACPA
    NotACPA SuperUser ✭✭✭✭✭
    edited October 2018
    A charge-off would be an expense transaction recorded within the Lending Club account.  What category you use (and it's tax line assignment) would be up to you.

    Q user since February, 1990. DOS Version 4
    Now running Quicken Windows Subscription, Business & Personal
    Retired "Certified Information Systems Auditor" & Bank Audit VP

  • RMW
    RMW Member ✭✭✭
    edited March 2017

    A charge-off would be an expense transaction recorded within the Lending Club account.  What category you use (and it's tax line assignment) would be up to you.

    Problem with that approach is that it would change (reduce) the cash balance. 
  • NotACPA
    NotACPA SuperUser ✭✭✭✭✭
    edited March 2017

    A charge-off would be an expense transaction recorded within the Lending Club account.  What category you use (and it's tax line assignment) would be up to you.

    How do you have those transactions (that account) recorded in Q?

    And yes, it will reduce the cash balance ... because you have a loss and so the account is worth less ... by the amount of the charge-off.

    Q user since February, 1990. DOS Version 4
    Now running Quicken Windows Subscription, Business & Personal
    Retired "Certified Information Systems Auditor" & Bank Audit VP

  • RMW
    RMW Member ✭✭✭
    edited March 2017

    A charge-off would be an expense transaction recorded within the Lending Club account.  What category you use (and it's tax line assignment) would be up to you.

    You are right in saying that the account is technically worth less. But, charge offs are not included in LC's monthly cash details and they do not have an impact on the cash I can withdraw or reinvest. I understand that charge offs are capital gain losses and are recorded as such for tax purposes. I can not find a way in Quicken to enter them for that purpose without changing the cash balance in the account. Somebody suggest a while ago to enter charge offs as a Sell transaction at zero dollars. But how to track losses if there are always zero?
  • Unknown
    Unknown Member
    edited October 2018
    I invest in Lending Club notes, so I will give you what I do.
    But a bit of background for people that aren't familiar with Lending Club notes.
    Lending Club started as peer to peer lending.  These days the "big guys" even funds buy notes, but still there is peer to peer lending.

    These are personal loans, and to the person getting the loan it is pretty much like getting a loan from anywhere else.  They deal with Lending Club for payments and such.

    The "note" come in that no one person funds a loan (at least I hope they don't!).
    Each person decides how much they will be putting into any given note (a part of the loan).  This can be done manually or automatically.  And the suggestion is that people should be investing in at least 100 notes to spread out their risk.  Because these can and do have people that default on loans and they get charged off.

    OK there is no way I would use the normal loan accounts for this.  Not only would that be a lot of manual entries (Lending Club notes transactions can't be downloaded directly), and it might affect performance with that many loan accounts, people pay off loans before the 3 or 5 years is up, so you cycle through a lot of loans in a short amount of time.  Quicken has a limit of 512 accounts.

    So what I do is use an investment account (per account on Lending Club).
    The security is Lending Club Note, and it is always 1 share to $1.

    If you were to download every transaction for every loan and put in buys/sell for all of them you would end up with tons of investment transactions, and performance would be terrible in no time.

    So what I was doing was any automated summary of notes per week converted to QIF and then imported.

    I did that for several years, but even that became more trouble than I wanted, so I currently what I do is just enter a set of transactions that summarize the monthly totals.  I so this from the monthly statement.  And I just do this manually.

    It is pretty simple.
    Investing in new notes: Buy (1 share times dollar amount)
    Principal received: Sell (1 share times dollar amount)
    Next there are a few ways you will get income.
    There is of course the interest, but there is also Late fees (person that took out loan is paying), and there are recoveries (money collect by collection agencies for charged off loans).

    These are entered with the Income action.  You could enter the interest on the interest line and use the default category, but I have a category for each the different kinds of income including the interest, because I like to keep them separate.  So I put the number in the miscellaneous field, and enter the category of my choice.

    Then there are the fees.  Basically these just mirror the income with the exception that you use the MiscExp action.

    That leaves charged offs.
    I actually use three transactions for this because I want my charged offs to be recorded in my category for it.

    The first transaction is a sell for the dollar amount of the charged off, with the price being left empty (zero).
    This will result in the _RlzdGain getting the amount of charged offs.  (You have no control over what category it uses for a sell, but you have to use a sell to get rid of the shares)

    So the next thing I do is put in an Income transaction with the misc category of _RlzdGain.  this basically removed that amount from _RlzdGain.

    The last transaction is a MiscExp transaction putting the charged off into my category for this purpose.

    Also I will note that because these notes are being split amount lots of people fractions of a cent can result.  Lending Club keeps track of these, but it might mean this month you are one cent "short" the next one cent "over".  So adjusting the share (with a buy or a sell or just changing one of the existing transaction) and the same with the cash is certainly needed from time to time to reconcile.

    For transfers.  I just do a normal transfer to/from my checking account for my taxable accounts (which BTW I'm almost completely out of) or a transfer from CamaPlan (IRA provider for Lending Club) to the Lending Club account.

    I will mention that you can only do very basic "return" calculations for these in the portfolio and such.

    Also none of this is used for taxes.  I take the 1099s from Lending Club in Excel format, save them to CSV format, and import those into my tax program.
  • Unknown
    Unknown Member
    edited October 2018
    P.S.  I should have noted that the cash balance shown in Quicken will not be your "cash on hand", even at the moment when this was calculated, because it will also include cash that is currently committed to notes that you have "bought", but have yet to be fully funded/issued.

    Also if a person wanted to have a bit more up to date values, they could do the exact same thing using the current numbers on the website, say once a week.
  • Unknown
    Unknown Member
    edited October 2018
    "I did that for several years, but even that became more trouble than I wanted"

    Note part of the "trouble" was that even 5 or 6 transactions a week over a long period of time add a lot of transactions to one of these accounts, and performance started to suck.

    Also because of the rounding and things like where I would take the current values on on the website, on say the 31st of December, and get those entered, this wouldn't line up what was on the statement because things like charged off are sometimes delayed into the next month even though it showed on the website.
  • RMW
    RMW Member ✭✭✭
    edited October 2018
    Let's say, I have a charge off of $500. Would you enter that as 0.00 Total Sale and 500 as the number of shares? The price per share is always greyed out and nothing can be entered.
    If I enter the $500 in the Total Sale field and 0 as the number of shares sold, my cash balance will increase by that amount. Which is what I wanted to avoid.
  • Unknown
    Unknown Member
    edited March 2017
    RMW said:

    Let's say, I have a charge off of $500. Would you enter that as 0.00 Total Sale and 500 as the number of shares? The price per share is always greyed out and nothing can be entered.
    If I enter the $500 in the Total Sale field and 0 as the number of shares sold, my cash balance will increase by that amount. Which is what I wanted to avoid.

    "Would you enter that as 0.00 Total Sale and 500 as the number of shares?"
    Yes

    "The price per share is always greyed out and nothing can be entered."
    That is fine.
    It is calculating that the price per share has to be zero.

    "If I enter the $500 in the Total Sale field and 0 as the number of
    shares sold, my cash balance will increase by that amount. Which is what
    I wanted to avoid."

    The goal here is to remove shares not cash.  Each share should represent $1 of principal left owed to you. 

    Note I'm on Windows so this is going to react differently, and as such you might have to adjust your entries according to what Quicken Mac will let you enter.  Not only in how you enter this sell, but also what category they might use for realized gains, and how to enter both misc income and expenses.

    In Quicken Windows for a sell I'm free to type in Number of shares, Price received XXX per share, commission, and Total sale.  Of course in reality you don't type in all of them because at least one will be calculated for you.  And in my case I actually only type in one number because the defaults/calculation are fine.  I enter the number of shares and leave the rest blank (blank Price received equal $0,00)
    image

    Note I can type into the Price per share, but look what would happen (entering 1):
    image
    This wouldn't be a charge off, the shares have value.

    Now I try typing 2 for the price per share just as a test.
    image

    The math doesn't line up so Quicken Windows is asking me what value to recalculate.

    In the case of Quicken Mac from what I understand, you can't do this, you have certain fields you have to enter, and you have others like the price that are always calculated.
  • Unknown
    Unknown Member
    edited March 2017
    RMW said:

    Let's say, I have a charge off of $500. Would you enter that as 0.00 Total Sale and 500 as the number of shares? The price per share is always greyed out and nothing can be entered.
    If I enter the $500 in the Total Sale field and 0 as the number of shares sold, my cash balance will increase by that amount. Which is what I wanted to avoid.

    BTW.  I did make the mistake of not noticing that you were using Quicken Mac when I first posted, and posted about Quicken Windows, on the first posts, but I think pretty much all of it applies quite well to Quicken Mac too.
  • RMW
    RMW Member ✭✭✭
    edited March 2017
    RMW said:

    Let's say, I have a charge off of $500. Would you enter that as 0.00 Total Sale and 500 as the number of shares? The price per share is always greyed out and nothing can be entered.
    If I enter the $500 in the Total Sale field and 0 as the number of shares sold, my cash balance will increase by that amount. Which is what I wanted to avoid.

    Why use 1 share at $1?
    LC Notes are sold at $25 or multiples of $25. 
  • Unknown
    Unknown Member
    edited March 2017
    RMW said:

    Let's say, I have a charge off of $500. Would you enter that as 0.00 Total Sale and 500 as the number of shares? The price per share is always greyed out and nothing can be entered.
    If I enter the $500 in the Total Sale field and 0 as the number of shares sold, my cash balance will increase by that amount. Which is what I wanted to avoid.

    You use 1 share to $1 because in fact you have no shares, only dollars.  By keeping them one to one you have no problems with any kind of conversion between the two.

    You definitely don't want to price them at $25 a share.  Yes, you have to buy notes in $25 dollar increments, but the principal doesn't come back in $25 increments.  And you need to use a sell of shares to record the returning of principal.

    For instance you bought $3300 dollars worth of notes in a month.  And you got $2141.09 of principal back.

    That would be recorded as:
    A buy of 3300 shares at $1 a share.
    A sell of 2141.09 shares at $1 a share.

    If you have your shares priced at $25 a share, that sell would have to be calculated out like 2141.09 / 25 = 85.6436.  And that is time consuming, and causes rounding errors.
  • RMW
    RMW Member ✭✭✭
    edited March 2017
    RMW said:

    Let's say, I have a charge off of $500. Would you enter that as 0.00 Total Sale and 500 as the number of shares? The price per share is always greyed out and nothing can be entered.
    If I enter the $500 in the Total Sale field and 0 as the number of shares sold, my cash balance will increase by that amount. Which is what I wanted to avoid.

    I see, makes sense. I had over $400k invested at the peak in about 9,000 notes or so. At $1 per share that would be over 400,000 shares. Using the $1/share method would not give me the actual numbers of notes in the account.

    How do you record "Committed Cash" and "In Funding Loans"? Both show up in the Cash Details. I suppose "Funds lent" would be Buy transactions in Quicken.

    By the way, you obviously have a lot of experience with this and I appreciate the advice very much. Clearly, Quicken is not designed to support LC but it's what I am using to track my money.
  • Unknown
    Unknown Member
    edited March 2017
    RMW said:

    Let's say, I have a charge off of $500. Would you enter that as 0.00 Total Sale and 500 as the number of shares? The price per share is always greyed out and nothing can be entered.
    If I enter the $500 in the Total Sale field and 0 as the number of shares sold, my cash balance will increase by that amount. Which is what I wanted to avoid.

    I was never concerned about the number of notes, other than having enough to spread out the risk.  Of course I have browsed them on the website and such brought reports on them, but as far Quicken is concerned I didn't care.  And the number of shares "costs nothing" as far as performance or such in Quicken.  And Quicken can easily handle millions of shares.

    On the separating of "Committed Cash", "In Funding Loans" and "Available Cash".
    I never recorded any "Committed Cash".  It might be committed, but it hasn't bought anything yet, and it very possible for loans not to be funding and you get the money back.  That doesn't happen as much these days, but it happened a fair amount of time in the past, and can still happen.  BTW I have been investing in Lending Club notes since 2009.

    So I didn't care about separating these, they are combined in the cash balance of the account.  BTW to me "Committed Cash" and "in Funding Loans" are the same.

    So "Available Cash" + "Committed Cash" = cash balance of Quicken account.

    I was really only interested in "Available Cash" for the most part, because it was how much I needed to go in buy notes for.  But when I was buying notes manually I was logging in almost everyday and so that is how I knew what was available.  Once I switched to automatic, well it takes care of that.  I just log in every once in a while to monitor it.

    For transferring out, the only times I have done that is when I decided that I didn't want to have any more funds in taxable accounts (SEC rule changes made it not worth the trouble to me), so just stopped buy notes in those accounts, and then logged in ever once in a while to transfer money out of them.  I still have three IRA accounts though.

    I suppose if I was making transfers out to fund my spending, I would have a bit more concerned about the available cash.

    So "Outstanding Principal" is equal the the number of shares in the Quicken account.

    A note on the "buys".  If you are doing this off of the statement it is easy.
    The second page has "Funds Invested", that is the amount to buy.  (Principal received is the sell).

    If you want to do it more often and you are using the web page or the CSV file, you have to record how the values like Outstanding Principal from the last time and subtract it from the current values to get the difference to decide on what you bought.  This is or course quite a bit more complicated.
This discussion has been closed.