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%
Related Posts -
New Agency Test Results Another interesting note from the official forum... New Agency Test Results Prosper is testing a collection law firm and is recording this on member accounts as "New Agency Test". This is a limited pilot to test the process of collecting more aggressively on Prosper Accounts. Thus far, in approximately one...... -
New Loan — debt consolodation [sic] — $25,000 at 15.00% — B Credit — DTI 15% A new loan funded this afternoon (debt consolodation [sic] (Loan #6434) â $25,000 at 15.00%). I participated via my standing order: Low DTI and Mid DTI â NAF. Which is this loan was funded as a low and medium debt to income ratio and was an non-auto-funding loan. The borrow had B...... - RateLadder Crosses 11K in Prosper Account Value After reaching the 10K milestone I stopped adding fund to my Prosper account unless the funds were generated in advertising/donations. Today, my account value peaked over the 11K threshold ($11,079.42). I am very happy with my progress to date. To be sure, I wish I had avoided certain extended credit that......
- Prosper com Loan Ordering Feature Request My account has 56 loans originating across 7 months. I regularly go and look at the entire list and attempt to gleam which loans need to make a payment. This task is an exercise in futility. The loans are in origination date order so at any given time there are 7......
-
Zopa Shuts Down US Branch Zopa, the UK based peer to peer lending company, is shutting down their US branch due as a result of toughening conditions that are currently being experienced by the US economy. Zopa provides loan services in the UK, as well as in Italy and Japan through a pure P2P style......
Related Websites - Quick Prosper Update I had been steadily feeding money into Prosper for a couple of weeks now. Though, I've been getting what I feel are going to be solid loans, it's been even longer than usual for Prosper to make the loans active. It had to have been more than week with 4......
- Reader Question: Which Loan Should I Pay First? I got a question from a reader recently and when I tried to e-mail her back the email bounced back to me. Seems pretty odd because it was persons full name at a very well-known company. It was a well thought-out question and I hate to leave it unanswered, so......
- Net Worth Update This month a lot of things went right and only one went wrong. I've increased my Prosper account while reducing my HELOC as I had hoped. My retirement accounts grew due to a strong stock market. Everything is rosey, right? Well there's the matter than Zillow values my investment property......
-
Understanding your FICO Score pt 1 FICO scores, maintained by the Fair Isaac Credit Organization, are calculated from a number of different pieces of credit data that is found within your three credit reports. There are five different categories of data that this information can be grouped into. There are specific percentages that reflect how important...... - Weakon 131: Mortgages Previously, I talked about how the mortgage industry works. Today I want to talk about how mortgages work. We must start by making a few distinctions. A mortgage is not a loan. A mortgage is a legal instrument for the transfer in ownership of a piece of land or......
Categories:
Prosper.com, Statistics
Tags:
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