Author Topic: Interesting calcs using LC Portfolio tab info?  (Read 4500 times)

lascott

  • Hero Member
  • *****
  • Posts: 1396
    • View Profile
    • Appreciate my post and want to try LendingRobot? URL below
Interesting calcs using LC Portfolio tab info?
« on: August 31, 2014, 03:13:47 PM »
What interesting calculations or insights have you folks done or seen just using the LC Portfolio tab info?? Please share some.

Here is a shared google doc that you can view/copy.  Yellow is from a real LC account (but not mine). White are my additions. =PMT was done using 36 months (I don't know if some 60s are in there).

Personally, I like the EMP/OP percentage (~3.72%) to estimate if I invest X I can expect Y in monthly payments.

https://docs.google.com/spreadsheets/d/1AnRU_nS6qC9z9blhXE3LSShQkWws6Q1pe0Iy3QS45Hw/edit?usp=sharing

« Last Edit: August 31, 2014, 09:32:37 PM by lascott »
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

rawraw

  • Hero Member
  • *****
  • Posts: 2756
    • View Profile
Re: Interesting calcs using LC Portfolio tab info?
« Reply #1 on: August 31, 2014, 05:35:53 PM »
Your rate calculation is incorrect in the PMT function.  May want to Google the correct way to calculate a monthly interest rate from an annual one.  The difference won't be huge, but probably don't want sloppy formulas like that in your work of art :)

You could also just put a negative sign in front of the variable, instead of multiplying times -1

hoggy1

  • Sr. Member
  • ****
  • Posts: 401
    • View Profile
    • Email
Re: Interesting calcs using LC Portfolio tab info?
« Reply #2 on: August 31, 2014, 05:41:51 PM »
Yeah, I didn't understand why your difference column was so big either. Something is wrong.
Steve

hoggy1

  • Sr. Member
  • ****
  • Posts: 401
    • View Profile
    • Email
Re: Interesting calcs using LC Portfolio tab info?
« Reply #3 on: August 31, 2014, 05:43:12 PM »
Oh, I know whats wrong. They are not all 36 month loans.
Steve

lascott

  • Hero Member
  • *****
  • Posts: 1396
    • View Profile
    • Appreciate my post and want to try LendingRobot? URL below
Re: Interesting calcs using LC Portfolio tab info?
« Reply #4 on: August 31, 2014, 07:44:39 PM »
I've tried several things in the past with the PMT formula using the LC Portfolio data and can make it match. (i.e. removing the /100)  I know the loan length on those and still can't make it match.

So have you guys tried something similar with your Portfolio tab data and got it to match?

BTW, I cut-n-paste the whole Portfolio tab data in one fell swoop.
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

rawraw

  • Hero Member
  • *****
  • Posts: 2756
    • View Profile
Re: Interesting calcs using LC Portfolio tab info?
« Reply #5 on: August 31, 2014, 08:11:16 PM »
The formula for interest rate isn't simply dividing it.

The formula for changing from an annual percentage rate to a semiannual, quarterly, or monthly one is straightforward. In general, given an annual rate:


