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%
- My Prosper Internal Rate of Return Update (End of September 07) = 0.61% This is a RateLadder only IRR update. (An IRR lender “game” update is in the works.) I was given a last second reprieve from a negative IRR this month by a collection payment!!! A post is coming about this. Here is my permanent tracking page: http://rateladder.com/my-prosper-irr/ My Prosper IRR is......
- My Prosper Internal Rate of Return Update (End of August 07) = 2.75% This is a RateLadder only IRR update. (An IRR lender “game” update is in the works.) I was involved with my first default debt sale. I sold 1 AA loan for 25% of prinicpal. Here is my permanent tracking page: http://rateladder.com/my-prosper-irr/ My Prosper IRR is defined as: Monthly cash flows......
- Default Rate: It's Not Just If a Loan Defaults, But When I had a very interesting email exchange with RGF. (I emailed him after using his forum posts in articles last week). Here is a portion of that email exchange on default rates over time and their affect on yield. RGF: Default rate: It's not just if a loan defaults, but......
- Zopa Guarantees Returns 5.1% But the yield is only 5.1%. Essentially you are buying a 1 year CD in the credit union of Zopa. Details can be found in the this Wall Street Journal article. FDIC insured guaranteed rate of return. Yield capped at 5.1% Seems just like a credit union. I will be glad to have......
- My Prosper Internal Rate of Return Update (End of Jly 07) = 0.57% This is a RateLadder only IRR update. (An IRR lender “game” update is in the works.) I saw a big spike in loan 1 month late or more. 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......
- Return of the King Lord of the Rings Sci-Fi, Fantasy Trading Cards Collectibles
- VI Return of the Jedi Star Wars Science Fiction Collectibles
Categories:
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