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 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...... -
Another Default Debt Sale Prosper completed another debt sale... The details are not yet on the Default Sale History page; however, Prosper Shira did make an announcement on the forums... We completed a debt sale this week which will be evident in participating lender accounts over the next few business days. When a loan...... - In Defense of Living Off Dividends Living Off Dividends (aka WealthBuildingLessons (WAB) on Prosper) wrote a guest post on the Prosper Blog: Why I Love Prosper.com. The blog post has generated some heated comments due to one aspect of the post. WAB claimed "over 13% annualized returns". When questioned what his calculation actually was he said.........
- 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......
-
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 - 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......
- Having Debt Does Not Make You A Bad Person. Two days ago, I received an email from a reader complaining about being in debt, feeling trapped by it, and feeling like a failure because his (or her?) friends are buying houses and are able to afford new cars. They felt really bad that they could not give their family......
- Why I Started Lending Money With Prosper And Lending Club. I am a savings-account kind of guy. I like knowing how much interest my own money is earning, and how much I can depend on each and every month from my accounts. ING has been very good to me over the last couple of years, but lately their interest rates......
-
Education Loan Interest is Not Always Tax Deductible It seems to be a common statement among parents, planners, financial aid people, some bloggers, etc. that Don't worry about education loans, they are tax deductible... Like most tax advice given...it is a half truth. Student Loans Are not Always Tax Deductibe Yes, your interest is tax deductible if you...... -
The Dangers of Long Term and Interest Only Loans There are two new trends in the banking world that may actually be very dangerous for consumers. Long term personal loans and interest only loans are gaining in popularity, especially in the wake of the housing crisis. While these may seem to be a great option at the time, there......
- Cash Registers Banks, Registers & Vending Collectibles
- Rock Sheet Music Sheet Music & Song Books
- Vocal Classical Sheet Music Sheet Music & Song Books
- Sheet Music Sheet Music & Song Books
- Excel Wetsuits Surf Store
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