Author Topic: Calculation of ROI and IRR  (Read 5269 times)

viking

  • Sr. Member
  • ****
  • Posts: 370
    • View Profile
    • Email
Calculation of ROI and IRR
« on: November 09, 2012, 07:07:09 PM »
Lending Club calculates the "Net Annualized Return", supposedly using the formula described on their website:
http://www.lendingclub.com/public/lendersPerformanceHelp.action?v=2

I have not been able to match their calculated Return for my uploaded Notes with the calculated ROI (Return on Investment) or IRR (Internal Rate of Return) at Interest Radar or Nickel Stem Roller.
Furthermore, the ROI analysis/statistics calculations using LC historical data also seem to be be different at different websites.

How do you calculate the ROI (in Analysis Section) and IRR (Analysis Section; Cashflow and for my uploaded Notes). How do these calculations compare to that of the LC "Net Annualized Return"? Please show calculations with Formulas (like LC) if possible.

rev

  • Sr. Member
  • ****
  • Posts: 308
    • View Profile
    • Interest Radar
    • Email
Re: Calculation of ROI and IRR
« Reply #1 on: November 10, 2012, 09:51:57 AM »
Unfortunately LC does not provide payment history and charges history for the loans, so we can only guess these variables from what they provide, that is basically the date the loan was issued, original amount, total payments, interest rate and current balance.

My ROI is a very simple formula, that does not measure to any accounting standard, but is fast to calculate and can be used to compare to other ROI within Interest Radar only. What I mean is, if you do two strategies in IR and the first has a better ROI then the second, if means the first will yield you more return than the second. It's not meant to be compared to anything outside IR, like a CD or stocks or LC's ROI or any indicator in another site.
The ROI is simply the Interest Rate minus the Loss Rate that I define here: http://interestradar.wordpress.com/2012/08/30/definition-loss-rate/
So for example, the Loss Rate for a "In Grace Period" loan will be RemainingPrincipal / TotalAmountFunded * 16%

Now, the IRR is a different story. For the IRR what Interest Radar tries to do is to reconstruct the cash flow of the loan and then applies the IRR formula (just like you heve in Excel).
You can read a little bit more about this here: http://interestradar.wordpress.com/2012/08/30/my-portfolio-cash-flow/
I'm a programmer not a statistician, so I couldn't write down the mathematical notation of the formula, only describe the algorithm behind it.
The idea is to get the approximate IRR by guessing the payments received. The guess is basically done in an iteration: we start with the original amount, and for each month since the following month after the issuing date we subtract the principal paid from the balance. When it reaches the current balance, we stop and calculate the IRR of the cash flow, considering the current balance as a "next payment" if the status is Current.
This is obviously a rough estimate. It will work for maybe 90% of the loans, that should have the usual behavior: borrower receives the money, borrower pays the monthly installment until the loan is paid off or he stops paying at some point. For loans where the borrower makes erratic payments, for example sending more or less money than the agreed upon monthly payment, or stopping paying for a few months then starting paying back, the IRR calculated by IR will not match LC's, because they have all the information and we don't.
When a borrower just pays off the loan earlier, my IRR should match LC's.

Peter

  • Administrator
  • Hero Member
  • *****
  • Posts: 754
    • View Profile
    • Lend Academy
    • Email
Re: Calculation of ROI and IRR
« Reply #2 on: November 10, 2012, 01:13:47 PM »
Viking, Your Lending Club NAR will likely never match your real ROI because of several factors. First, it doesn't take into account the cash sitting in your account. Second, it fully values late loans even those that are about to default. Third, it ignores any trades made on the secondary market. I wrote an article on how I calculate my real return and I think it is important that all investors do this:
http://www.lendacademy.com/how-to-calculate-your-real-p2p-lending-return-with-xirr/

You should also check out this article from last year:
http://www.lendacademy.com/five-ways-to-measure-your-p2p-lending-roi/

