Author Topic: Possible to see how many payments remaining?  (Read 655 times)

rj2

  • Jr. Member
  • **
  • Posts: 80
    • View Profile
Possible to see how many payments remaining?
« on: February 04, 2019, 01:37:43 AM »
I was looking around the LC site and also downloaded the spreadsheets on the "notes" page, trying to see if I could see how many payments remain on my notes. I found lots of data, but didn't anywhere see the number of remaining payments. I can see the term, outstanding balance, amount of payment, date of first payment, but not how many are left.

What I'd like to do: Calculate the duration of my portfolio. If I can't do that, I'd settle for calculating the average time to maturity of my notes. But if I can calculate the weighted average, I could probably do it properly and calculate the Macaulay duration.

For example, if I had only two loans with the same payment and one had 10 payments left and the other 20 then the average remaining is 15.

Why: I am trying to reduce the risk in my portfolio in anticipation of a potential economic downturn. I am increasingly buying lower interest loans, and I have stopped buying 60 month loans. I'm even considering selling off the higher interest, longer duration loans. Anyway, I was hoping to track this number to measure the difference I'm making as I do this. Also, if I do decide to exit LC at some point it strikes me that it's easier to get out of a short-duration porftolio than a longer duration one, as in, fewer notes need to be sold and more can simply be waited-out.

As an aside: duration is such a fundamental, common concept in fixed income that I'm surprised LC isn't just showing it to me everywhere. Weird the numbers they decide to give me.
« Last Edit: February 04, 2019, 01:56:48 AM by rj2 »

Fred93

  • Hero Member
  • *****
  • Posts: 2129
    • View Profile
Re: Possible to see how many payments remaining?
« Reply #1 on: February 04, 2019, 08:55:39 AM »
I was looking around the LC site and also downloaded the spreadsheets on the "notes" page, trying to see if I could see how many payments remain on my notes. I found lots of data, but didn't anywhere see the number of remaining payments. I can see the term, outstanding balance, amount of payment, date of first payment, but not how many are left.

You can calculate payments remaining from the columns they have given you.

lascott

  • Hero Member
  • *****
  • Posts: 1410
    • View Profile
    • Appreciate my post and want to try LendingRobot? URL below
Re: Possible to see how many payments remaining?
« Reply #2 on: February 04, 2019, 11:51:40 AM »
... date of first payment, but not how many are left. ...

So why would you use the rough math of:
(loan_term_in_months) - ( (today() - "date of first payment") / (365.2425/12) )
Tools I use: (main) BlueVestment: https://www.bluevestment.com/app/pricing + https://www.interestradar.com/ , (others) Lending Robot referral link: https://www.lendingrobot.com/ref/scott473/  & Peercube referral code: DFVA9Y

rj2

  • Jr. Member
  • **
  • Posts: 80
    • View Profile
Re: Possible to see how many payments remaining?
« Reply #3 on: February 04, 2019, 02:27:11 PM »
People can pre-pay their loans and reduce the number of remaining payments and it's a pretty common thing that I have seen a lot of people do

It didn't seem so simple but maybe there's no better way.

I am amazed that LC doesn't consider duration an important number to communicate given how fundamental it is to any fixed income portfolio analysis. Seems like they provide a bunch of random data nobody actually cares about instead of the stuff people usually look at.

Fred93

  • Hero Member
  • *****
  • Posts: 2129
    • View Profile
Re: Possible to see how many payments remaining?
« Reply #4 on: February 04, 2019, 04:33:43 PM »
I am amazed that LC doesn't consider duration an important number to communicate given how fundamental it is to any fixed income portfolio analysis. Seems like they provide a bunch of random data nobody actually cares about instead of the stuff people usually look at.

Duration is an important concept, but maybe you're making it too complicated.  These are ALL very low duration notes.

Duration is the weighted average time from now until you get your money back.  For a bond, which has little payments along the way and a big payment at the end, you need a formula.  For declining balance loans, things are much simpler.  A 36 month loan (presuming borrower keeps to the schedule), pays 36 EQUAL SIZED PAYMENTS and nothing more.  Hence, when the note is new, the duration is 36/2 = 18 months.

But a significant # borrowers prepay, so in practice duration is more like 12 to 15 months.  Lets call it 13.5 months.

After you reinvest for awhile, you have a mix of different ages of notes, so the duration of your portfolio is even shorter.  As your portfolio ages, you will end up with a uniform mix of different ages of notes with 0 to 36 months left on them.  These notes will have durations from 0 to 13.5 months.  Again, a uniform mix, so its is pretty easy to calculate the average of that mix.  Its something like 7 months.

So a portfolio of LC notes is a VERY short duration investment.

rj2

  • Jr. Member
  • **
  • Posts: 80
    • View Profile
Re: Possible to see how many payments remaining?
« Reply #5 on: February 04, 2019, 06:58:27 PM »
Well. Until now I had been ignoring the length of notes and using auto-buy and manual-buy rules that looked at other factors, ignoring the 36/60 difference.

Now I am actively trying to reduce risk in my note portfolio by doing two things. First, I have moved to buying lower interest rate loans, and second I have switched to buying only 36 months loans, or in Folio, loans that have less than 36 months remaining (which may have begun life as 60 month loans).

