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 -
Adam Nash Merged with RateLadder to form the Ideal Quicken Solution (without Prosper integration)  I have found what I think is the most complete solution to the Quicken and Prosper dilemma, but that doesn't mean it is the best solution... Let me start with the negative and work to the positive... The problem with his solution is that it is too much work... I...... - Prosper Discontinues Group Payment Rewards Hallelujah! Hallelujah! Hallelujah! I never placed much stock in Groups. Decoupling Groups from money turns them into what they should be... social networking. Here is the announcement email which I received because I am the group leader of Group RateLadder... Dear Kevin, At Prosper, we have been listening to your......
- My Prosper Internal Rate of Return Update (End of Jan 07) — 12.31% It seems that after some thought EnoughWealth and TB and hit upon the thing that wasn't quite right with my XIRR based IRR My Prosper Internal Rate of Return Update (End of Jan 07) â 18.14%. It was double counting the interest since it was both being paid at the end......
-
New Loan Funded — cash for small business — $25,000 at 14.5% — AA Credit — DTI 1422125% A new loan funded (cash for small business â $25,000 at 14.5%). I participated via a manual bid the loan was Autofunding. The borrower had AA credit and 1422125% DTI (undocumentable income). As a reminder my standing orders (and manual bids) only find loans with 0 current delinquencies, 10 or less delinquencies...... - Major Prosper Update -- Just in Time for Prosper Days  Prosper's First Anniversary First of all, let's take a moment to celebrate. On Feb 5, Prosper turned one year old. In our first year, exactly 7048 loans with a value of $35,166,711 were made on the Prosper marketplace. If you're coming from the perspective of a massive bank, $35M might......
Related Websites - 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......
-
Easy Ways To Reduce Your Credit Account Fees Many people across the country have a credit account and use the credit account to charge purchases on a regular basis. Businesses that deal in credit accounts love when individuals are using their credit accounts more often because it gives the business more chances to obtain more funds from these...... -
Personal Finance Equations You Should Know: the Amortization Equation Amortization is probably one of the most ubiquitous financial concepts that encompasses our daily lives, but yet, few of us know what it means, how it works, much less, how the monthly payments on all our stuff is calculated. By definition, amortization is: The reduction of a debt over time...... - In Defense of Prosper.com - Other Common Complaints Rebutted (Jonathan at My Money Blog has a very in-depth two part review of Prosper.com. My Money Blog is one of my favorite sites, which is why itâs in my blog roll and feed reader. Itâs worth familiarizing yourself with that article before continuing here with my rebuttals.) Jonathan notes that......
-
Buying a New Car to Save on Gas Mileage The other day I was working up an article at a local cafe when I overheard a couple discussing whether or not they should buy a new car to "save on gas." The wife made the comment, "I know we spend a lot on gas, but we don't have a......
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