Effective rate for period = (1 + annual rate)^(1 / # of periods) – 1

So for monthly, quarterly, and semiannual rates, the math becomes:


Monthly rate = (1 + annual rate)^(1/12) – 1
Quarterly rate = (1 + annual rate )^(1/4) – 1
Semiannual rate = (1 + annual rate)^(1/2) – 1
« Last Edit: August 31, 2014, 08:13:04 PM by rawraw »

lascott

  • Hero Member
  • *****
  • Posts: 1396
    • View Profile
    • Appreciate my post and want to try LendingRobot? URL below
Re: Interesting calcs using LC Portfolio tab info?
« Reply #6 on: August 31, 2014, 08:37:22 PM »
The formula for interest rate isn't simply dividing it.
The formula for changing from an annual percentage rate to a semiannual, quarterly, or monthly one is straightforward. In general, given an annual rate:
Effective rate for period = (1 + annual rate)^(1 / # of periods) – 1
So for monthly, quarterly, and semiannual rates, the math becomes:
Monthly rate = (1 + annual rate)^(1/12) – 1
Quarterly rate = (1 + annual rate )^(1/4) – 1
Semiannual rate = (1 + annual rate)^(1/2) – 1
I see similar descriptions: http://www.experiglot.com/2006/06/07/how-to-convert-from-an-annual-rate-to-an-effective-periodic-rate-javascript-calculator/
Quote
if you have a loan with an annual percentage rate of 6% and want to calculate the amount you’re paying each month, your effective rate each month isn’t 0.5% but 0.486%. Effective rates take the impact of compounding into account, whereas simply dividing one rate by the number of periods ignores this factor.

I found an EFFECT function: https://support.google.com/docs/answer/3093223
Quote
Calculates the annual effective interest rate given the nominal rate and number of compounding periods per year.
EFFECT(nominal_rate, periods_per_year)
nominal_rate - The nominal interest rate per year.
periods_per_year - The number of compounding periods per year.

I looked up various example of using PMT but none of them showed using the effective rate (or function). I did try it but have not been able to get the EMP via LC to be close to =PMT function.



Potentially need to use the EFFECT function twice per this desc: http://support.microsoft.com/kb/291106
« Last Edit: August 31, 2014, 08:39:38 PM by lascott »
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

rawraw

  • Hero Member
  • *****
  • Posts: 2756
    • View Profile
Re: Interesting calcs using LC Portfolio tab info?
« Reply #7 on: September 01, 2014, 05:59:04 AM »
I just copied pasted it. I wasn't typing that on a cell phone :-)

Why are you dividing the effect results by twelve
« Last Edit: September 01, 2014, 06:01:22 AM by rawraw »

lascott

  • Hero Member
  • *****
  • Posts: 1396
    • View Profile
    • Appreciate my post and want to try LendingRobot? URL below
Re: Interesting calcs using LC Portfolio tab info?
« Reply #8 on: September 01, 2014, 09:33:01 AM »
I just copied pasted it. I wasn't typing that on a cell phone :-)
No worries. I wasn't attempting to imply anything negative at all. I just found another example with another explanation that I quoted. I appreciated the interaction/education.

Why are you dividing the effect results by twelve?
Because EFFECT function returns the annual rate and PMT expects the input in months. I put EFFECT result in column E in my new graphic above.

Actually in all this I thought the percentage of Expected Monthly Payments (EMP) to the Outstanding Principle (OP) at each grade was more interesting than my attempt to duplicate the EMP using the PMT function.   I have separate portfolio names for "grades  and terms" for my Bluevest-n-P2PPicks notes as well as my LendingRobot notes.  Since the portfolio name is on many of the LC user interface tables it is nice to see where this note came from at a glance (ie. in the default list).  Plus I can do some of the above calcs on a fine breakdown.
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

rawraw

  • Hero Member
  • *****
  • Posts: 2756
    • View Profile
Re: Interesting calcs using LC Portfolio tab info?
« Reply #9 on: September 01, 2014, 09:44:54 AM »
I'm on my computer now.  I still don't understand what you are doing


lascott

  • Hero Member
  • *****
  • Posts: 1396
    • View Profile
    • Appreciate my post and want to try LendingRobot? URL below
Re: Interesting calcs using LC Portfolio tab info?
« Reply #10 on: September 01, 2014, 10:06:53 AM »
I'm on my computer now.  I still don't understand what you are doing
Perhaps I am missing something but all =PMT examples I've seen divide by 12 to get a monthly (period) rate. EFFECT is documented as an annual rate.  Without dividing by 12 in my PMT forumula the monthly payment is way off.



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

rawraw

  • Hero Member
  • *****
  • Posts: 2756
    • View Profile
Re: Interesting calcs using LC Portfolio tab info?
« Reply #11 on: September 01, 2014, 10:10:41 AM »
It depends on the type of rate. Pretty sure LC rates are ones that can't be divided. It's been a few years since I studied this stuff, but Fred could confirm or deny

rawraw

  • Hero Member
  • *****
  • Posts: 2756
    • View Profile
Re: Interesting calcs using LC Portfolio tab info?
« Reply #12 on: September 01, 2014, 10:16:37 AM »
From the Web

One of the most common mistakes made in when calculating DCFs and seen all too often in corporate finance analyses is forgetting to properly adjust an annual percentage ratefor the period in question.

For example, if your cost of capital is 12% on an annual basis, and you’re valuing a project that lasts only a quarter, then the discount rate you use in your DCF analysis shouldn’t be 12% (the most common mistake), or even 3% (e.g. 12% / 4 quarters), but 2.87%.

Likewise, if you have a loan with an annual percentage rate of 6% and want to calculate the amount you’re paying each month, your effective rate each month isn’t 0.5% but 0.486%. Effective rates take the impact of compounding into account, whereas simply dividing one rate by the number of periods ignores this factor.


hoggy1

  • Sr. Member
  • ****
  • Posts: 401
    • View Profile
    • Email
Re: Interesting calcs using LC Portfolio tab info?
« Reply #13 on: September 01, 2014, 10:37:10 AM »
Actually in all this I thought the percentage of Expected Monthly Payments (EMP) to the Outstanding Principle (OP) at each grade was more interesting than my attempt to duplicate the EMP using the PMT function.   I have separate portfolio names for "grades  and terms" for my Bluevest-n-P2PPicks notes as well as my LendingRobot notes.  Since the portfolio name is on many of the LC user interface tables it is nice to see where this note came from at a glance (ie. in the default list).  Plus I can do some of the above calcs on a fine breakdown.

Sorry Scott,

I'm with rawraw; confused; especially with what you think you see in the ratio you define above.

Pretend instead of a group of loans you are looking at a single loan. The loans payments are always the same and the outstanding principal is reduced with each payment. So the ratio get larger each month. Exactly what the ratio is is a fixed by the term, interest rate, payment amount, and how many payments have already been made. To see anything meaningful in this number you would have to know the age of loan(s) (# of payments) but that is not even in you table?

Are we missing something?
Steve

lascott

  • Hero Member
  • *****
  • Posts: 1396
    • View Profile
    • Appreciate my post and want to try LendingRobot? URL below
Re: Interesting calcs using LC Portfolio tab info?
« Reply #14 on: September 01, 2014, 12:54:53 PM »
Actually in all this I thought the percentage of Expected Monthly Payments (EMP) to the Outstanding Principle (OP) at each grade was more interesting than my attempt to duplicate the EMP using the PMT function.   I have separate portfolio names for "grades  and terms" for my Bluevest-n-P2PPicks notes as well as my LendingRobot notes.  Since the portfolio name is on many of the LC user interface tables it is nice to see where this note came from at a glance (ie. in the default list).  Plus I can do some of the above calcs on a fine breakdown.
Sorry Scott, I'm with rawraw; confused; especially with what you think you see in the ratio you define above.

Pretend instead of a group of loans you are looking at a single loan. The loans payments are always the same and the outstanding principal is reduced with each payment. So the ratio get larger each month. Exactly what the ratio is is a fixed by the term, interest rate, payment amount, and how many payments have already been made. To see anything meaningful in this number you would have to know the age of loan(s) (# of payments) but that is not even in you table?

Are we missing something?
I reject your reality and substitute my own. <grin>  I appreciate both your guys patience.  I'm am seeing flaw in my calcs because I don't have some details like term.  However, I really started this thread thinking people may play with their own Portfolio tab information where they would have some idea of the the terms they use.  I thought some people would have already played with this information like I did and would share some of their calcs.  Perhaps it is too generalized of information to gain insight from.

I realize I was working with some generalizations when I use the outstanding principle (OP) but I absolutely was not trying to think of it as a single loan with varying principal.  Instead I was thinking with all the notes that it would be an ongoing average of principle (see blue box in graphic below) assuming you have new notes coming in with high outstanding principle and existing notes getting older with low outstanding principle.

I thought on such a large and older LC account that I was seeing a way to roughly estimate cashflow (Expected Monthly Payments (EMP)).

I think in the back of my head it was along the lines of this old post by Fred93 that I recalled (and just now re-found in a search).
Once your money is invested in loans every month (actually daily) some amount will be returned to your cash account as earned interest and principal paid. If your average loan return is 10%, monthly approximately 1% of your total notes will be back in your cash account. If you need a small amount of money, this can be withdrawn easily.
Agree with the principal.  The amount is higher tho.

Suppose you have a mix of 36 month and 60 month loans.  Lets call the average 48 months.  Your entire principal is paid back over 48 months, so principal alone averages a little more than 2% per month.  (ie 1/48 = about 2%)  On top of that there's interest, which might be nearly another 1% per month.  Then a significant fraction of borrowers pay off early, so in practice there's even more, maybe another 0.5%   That adds up to around 3.5%/month cash becoming available.

If you recycle this money by buying new loans, it continues.  If you take it out or fail to reinvest it, then the amount coming out goes down each month. 

This is of course a cash flow calculation, not a returns calculation.

Obviously this is for 36 month term but if you constantly purchased new notes then I thought your ongoing average of principle for your account ought be consistent.  The blue box is just showing the generalized area and concept of an average principle.

« Last Edit: September 02, 2014, 09:11:39 AM by lascott »
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