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 of the month and included in the ending account balance.
So with that in mind, the final entry for my Prosper IRR will be the current account value minus cumulative interest paid to date minus any late loan values.
So for the End of Jan:
Current Account Value = $3,614.65
Cumulative Interest Paid To Date= $46.03
Current Late Loan Value (used as monies added in last month) = $0
Giving me cash flows as follows:
| 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 | ($46.03) | $3,568.62 |
Which yields an IRR of 12.31%.
I like this number a lot better than 18.14%. Not because of the interest rate, but because I believe it. My average interest rate has been climbing since starting RateLadder.com, but it is still only 16.3%. Plus money leaving my bank account does not make its way into loans instantly.
Thanks for the help and if you think something else isn’t right please let me know.
If you liked this article, vote for it on del.icio.us and stumbleupon.Categories:
Prosper.com, Quicken, Statistics
Related Articles Related Stores
4 comments ↓
I got this from TB and thought it worth adding as a comment:
A good way to calculate the annualized IRR would be to do it as I showed in sheet “Alternative 1″ in the recent workbook I sent. I think this is also what you are suggesting below in your first comment. To make sure we both are thinking along the same lines, this is how I would capture the cash flows in an annualized IRR calculation for a Prosper account:
Count interest as cash inflow in the period it is received.
Count monies added to Prosper account as cash outflow when monies are added to the account
Count principle received i.e. loan paid back as cash inflow in the period it is paid back. This would apply if someone pre-paid their loan.
Count the remaining principle outstanding (i.e. the remaining principle on all loans outstanding including accrued interest) as cash inflow in the last period. This amount would be the cash you would receive if all borrowers that owe you money would pay back their loans plus accrued interest today.
(There is no need to break out the interest and principle portions from payments you receive, as long as the principle portion is reflected as a reduction of the remaining principle to be paid. For example, if the outstanding loan balance at the beginning of the period is $100 and you receive $10 as payment during the month, of which $6 is interest and $4 is payment of principle, your cash inflow for the month would be $10 and the cash flow you would assume at the end of the period would be the remaining principle i.e. ($100 - $4) = $96.)
This would be a reasonable calculation for the actual IRR.
It could be agrued to be optimistic since it would not assume any defaults on any loan principle outstanding and it would assume that all accrued interest would be paid. However, this calculation would allow you to assume some default rate to the final period amount easily since someone who would default would obviously default on not only the principle but the accrued interest as well.
Your second suggestion would work also, but it would understate the IRR since you would have to assume that interest is only accruing and not paid out until the last period. In this calculation you would count monies added as cash outflows in the period they occur but you would NOT count any interest payment received as cash inflows. Your final period cash flow would be the account balance. If you want to be even more conservative, you would as you suggest, exclude accrued interest from the account balance in the final period. I personally DO NOT like this second way of calculating the IRR since it would not capture the fact that you get interest payment prior the final period that are available for you to reinvest. However, depending on how much information you have available, it could be the easiest way to do it and would be reasonably accurate.
Any way you slice it, you always have to make some type of assumptions when you calculate your IRR.
I think the way it is now is right. If I included principle repayment in the month that is happened and then subtracted from the final month I would eventually end up with a negative final month even under optimal loan performance. The way it is now seems right.
Plus when I remove any loans value (principle plus unpaid interest) for being late I am adding in a conservative factor. I will have late loans, that is just a fact.
TB Sent another excellent email so I made it a new post: http://rateladder.com/2007/02/05/another-excellent-email-from-tb-on-irr/
Thanks for the praise but if it is “excellent” or not depends on whether I am helping to clarify the issue or not. Hopefully I am (to some extent). I want to clarify for anyone who might be looking at the Excel sheet, the XIRR for a loan should only match the APR in a situation with no funding delays, no late payments (or defaults) and assuming that the money is invested in the loan ASAP. Obviously the return on the Prosper account would most likely differ from the theoretical APR due to funding delays, time before money is invested, defaults, late payments, late payment fees etc.
Leave a Comment