I saw your last comment and wanted to provide you with an Excel sheet showing the implications of not counting the principle payments as cash flows when they are received.
In this sheet I have included a situation where there is a hypothetical Prosper account that is made up by one loan that will be paid in full and on time. The calculations would work the same with a portfolio of many loans. The assumptions that can be changed (loan amount and interest rate) is in blue font.
The sheet shows the hypothetical loan and how the amount outstanding declines over time as the principle is paid off. It also shows how the Prosper account balance for the lender would change over time as interest and principle get paid. In addition, it shows what the correct XIRR would be for the full term (it would be the same for each and every period for the durating of the loan as well) and what the XIRR calculation would show if it was calculated without counting the principle payments as the are received, but rather include them as part of the account balance (which I think is what you are suggesting in you last comment on your site).
The conclusion from the examples I have included in the Excel workbook is that if the principle payments are not counted when they are received, the XIRR will look like it is declining over time. This is incorrect, since it does not reflect that the principle payments are available to reinvest. If the principle payments are not counted as cash flow when they are received, the XIRR will implicitly assume that the paid principle just sits in the account balance earning zero return until the loan is fully paid off. This is not reasonable to me. I don’t understand how the account balance at the end (for XIRR purposes) could ever go negative by deducting the principle payments received, if you don’t assume defaults, since the amount you deduct would be exactly equal to the principle implicitly included in the ending account balance (either as cash or as loans outstanding).
Btw, I am not bothering you with this to prove a point. I am potentially looking to become a lender at Prosper (whenever they actually get around to approving me. Their customer service is severely lacking) and going through these return calculations is a very good exercise for me. I always compare all my investment alternatives on a risk-adjusted XIRR basis, since it is (IMO) the best way to do it, assuming that there are no capital constraints involved, but that is a different topic.
As always, I welcome comments.
Google Spreadsheet Link to TB IRR Analysis
Google Spreadsheet Link to MY Prosper IRR
My comments so far are:
- I reinvest all monies. Using his suggestion the principle would be repaid multiple times. Eventually leading to a negative final month. The Interest can only be repaid once (then it becomes principle.)
- I think another suggestion he is making is that my IRR would be my APR under ideal conditions. I don’t think this is true. Since it takes from several days to several weeks for money to make its way into loans.
- Not bothering me in the slightest. I am enjoying the discussion.
- What is the best way to get an excel spreadsheet into a wordpress blog? hand coding the table would be too much work for this spreadsheet.
Another Comment:
- Maybe I should be claiming the principle. But since I reinvest it goes on both sides of the ledger creating a wash. If I were to remove the money from prosper then it would only be on one side of the ledger.
Here is the previous post: My Prosper Internal Rate of Return Update (End of Jan 07) — 12.31%
If you liked this article, vote for it on del.icio.us and stumbleupon.Categories:
Prosper.com, Statistics
Related Articles Related Stores
5 comments ↓
Comment From TB:
The XIRR on the loan, not the Prosper account, should match the APR. This was just to show that on a theoretical example with no transaction time (i.e. no funding delays), no defaults, no late payments or late payment fees, the APR and XIRR should match. The XIRR on your Prosper account will differ depending on how long it takes to fund the account, to find loans to invest in, how timely payments are made etc. I just wanted to set the theoretical base before making the adjustments to calculate your actual annualized return.
Comment from TB:
have added a sheet to the one I sent before. This new sheet is called Multiple Loans and is trying to capture what happens when principle and interest received is reinvested into a new loan.
I had to keep the term of the loan short (3 months) to keep the calculation manageble but it still shows how reinvesting principle and interest would occur and how to adjust cash flows to get a correct annualized return calculation. If you adjust the assumption in cell J13 you can see the impact on the XIRR if principle received is not considered in the period it it received.
In short, the final period cash flow in your XIRR calculation should be the account balance minus cumulative interest payments minus principle received plus capital (principle and interest) reinvested.
However, if you always immediately reinvest all principle and interest received, the XIRR using your method would be close to the correct one.
But If I don’t reinvest the money, then I must have taken the money out of Prosper. This action would require a positive cash flow transfer.
The thing to keep in mind is that tracking all loans down to the minute detail is a full time job. So my approach is an attempt to calculate a reasonable answer simply and quickly.
I treat Prosper as Fund. I put money in, it churns our dividends (interest which I reinvest), eventually I might take money out.
While Prosper.com caters mainly to the US market, there’s another site called GlobeFunder.com which caters to the global crowd, especially those living in the third world.
While Prosper.com caters mainly to the US market, there’s another site called GlobeFunder.com which caters to the global crowd, especially those living in the third world.
Leave a Comment