You are going to find that the tools on Interest Radar or Nickel Steamroller provide you with a more accurate ROI than Lending Club.
Publisher of the Lend Academy blog

See my returns here: http://www.lendacademy.com/returns

viking

  • Sr. Member
  • ****
  • Posts: 370
    • View Profile
    • Email
Re: Calculation of ROI and IRR
« Reply #3 on: November 11, 2012, 10:34:42 PM »
My ROI is a very simple formula, that does not measure to any accounting standard, but is fast to calculate and can be used to compare to other ROI within Interest Radar only. What I mean is, if you do two strategies in IR and the first has a better ROI then the second, if means the first will yield you more return than the second. It's not meant to be compared to anything outside IR, like a CD or stocks or LC's ROI or any indicator in another site.
The ROI is simply the Interest Rate minus the Loss Rate that I define here: http://interestradar.wordpress.com/2012/08/30/definition-loss-rate/
So for example, the Loss Rate for a "In Grace Period" loan will be RemainingPrincipal / TotalAmountFunded * 16%

First, if I understand correctly, for a single loan you calculate the ROI as:

ROI = InterestRate - Loss
where Loss=LossRate * RemainingPrincipal / TotalAmountFunded
and, for example, the LossRate = 0 for a current loan and 1 for a defaulted loan (extreme cases)

For all loans, that match the filter criteria, your Analysis screen shows:
Interest (or Av. Interest; %) = The average of the interest rate of all matching loans
Loss (or Av. Loss; %) = The average of the Loss of all matching loans

For the ROI, we can use either:
A. ROI=The average of the ROI of all matching loans
or
B. ROI= Av. Interest - Av. Loss
(it should give same results)

I tried the calculations on a simple example using the Downloaded Statistics from LC and Interest Radars Analysis screen using the following Filters:
Credit Grade = G (all subgrades)
Loan Length = 36 months
Loan Purpose = Other
Inquiries = 1

Interest Radar gave me 3 loans (1 loan with a loss) with:
Av. Interest = 21.2%
Av. Loss=6.0%
Av. ROI= 15.1%

Note that  Av. Interest - Av. Loss = 15.2% which is approximately the same as Av. ROI=15.1% (maybe rounding error?)

When using the LC csv file, I have two choices when selecting the "Amount Funded" as well as any "Remaining Principal" (for Charged Off loans).
In the LC file I have:
Amount Funded By Investors (col C)
Total Amount Funded (col O)
Remaining Principal Funded by Investors (col Q)
Remaining Principal (col S)

What is the difference between "Amount Funded By Investors" and "Total Amount Funded" ? ???
If not funded by investors, does LC contribute with the difference...?


I calculated the ROI using both alternatives, where
Alt 1: Loss= LossRate*Remaining Principal Funded by Investors/Amount Funded By Investors
Alt 2: Loss= LossRate*Remaining Principal/Total Amount

The filtered LC cvs file gave me 3 loans with 2 current loans and 1 charged of loan;
Alt 1:
LoanID     InterestRate     Calculated Loss     Calculated ROI (=InterestRate - Loss)
362421       19.04%         23.79%               -4.75%
579815       20.16%           0.00%               20.16%
1208692     24.33%           0.00%               24.33%

Averages:    21.18%           7.93%               13.25%
(Alternatively, note that ROI = 21.18%-7.93% = 13.25% as well)

Alt 2:
LoanID     InterestRate     Calculated Loss     Calculated ROI (=InterestRate - Loss)
362421       19.04%         18.09%               0.95%
579815       20.16%           0.00%               20.16%
1208692     24.33%           0.00%               24.33%

Averages:    21.18%           6.03%               15.15%
(Alternatively, note that ROI = 21.18%-6.03% = 15.15% as well)

Alternative 2 matches your results, so that is obviously how you calculate it (using Total Amount rather than Amount Funded by Investors). 

« Last Edit: November 12, 2012, 03:13:07 AM by viking »