My Prosper Internal Rate of Return Update (End of Jan 07) — 18.14%

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%

February 1, 2007 | Filed Under Prosper.com, Statistics

Useful Info:

comments

5 Responses to “My Prosper Internal Rate of Return Update (End of Jan 07) — 18.14%”

Enough Wealth on February 1st, 2007

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.
Kevin on February 1st, 2007

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.
TB on February 2nd, 2007

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.
TB on February 2nd, 2007

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.
Kevin on February 2nd, 2007

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.