I am currently looking through the data file that is provided for export by the lending club, and I am a little confused on how you would go about calculating the ROI for an investment. Just as an example, I will make it very simplified. For example, in their data file there is a loan that has been fully paid without any charge-offs or late fees, and it was completed exactly on time.

The principal amount of the loan is $2400, the interest rate is 15.96%, the total amount repaid is $3004, with $2400 being counted towards paying back the principal and $604 towards paying interest. The term of the loan is 36 months, and as I said it took the borrower exactly 3 years to pay. Inthis situation, I would think that the annual ROI would simply be the interest rate. However, when I calculate the ROI the best way I know how, I get

 ROI= (Total Payment Recieved/Total amount invested)^(1/3)-1= around 3.5%.

However, the interest rate is 15.96%. Why is there such a huge discrepancy?

The reaason I ask is that I am trying to go through this file and calculate the average anual return for all of the loans put together, but I am getting numbers that are significantly negative, whereas the figures given on the LendingClub website are in the 6%-15% range.

Could someone please help explain this disrepancy?

EDIT: I believe I understand why the NAR should be higher, because not all of that principal is actually being used the entire time to generate that payment, which implies that the NAR is actually higher. But how to do atually incorporate this amortization schedule into my calculation of NAR?

What I have to work with so far is the fact that the total payment should be given by the annuity formula

A = P*( r+ r/((r+1)^n-1)

where r is the interest rate. I would think that since we are given P (the principal) and A (the total payment/number of payments) we can solve for r, which I guess in this case would be the NAV. But when I do this using goalseek in excel I am not getting the correct answer. Does this seem like the right track to be on?



  • 1
    If the principal is $5000 how come only $4669 is being counted towards principal? also as the balance goes down each month the amount of money invested is also declining. – mhoran_psprep Dec 24 '14 at 13:02
  • @Mhoran_psprep you are right that I made a mistake in the problem description -- it appears that the loan is actually not completely fully paid yet. I have updated the example. And I understand conceptually what you are saying about how the principal gets paid back gradually... but how would I actually use this in calculating the NAR? – Paul Dec 24 '14 at 13:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.