Author Topic: How to calculate Peer Lending returns  (Read 8038 times)

Emmanuel

  • Full Member
  • ***
  • Posts: 157
    • View Profile
    • LendingRobot
How to calculate Peer Lending returns
« on: April 16, 2014, 09:08:04 PM »
It seems each marketplace has its own way of calculating returns, none of them fully satisfying.
Maybe it's time to settle on something consistent...

So, here's our proposal: http://marotte.net/LendingRobot_CalculatingReturns.html

(Still a draft, I'll move it to LendingRobot.com once it's final)

Any comments, suggestions or thoughts ??

core

  • Hero Member
  • *****
  • Posts: 1784
  • Your loss is my gain
    • View Profile
Re: How to calculate Peer Lending returns
« Reply #1 on: April 16, 2014, 09:22:12 PM »
I think this is how BetaMax started, Emmanuel.  Best wishes.

Not that I don't have beta tapes that I cherish.  Just saying.

Fred

  • Hero Member
  • *****
  • Posts: 1421
    • View Profile
Re: How to calculate Peer Lending returns
« Reply #2 on: April 16, 2014, 09:59:01 PM »
Quote
The 'n/a' number means it was impossible to compute the corresponding number, because at least one loan did not make any payments.

Can you tell me why, in the last table, the n/a's are only for Arithmetic Averages, but not for Dollar-Weighted?

Furthermore, for loans with no payments, I would assume the ROI would be -100%?

Emmanuel

  • Full Member
  • ***
  • Posts: 157
    • View Profile
    • LendingRobot
Re: How to calculate Peer Lending returns
« Reply #3 on: April 17, 2014, 10:49:30 AM »
Quote
The 'n/a' number means it was impossible to compute the corresponding number, because at least one loan did not make any payments.

Can you tell me why, in the last table, the n/a's are only for Arithmetic Averages, but not for Dollar-Weighted?

Furthermore, for loans with no payments, I would assume the ROI would be -100%?

With the dollar-weigthed average, it's the sum of all payments versus the sum of all fundings, so loans that defaulted immediately are thinned down with the rest. With arithmetic average, each return has to be calculated individually.

Yes, as shown in Calculation Methods > Return on Investment, for loans with no payments, the ROI is -100%. The problem is with many other methods, once cannot cannot it.




Emmanuel

  • Full Member
  • ***
  • Posts: 157
    • View Profile
    • LendingRobot
Re: How to calculate Peer Lending returns
« Reply #4 on: April 17, 2014, 10:53:12 AM »
I think this is how BetaMax started, Emmanuel.  Best wishes.

Not that I don't have beta tapes that I cherish.  Just saying.

In Europe we got http://en.wikipedia.org/wiki/Video_2000, there were way better than VHS!

We resisted the temptation to design our own fancy, proprietary calculation method. Dietz returns are more like http://en.wikipedia.org/wiki/U-matic, a professional standard...

Fred

  • Hero Member
  • *****
  • Posts: 1421
    • View Profile
Re: How to calculate Peer Lending returns
« Reply #5 on: April 17, 2014, 08:41:45 PM »
In general, if you can get returns on dollar-weighted average, you should be able to get returns on arithmetic average.

With the dollar-weigthed average, it's the sum of all payments ...

For no-payment loans: what payments?

With arithmetic average, each return has to be calculated individually.

There should be a boundary condition for no-payment loans, so that the returns are simply -100%.  No 'calculations'  are required.  This applies to the basic ROI, Alternative ROI, and even IRR.

Emmanuel

  • Full Member
  • ***
  • Posts: 157
    • View Profile
    • LendingRobot
Re: How to calculate Peer Lending returns
« Reply #6 on: April 18, 2014, 11:02:12 AM »
In general, if you can get returns on dollar-weighted average, you should be able to get returns on arithmetic average.

With the dollar-weigthed average, it's the sum of all payments ...

For no-payment loans: what payments?

Let's say we have 3 loans, each of $5,000. Loan #1 paid back $5,500, loan #2 paid back #6,000 and loan #3 paid back nothing, zero.

