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.
Related Posts- New Loan — debt consolodation [sic] — $25,000 at 15.00% — B Credit — DTI 15% A new loan funded this afternoon (debt consolodation [sic] (Loan #6434) — $25,000 at 15.00%). I participated via my standing order: Low DTI and Mid DTI — NAF. Which is this loan was funded as a low and medium debt to income ratio and was an non-auto-funding loan. The borrow had B......
- My First Kiva Payments Are Made On Time About a month ago I made my 4 loans on Kiva to women in Somoa. I was both testing Kiva and hopefully making the world a better place. Kiva allows you to make individual loans to people in need through NGOs around the world. I went with 4 women from......
- Inquiries Are As Important as Credit Grade This is a guest post from a regular reader pjz... The following table shows zero DQ loans by inquiries. 0 DQ inquiries 0 1 2 3-4 5-7 8-999 1+Â month late & default 48 56 59 95 123 134 loans active, billed 1565 1364 929 1157 886 684 1+ month......
- Prosper's People-to-People Lending Market Survey SAN FRANCISCO--(BUSINESS WIRE)--Prosper (www.prosper.com), America's largest people-to-people lending marketplace, today introduced its first monthly People-to-People Lending Market Survey. Prosper's People-to-People Lending Market Survey will survey the marketplace each month to provide key statistics including: membership and loan volume statistics; marketplace returns; borrower rates; mix of prime, near prime and sub-prime......
- Loan Rate Analyzer Here is the actual Prosper Loan Analyzer that I use when evaluating a single loan. For input it takes the credit grade, debt to income ratio, and loan amount. It looks at all loans in the last 100 days with the same credit grade, DTI 0% to DTI+10%, and loan......
Categories:
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