Clearly I am not an expert, but I like to play one on TV.
In my previous post: My Prosper Internal Rate of Return Update (End of Jan 07) — 1.67% I was tracking my IRR using the Excel Function IRR and making a monthly cash flow simplification to have regular intervals. This is not invalid, but I was misinterpreting the results. TB pointed out in a comment that the 1.67% would be for the regular interval period (in my case a month), so if I wanted to have the yearly annualized return I should multiply by 12 (1.67%*12=20.04%). However, he suggested that I should really use the excel function XIRR and use the correct transfer dates and still using the monthly interest simplification.
So here are the cash flows with transfer dates…
| Date | Interest Paid | Monies Added | Cash flow |
|---|---|---|---|
| 7/7/06 | $0.00 | ($200.00) | ($200.00) |
| 8/31/06 | $1.92 | $0.00 | $1.92 |
| 9/19/06 | $0.00 | ($200.00) | ($200.00) |
| 9/30/06 | $2.33 | $0.00 | $2.33 |
| 10/10/06 | $0.00 | ($200.00) | ($200.00) |
| 10/12/06 | $0.00 | ($400.00) | ($400.00) |
| 10/13/06 | $0.00 | ($36.31) | ($36.31) |
| 10/17/06 | $0.00 | ($200.00) | ($200.00) |
| 10/23/06 | $0.00 | ($246.58) | ($246.58) |
| 10/30/06 | $0.00 | ($148.28) | ($148.28) |
| 10/31/06 | $2.73 | $0.00 | $2.73 |
| 11/6/06 | $0.00 | ($45.00) | ($45.00) |
| 11/7/06 | $0.00 | ($600.00) | ($600.00) |
| 11/8/06 | $0.00 | ($49.94) | ($49.94) |
| 11/17/06 | $0.00 | ($435.49) | ($435.49) |
| 11/30/06 | $13.65 | ($300.00) | ($286.35) |
| 12/27/06 | $0.00 | ($300.00) | ($300.00) |
| 12/31/06 | $25.40 | $0.00 | $25.40 |
| 1/25/06 | $0.00 | ($150.00) | ($150.00) |
| 1/31/07 | $3,614.65 | $0.00 | $3,614.65 |
Using Excel and XIRR I determine that my Prosper IRR is currently 18.14%.
So what does this tell me? That I have been doing extremely well in Prosper. Impressive! However, I think the number in inflated because I don’t have any defaults, but after I have defaults I will probably think the number is deflated 
I know quite a few people that can’t admit a mistake. Personally I like mistakes…. To clarify I like fixing mistakes. It means I have learned something. So please, if you see any more mistakes let me know…
Thanks TB!!!
To avoid the double count of interest I have adjusted to removed the cumulative interest earned to date and present the results here: My Prosper Internal Rate of Return Update (End of Jan 07) — 12.31%
Related Posts -
Lending Club Loan Volume Misquoted in National Newspaper I encourage all reporters to check facts. In the p2p lending space, both major US p2p lending companies provide data and this data can usually corroborate or debunk basic marketplace claims. I was reading a recent Pittsburgh Post-Gazette article on p2p lending: The credit crunch: How did it happen and where...... - My Prosper Internal Rate of Return Update (End of October 07) = (0.57)% This is a RateLadder only IRR update. (An IRR lender âgameâ update is in the works.) Here is my permanent tracking page: http://rateladder.com/my-prosper-irr/ My Prosper IRR is defined as: Monthly cash flows with one extra month using the current account value minus any loans 1 month or more late. Current......
- My Prosper Internal Rate of Return Update (End of Jun 07) = 5.75% This is a RateLadder only IRR update. (An IRR lender âgameâ update is in the works.) Here is my permanent tracking page: http://rateladder.com/my-prosper-irr/ My Prosper IRR is defined as: Monthly cash flows with one extra month using the current account value minus any loans 1 month or more late. Current......
- State Default Rates -- A Publishable Application of the RateLadder ListingKey In the past I have talked about my technique for adding a ListingKey to the Loan table of the Prosper.com data export. While I had successfully added the key several weeks ago I hadn't found an application of the key that I could publish until recently.  A fellow Princetonian and prosper lender......
- My Prosper Internal Rate of Return Update (End of Jan 07) -- 1.67% I am tracking my Prosper internal rate of return (IRR). As a reminder my prosper IRR is defined as actual cash flows up to the current month. The current month is positive account balance minus monies added minus loan values in default. I currently have 0 loans in default and......
Related Websites - 100 Oldest Dot-Com Domains Hi Guys/Gals, Here's a list of Internet's Top 100 Oldest Dot-Com Domains. 1. symbolics.com: March 15, 1985 2. bbn.com: April 24, 1985 3. think.com: May 24, 1985 4. mcc.com: July 11, 1985 5. dec.com: September 30, 1985 6. northrop.com: November 7, 1985 7. xerox.com: January 9, 1986 8. sri.com: January......
-
Vancouver 2010 Olympic Schedule and Results [/caption] Olympic Schedule and Results Dates * February 12 * February 13 * February 14 * February 15 * February 16 * February 17 * February 18 * February 19 * February 20 * February 21 * February 22 * February 23 * February 24 * February 25 * February...... -
Callippe Preserve Golf Course Callippe Preserve Golf Course is located in: Pleasanton, CA Phone: 925-426-6666 Website: http://www.playcallippe.com/ Course History: This is a relatively new course that premiered in 2005. It was highly anticipated and proved to be well worth the wait. It has been ranked in the top 10 of all California golf...... -
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...... - Wednesday Lotto AU 750K Today's draw has some potential, as mentioned in the Monday draw comments: Soon to come: A draw where ALL numbers will come from only the last 4-7 draws!! Also look for all small numbers (under 33) to come in one of the next few draws. There is a good possibility......
Categories:
Prosper.com, Statistics
Tags:
5 comments ↓
Still doesn’t look quite right to me (but I’m no expert at using the XIRR function!). If your cash additions (deposits) are showing as -ve amounts, and interest received appears as positive amounts, where is it tracking the principal repayments (I believe that with prosper you get back some of the principal each month during the term of the loan, not just interest payments)? I would have thought it should be appearing as positive amounts in the “monies added” column, as your loans are appearing as negative amounts in this column??
ps. Are you including all the monies deposited with prosper.com, or just the amounts that get lent out? Hopefully you are tracking cash amounts added (deposits) and ignoring when loan amounts are lent out, as this is irrelevant to XIRR calculation.
The principle repaid is in the account balance at the end as an implied assumption and in the offsetting amount is in the monies added.
The monies added date is the day my bank shows the transfer. The day monies goes into loan is irrelevant.
I don’t know if the assumptions are valid and if you have suggestions for better ones I am all ears. I was trying to avoid guessing at default rates with I think are unknown at this time.
When a loan is repaid (partially or in full), the proceeds you get should be included as a cash inflow for the period when it occurs (the cash flow column would be the net of interest received, monies added and loans repaid). As Kevin points out, the amount repaid in the final period would be reflected in the account balance (which is the assumed cash inflow at the end of the period for the calculation). The reason the annualized IRR is so high is because, from what I can tell, there haven’t been any defaults yet and so the annualized return reflects an optimal outcome to date. One could assume that a % of the existing loans would default and adjust the ending balance by that %. That would obviously just be an assumption.
Enough Wealth, your comment that it didn’t quite seem right got me thinking a little further (which I should have done from the beginning). Sorry Kevin but I did find an error in my initial suggestion. The interest received is being double counted since it is counted as a cash inflow when it is received and included in the account balance at the end. The correct way to do it would be to reduce the account balance at the end with the interest already received or if you want to be more conservative, don’t count the interest when it is received and only include it as part of the account balance at the end. The annualized IRR using these cash flows would be 12.2 – 12.3% depending on if you assume it is paid out during each period or accrued and paid out at the end.
BTW, I don’t exactly agree that taking the IRR (as originally calculated) x 12 would get you the annualized IRR but it would in most cases serve as a very rough estimation. However, if XIRR is used going forward, it does not warrant further discussion.
It seems like I should count the interest when received and then subtract from account balance. That way when I through out a loan value because of late payment it will do the right thing.
I don’t think this is quite right, but what about just using the principle plus cash (so account balance minus unrealized interest minus principle in default) in the final month?
I don’t want to assume a default rate and build Markov model (I will be doing that in a future post). The purpose of this was to track actual IRR.
Leave a Comment