Building the LenderPayment Table
The first step in building my ROI calculation involves reconstructing the payments for a lender. The principal and interest payment calculations have to be made using the information available in the LoanPerformance table from the private data extract. The biggest challenge in doing this is that the LoanPerformance table essentially only provides the principal balance on a given ObservationDate. In order to construct a LenderPayment table, I have to calculate the amount of time that has elapsed between observation dates in order to determine how long interest has accrued. Although Prosper accrues interest daily, some odd things tend to happen when regular payments come early or slightly late.

It would seem that even if a payment for a given payment cycle comes early, Prosper charges the borrower the full interest for the payment cycle. It really isn’t possible for me to determine when this happens, so I just always accrue and charge interest daily based on the prior balance. However, if a payment comes early in one cycle cycle, the payment in the next cycle should accrue more days, so the interest I calculate on the next payment will be higher. The sum of the interest paid on the two payments should be close to what the borrower actually paid in interest for the two cycles. My interest calculated will be slightly lower since I will have accrued more interest with the lower balance of the two periods. This will result in a more conservative ROI calculation.

When loans are late and payments are made, accrued interest is paid first. That means that if a payment is made while a loan is late and the total payment doesn’t cover all of the accrued interest, the principal balance will not change. Therefore, by looking at the LoanPerformance table it isn’t possible to determine when such (interest-only) payments have been made. Once again, the error in my calculation will result in a more conservative ROI calculation, as I will be unable to calculate any such payments.

If additional principal payments are made, Prosper calculates the accrued interest for the next payment based on the average loan balance. As I stated earlier, I simply calculate the days between payments and track the prior balance. If additional principal payments are made, my interest calculation will be based on the prior loan balance and not the average. My interest calculation will be slightly lower than the actual, which will also result in a more conservative ROI calculation.

I do include some servicing fee calculations in my LenderPayment table. However, the fee calculations are based on the current servicing fee structure. The fees I calculate for older loans may not be accurate, as the loan servicing fees have changed over time. I was a bit lazy with my fee calculations and didn’t research the changes that have taken place with servicing. If and when I eventually look up these changes, I should be able to make improvements with the fee calculations. Unfortunately, my inaccuracies with servicing fees will result in a slightly higher ROI in cases where the older fees are higher than the current servicing fee structure. My lower servicing fee calculation will show that the lender made more profit on old loans than they actually did. I don’t include NSF or late fees either, so that is an additional improvement that can be made in the future as well.

Within the LenderPayment table, I calculate principal, interest, fees, lender income, and lender profit. LenderIncome is the principal and interest less servicing fees that lenders receive. LenderProfit is the interest received less fees. LenderProfit also includes net losses that occur as a result of defaults. The net loss calculation includes any proceeds that result from the defaulted loan being sold to a debt buyer. If a loan is repurchased by Prosper as a result of identity theft, it is treated the same as a paid loan, as these loans do not result in any losses for lenders.

Building the LenderBid Table
The second major step after building the LenderPayment table is building the LenderBid table. LenderBid calculates the accumulated income (P&I less fees) for each lender. By maintaining a running total of how much cash flow a lender has received, I am able to determine when the lender has accumulated enough income to fund a particular bid. When enough income has been accumulated to fund a bid, I set a flag on the reinvested bid. The amount that was reinvested is deducted from the accumulated income. I think this is where the real magic is with the ROI calculation. By determining which bids were funded by reinvested funds, I can exclude those funds from the cost basis for the ROI calculation. This will provide a more accurate picture on how much cash went into the lender’s Prosper account and how much profit was made as a result of the investment of those funds

SQL Source
Being a big proponent of open source software, I have made my SQL code for the WealthBoy Strict ROI available for all to see. It is written for Microsoft SQL Server, so I apologize to those of you that use that use other relational database management systems for manipulating and analyzing Prosper data. I’m not a database programmer, so my SQL code is far from perfect and can certainly be improved. That is one of the many reasons I feel obliged to share my code, so that enhancements can be made and shared with the rest of the Prosper community.

If you do use any of my SQL code, I ask that you please give credit where credit is due. If you have any solutions for the issues I’ve mentioned (or issues I haven’t mentioned), please do share them. I will certainly publish improvements that others share with me. Right now, I think the biggest problem is the performance of the cursor I use to calculate the reinvestments. If someone else could figure out how to do it with just one or two update statements, that would be fabulous. I fooled around with recursive common table expressions some, but didn’t have any luck. Unfortunately I couldn’t figure out a way to do it without performing row-by-row operations.