The dollar-weighted average ROI is (6000+5500+0 - 5000*3) / (5000 * 3), the no-payment is included in in the numerator.


With arithmetic average, each return has to be calculated individually.

There should be a boundary condition for no-payment loans, so that the returns are simply -100%.  No 'calculations'  are required.  This applies to the basic ROI, Alternative ROI, and even IRR.

Unfortunately, that could cause loans that made few payments to have a lower returns that ones which did not make any payments at all.
I show an example in the paper where a $5,000 loan that made 3 payments has an IRR of -757%, way below -100% then.

A solution would be to limit negative returns to -100%, but any method that bounds the losses but not the gains seems fishy.

Fred

  • Hero Member
  • *****
  • Posts: 1421
    • View Profile
Re: How to calculate Peer Lending returns
« Reply #7 on: April 18, 2014, 12:36:21 PM »
In general, if you can get returns on dollar-weighted average, you should be able to get returns on arithmetic average.

With the dollar-weigthed average, it's the sum of all payments ...

For no-payment loans: what payments?

Let's say we have 3 loans, each of $5,000. Loan #1 paid back $5,500, loan #2 paid back #6,000 and loan #3 paid back nothing, zero.

The dollar-weighted average ROI is (6000+5500+0 - 5000*3) / (5000 * 3), the no-payment is included in in the numerator.

Exactly. 

My question was related to the 'n/a' on arithmetic averages in the bottom table of your article: if you did get dollar-weighted average, why couldn't you get the arithmetic average?

Unfortunately, that could cause loans that made few payments to have a lower returns that ones which did not make any payments at all.
I show an example in the paper where a $5,000 loan that made 3 payments has an IRR of -757%, way below -100% then.

A solution would be to limit negative returns to -100%, but any method that bounds the losses but not the gains seems fishy.

Not sure how you got -757% for loan D (that made 3 payments) in your paper.



For the above cash flow schedule, Excel shows XIRR as -100% (-99.9993949% to be more precise).  Perhaps I missed something here?

Yes, when fees are considered, a few cases would lead to returns (slightly) worse than -100%.  This is reality, and we should allow that in the model.  No hard-limit of -100% is required.

(The -100% return for no-payment is still needed to prevent 'n/a').

Emmanuel

  • Full Member
  • ***
  • Posts: 157
    • View Profile
    • LendingRobot
Re: How to calculate Peer Lending returns
« Reply #8 on: April 18, 2014, 08:51:00 PM »
My question was related to the 'n/a' on arithmetic averages in the bottom table of your article: if you did get dollar-weighted average, why couldn't you get the arithmetic average?

if F is the function to calculate the return, a, b, c the loans, c not making any payment, then F(c) is not a number, hence the inability to average F(a)+F(b)+F(c), while F(a+b+c) can still be calculated.


Not sure how you got -757% for loan D (that made 3 payments) in your paper.



For the above cash flow schedule, Excel shows XIRR as -100% (-99.9993949% to be more precise).  Perhaps I missed something here?

I'm using the IRR, which gives -63%, annualizing it by multiplying by 12 gives the -757%. I agree the XIRR is more robust, but I rather used the IRR function because it matches the canonical definition of the Internal Rate of Return.

RaymondG

  • Full Member
  • ***
  • Posts: 247
    • View Profile
    • Email
Re: How to calculate Peer Lending returns
« Reply #9 on: April 18, 2014, 09:01:29 PM »
The cash flow in the image is not complete for calculating XIRR for this period. You should assume the whole account will be withdrawed at the end. So, you should add 5000 into cashflow in 4/1/2014. This cash flow assumes that net gain ($166.79) was withdrawed in every month.


I normally use a different approach. Treating the LC account as blackbox, the net cash flow would include only the following:
* Net account value at the beginning of the period
* Cash added to the LC account in the months within the period.
* Cash withdrawed from the account within the period
* The account's total net Deposit/WD in last month at the end of the period. (ignoring production from new money, and adding back withdrawals in the last month)

