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 -
My Prosper IRR Update Normally, I expect to update My Prosper Internal Rate or Return twice a month: last day of the month for account value and the day I get the monthly statement. Since posting My Prosper IRR yesterday, I have gotten my monthly statement; hence, there will be 3 updates this month (yesterday, today,...... - My Prosper Internal Rate of Return Update (End of Mar 07) = 3.33% for an Decrease of 9.53% 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 Apr 07) = 3.57% for an Increase of 0.24% 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......
- Prosper in May Set a New Monthly Loan Origination Volume High Water Mark (sql to produce statistics at the end of post) In May, Prosper originated more loan volume than in any previous month roughly 9.6M loans the vast majority were credit grades AA-C. This is great news for Prosper on 2 fronts: total volume and loan quality. Here is the breakdown... May......
- What is my Internal Rate Of Return on Prosper? Why do I want to calculate my Internal Rate of Return (IRR)? To make sure it is higher than my cost of money - ergo a good investment. I claim that my cost of money is 5% which I can earn in an Internet savings account. So my IRR needs......
Related Websites - do I have a tennis elbow please help? I play tennis every day and whenever I serve my best my elbow starts hurting its been going on for 3 weeks. is it tennis elbow but when I put ice on it its fine and I can play tennis again without a problem until I serve. Is it tennis......
- Saturday Lotto AU 4million Tracking through the NSW Lotto recent results, some quite extreme situations have occured in the patterns analysed. The sum totals have been difficult to predict, as there were 3 games in a row where the smallest number was 19, 20, 21 consecutively, this had not occured for 135 draws. The......
- 2010 Federal Income Tax Brackets & IRS Tax Rates Since we are half way through 2010, I figured now was a good time to check out what the 2010 Federal tax rates were so you still had 6 months to make any changes to your withholdings if necessary. Every year there are always small changes to the absurdly wordy......
- Athlete Profiles: Pete Sampras Pete Sampras was born in 1971 and grew up in the Washington DC area of the United States. He is considered to be one of the most gifted male players in the sport and has one numerous titles, medals and cups throughout his long and illustrious career. Although he is......
-
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......
- National Guard Militaria (Date Unknown) Militaria Collectibles
- Coast Guard Militaria (Date Unknown) Militaria Collectibles
- Nelson Rain Date Timers & Controllers Watering Equipment Gardening & Plants Yard, Garden & Outdoor Living
- Nelson Rain Date Timers & Controllers Watering Equipment Gardening & Plants Yard, Garden & Outdoor Living Nursery
- Unknown Date Ornaments: Miniature Hallmark Decorative Collectible Brands Decorative Collectibles Collectibles
Categories:
Prosper.com, Quicken, Statistics
Tags:
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