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?