An example is given in the image: (Account total is col Y)
Formula for
          1st line: =-(Y13+Z13+AA13)    -- Except Deposit/WD, Y13, Z13, AA13 are as of END of September, 2013.
                                                               "Val of Bad..." is estimated loss from non-performing loans.
           other lines: =IF(W16=$X$9,Y16+Z16+AA16+AB16, IF(W16<$X$9, AB16,"")),   
                                where $X$9 = 47


« Last Edit: April 18, 2014, 09:28:39 PM by RaymondG »

Fred93

  • Hero Member
  • *****
  • Posts: 2235
    • View Profile
Re: How to calculate Peer Lending returns
« Reply #10 on: April 18, 2014, 09:02:54 PM »
if F is the function to calculate the return, a, b, c the loans, c not making any payment, then F(c) is not a number, hence the inability to average F(a)+F(b)+F(c), while F(a+b+c) can still be calculated.

You should never average "returns".  The average of  the returns of the notes in a portfolio is not the same as the return of the portfolio.  You should always calculate the return of the portfolio instead.  Basic math issue.


Quote
I'm using the IRR, which gives -63%, annualizing it by multiplying by 12 gives the -757%. I agree the XIRR is more robust, but I rather used the IRR function because it matches the canonical definition of the Internal Rate of Return.

It is simply not proper to mutliply IRR by 12.  This produces garbage.  Basic math issue.

However, if you had done (1-0.63)^12-1 you would have produced -99.99999%, ie -100%

You have to do the basic math correctly when evaluating these computational methods.
« Last Edit: April 18, 2014, 10:02:30 PM by Fred93 »

Fred

  • Hero Member
  • *****
  • Posts: 1421
    • View Profile
Re: How to calculate Peer Lending returns
« Reply #11 on: April 18, 2014, 09:51:08 PM »
if F is the function to calculate the return, a, b, c the loans, c not making any payment, then F(c) is not a number, hence the inability to average F(a)+F(b)+F(c), while F(a+b+c) can still be calculated.

You are missing the point.  For no-payment loan c, F(c) does not need to be calculated.  F(c) = -100%, and is a number.

Emmanuel

  • Full Member
  • ***
  • Posts: 157
    • View Profile
    • LendingRobot
Re: How to calculate Peer Lending returns
« Reply #12 on: April 21, 2014, 10:53:19 AM »

You should never average "returns".  The average of  the returns of the notes in a portfolio is not the same as the return of the portfolio.  You should always calculate the return of the portfolio instead.  Basic math issue.


Indeed. That's why I gave an example of how they differ in the preamble.

I should better clarify that arithmetic average is for calculating ex-ante returns, i.e., not inside a portfolio, but for estimating potential market return. If the selecting mechanism of an investors identifies 100 'good' loans, as without knowing a specific allocation method, my hypothesis is that he'd invest the same, small amount in each of those loans, and therefore the return he can expect equals the arithmetic average.

Thanks for your feedback!


 


twigster

  • Jr. Member
  • **
  • Posts: 95
    • View Profile
    • Email
Re: How to calculate Peer Lending returns
« Reply #13 on: April 29, 2014, 07:38:33 AM »
I was reading this thinking what is wrong with the XIRR?  Here is a interesting link where the guy shows problems with some XIRR results.    https://www.mhj3.com/excel.htm  I doubt these type of 'wierd' results occur in most simple examples where the account balance never goes negative.

Also the paper mentions the Dietz method which gives somewhat similar results to the XIRR but not exactly the same. 
There is an interesting write up and excel showing both XIRR and Dietz used on an investment portfolio and comparing results.
http://www.financialwisdomforum.org/gummy-stuff/Dietz.htm‎  The results in this example are very similar, 8.19% for Dietz Annual Return and 8.40% for XIRR.
« Last Edit: April 29, 2014, 08:05:18 AM by twigster »

Emmanuel

  • Full Member
  • ***
  • Posts: 157
    • View Profile
    • LendingRobot
Re: How to calculate Peer Lending returns
« Reply #14 on: April 29, 2014, 07:40:00 PM »
Thanks for pointing to these examples!