Actually what I considered doing was strictly buying loans with a duration LESS than the current duration of my portfolio. In other words, if I found that my current average duration was 18 months, then only buying folio notes with 18 months or less on them.

This will have the effect of keeping my money invested while dramatically reducing the amount of time it would take to exit LC if I decided to do that, which I may decide to do if LC gets clobbered in what I assume is an upcoming recession. On the flip side, if said recession never occurs and things are looking rosy, I might resume buying higher interest, longer duration notes.

Anyway I was hoping to find a way to track my progress. I guess there is no good way, I'll just average the number of payments the way you said and give up on calculating the actual duration.
« Last Edit: February 04, 2019, 07:00:05 PM by rj2 »

AnilG

  • Hero Member
  • *****
  • Posts: 1088
    • View Profile
    • PeerCube
Re: Possible to see how many payments remaining?
« Reply #6 on: February 11, 2019, 12:10:45 AM »
You can easily calculate actual duration of a loan from information listed in portfolio notes CSV file:

Code: [Select]
Number of Payments Remaining = -log( 1 - (InterestRate * out_prncp / (12 * 100 * installment) ) ) / log ( 1 + InterestRate / (12 * 100) )
Do this for all loans (not paid off or charged off) in your portfolio and then calculate weighted portfolio duration.

Code: [Select]
Weighted portfolio duration = Sum of (Number of Payments Remaining * out_prncp) / Sum of (out_prncp)
---
Anil Gupta
PeerCube Thoughts blog https://www.peercube.com/blog
PeerCube https://www.peercube.com

rj2

  • Jr. Member
  • **
  • Posts: 80
    • View Profile
Re: Possible to see how many payments remaining?
« Reply #7 on: February 13, 2019, 01:07:03 AM »
Thanks for the suggestion!

I actually did finally find a way to get this directly off the Lending Club site, it's a bit of a hack, but I thought I'd share.

Go to folio trading platform, go to the sell notes page, select to view all notes and select all, then click sell. This will take you to a page with (almost) all your active notes in a big table where you could set selling price and it DOES include the number of payments remaining. Don't bother setting selling prices, just highlight the whole table and paste it to an excel sheet then CANCEL the sell workflow (they aren't being sold yet and you presumably don't actually want to sell them).

With that data pasted to excel it's easy to sum, average, sumproduct in a few seconds. I learned that my longest term note has 50 months remaining, and my overall average has 22.4 months with a weighted average duration of 25.6 months. That let me know to compare my LC portfolio to a 24 month CD--they currently pay 3% whereas I have so far earned 5%.  It also let me know if I want to reduce the duration of my portfolio I need to buy notes with less than 26 months remaining, and if I want to strictly run it down, less than 50 for now to stay under my longest--though only 8% of my notes have more than 36 months remaining at this point.

So this is exactly what I was looking for! It's not a 100% solution since a few notes will be excluded for various reason (pending payment, etc.) but it was good enough for the overview I was looking for.

Don't know why LC doesn't display those values on other screens, but it's on THAT screen, so it works.
« Last Edit: February 13, 2019, 01:29:00 AM by rj2 »

AnilG

  • Hero Member
  • *****
  • Posts: 1088
    • View Profile
    • PeerCube
Re: Possible to see how many payments remaining?
« Reply #8 on: February 13, 2019, 06:05:01 AM »
Cool, workaround to find number of payments. Folio sell inventory list CSV file also has the number of payments remaining for all notes listed for sale. I also liked your idea of comparing your portfolio with CD of similar duration. It puts in perspective whether your loan portfolio is doing better than a similar duration mostly secured product (CD) or not.

Thanks for the suggestion!

I actually did finally find a way to get this directly off the Lending Club site, it's a bit of a hack, but I thought I'd share.

Go to folio trading platform, go to the sell notes page, select to view all notes and select all, then click sell. This will take you to a page with (almost) all your active notes in a big table where you could set selling price and it DOES include the number of payments remaining. Don't bother setting selling prices, just highlight the whole table and paste it to an excel sheet then CANCEL the sell workflow (they aren't being sold yet and you presumably don't actually want to sell them).

With that data pasted to excel it's easy to sum, average, sumproduct in a few seconds. I learned that my longest term note has 50 months remaining, and my overall average has 22.4 months with a weighted average duration of 25.6 months. That let me know to compare my LC portfolio to a 24 month CD--they currently pay 3% whereas I have so far earned 5%. It also let me know if I want to reduce the duration of my portfolio I need to buy notes with less than 26 months remaining, and if I want to strictly run it down, less than 50 for now to stay under my longest--though only 8% of my notes have more than 36 months remaining at this point.

So this is exactly what I was looking for! It's not a 100% solution since a few notes will be excluded for various reason (pending payment, etc.) but it was good enough for the overview I was looking for.

Don't know why LC doesn't display those values on other screens, but it's on THAT screen, so it works.
---
Anil Gupta
PeerCube Thoughts blog https://www.peercube.com/blog
PeerCube https://www.peercube.com