So, a few confessions to start this off.
First, I am still a Quicken addict. It has been thirteen years, I think, since I started using Quicken in earnest to track my finances, and I’m still at it. Despite absolutely terrible releases of the software, and lackluster Mac support, it’s still one of my must-have applications.
Second, I am a big fan of Prosper.com. I found Prosper when it was CircleOne, through some friends from eBay who left and joined the company. As a result, I’m a founding group leader (though not a very successful one), and a shareholder.
So, with those confessions out of the way, on to the good stuff.
If you don’t know what Prosper is, it’s basically a marketplace where you can easily borrow money or lend money to other people. Consumer debt is very expensive, so it’s potentially a way for individuals to get cheaper rates borrowing, and for lenders to make higher rates than normal saving options. Here is a Q&A on Prosper from Money Magazine. Here is a write-up in Forbes of some strategy when dealing with Prosper.
If this sounds crazy to you, here are some of the rates you can earn on Prosper. Note that even for the highest risk borrowers, right now the default rate is around 3%. 24% – 3% is a very good return, but only if you spread your money around with a lot of very small loans.
For the past year, I’ve been struggling with an appropriate strategy to track Prosper Loans in Quicken. I found some information through web searches that seemed appropriate for the Windows version of Quicken, but didn’t work for me on the Mac. The idea was to create an Asset account, which is the loan, and then to set up a loan paid from the asset back to the Prosper account. I couldn’t figure out how to do it.
Since I had trouble finding a solution for this online, I thought I’d post my solution here. Feel free to comment if you’ve found a better way to track Prosper loans in Quicken.
Step 1: Create a Security for each Prosper Loan. I name them after the unique Prosper Loan number, like “Prosper Loan 335”
Step 2: Create a Brokerage account for your Prosper account. Transfer the money from your checking account to this account when you move money to Prosper.
Step 3: When you make a loan for a certain amount, let’s say $100, then purchase the shares of the Prosper Loan security, at $1 per share. So, in this example, you would purchase 100 shares of “Prosper Loan 335”
Step 4: Whenever you want to update the account, use the following 3 transactions. Use a “Sell Shares” transaction to represent the principal re-payment. Use a “Interest Income” transaction to represent the receipt of the interest payment. Lastly, use a “Miscellaneous” transaction to record the Prosper fees charged.
This is likely too much work to do monthly, although you need to if you want Quicken’s IRR calculations to be accurate. Personally, I’ve decided just to update the account once every 3-6 months, which is sufficient for my needs.
Let me know what you think… if this helps even one Quicken addict out there, it will have been worth it. 🙂
Update (4/9/2007): This is why I love blogging. AMF posted my blog comment on a Prosper Board, and now there are good comments there too. Check it out!
Update (4/10/2007): RateLadder.com has their own solution… not as accurate as the one above, but worth linking to. I agree with them that it would be better for Prosper to offer a Quicken-compatible download format.
Update (4/10/2007): Are you interested in joining Prosper.com? If so, please join my group. I originally started it for my investment club, but I’m changing it to be an open group for friends & family. I feel a little lame right now because I only have 3 members in my group, and I am a founding group leader.
Update (4/10/2007): Rateladder.com has merged their approach with mine in a hybrid approach that tracks you entire Prosper portfolio as a single security. Only 3 entries per month! The only downside is you can’t track the performance of each loan this way. Check it out here.
Update (4/11/2007): OK, last update. But Rateladder.com has followed up with a finally post on the topic. Between the two of us, I think we’ve provided the best way to handle this until we convince Prosper to provide downloadable transactions.
15 thoughts on “How to Track Prosper Loans in Quicken 2007 (Mac OS X)”
In step 4, instead of doing 3 transactions manually each time, why not do a single transaction split 3 ways. Even better, set up a scheduled transaction with the split, set for once a month for 3 years?
I’m curious, how do you set up this type of payment when the amount of interest and principal will be different every month? I can’t figure out how to do this on the Mac OS version of Quicken.
If this could be automated into a single transaction, that would be super helpful. Unfortunately, the brokerage account type doesn’t seem to support split transactions.
I’m using Quicken 2007 for Mac OS X.
(Note: I’ve cross posted on rateladder)
Question: would the principal repayment be considered a “return of capital?” as opposed to a sale? What are the implications of using one category over the other? I’ve taken a look at the irs (a search on the phrase returned numerous results) and wikipedia (http://en.wikipedia.org/wiki/Return_of_capital)websites on the topic, but I’m still a little hazy.
I could only find the IRS applying the phrase to stocks and mutual funds.
I’ve tried both in Quicken. Recording principal payments as sales resulted in my cost basis increasing when that payment was reinvested, but with return of capital used the cost basis did not increase. (Well, technically it did with the purchase, but it declined by an equal amount when the payment was recorded.)
Return of capital doesn’t seem correct to me if it reduces your cost basis.
As a sale, your cost basis doesn’t change, and your roi is correct, because you get the same price for the sale as your purchase, leaving the interest income – misc expense as the return.
The way I’m thinking of it is:
cost basis for $1000 loan is $1000
total cash out should be $1000 + interest – expenses
The cost basis should increase when you reinvest, because you are making a new loan, with its own cost basis.
Why are you not using the Quicken Loan feature in Quicken 2007( OS X)? The first question it asks is are you the borrower or the lender. Put lender and go from there.
I just joined Prosper and I’m using the described hybrid method, but I’m using $10 rather than $1.00 as the price for the security. (and reducing number of shares by a factor of 10)
The advantage to me is that you can use the ‘estimated income’ column in Quicken’s Investing Center so you can see how much interest you’re generating per year for your total portfolio. (loans, stocks, bonds, cd, etc.)
For the individual loan, when you’re using $1.00 per share, Quicken will round the amount of estimated income per security, so that distorts the amount if a loan generates 19.5% interest. (You can’t enter 1.95 cents per dollar per year on a $1.00 unit [Quicken rounds it to 2 cents], but you can enter $1.95 for a $10 unit)
Hope this helps and makes sense.
I meant to say 19.5 cents per $1.00 unit on a 19.5% loan, but I hope you get the rounding idea.
I’m not sure I understand the logic of using $10.00 as a share price. If you are having a problem with rounding, then maybe you should use $100? Cents require 2 digits to prevent rounding, and $10 only provides you with one.
I am a Quicken addict back to the Dos days! – and a Propser Lender. I update a spreadsheet on Prosper loans weekly. At the end of the month the interest goes into Quicken. I would love to use your idea, however Propser has changed the screen so you can’t view the interest anymore. Only way to do it now is from their statement.
I’m actually scared of quicken now, and I’ve switched to KMyMoney. It’s a lot simpler, and it doesn’t force me to upgrade when I don’t want to. I don’t think that business users will find it very useful, but for my home use it’s great. It does require KDE (I run Ubuntu Linux), though. KDE4 should soon be available for the mac.
Do you know what percentage of applicants are able to get loans at Prosper? Would like to include the information at http://www.acreditlibrary.com/prosper.html
Thanks for this post. I’m an M$ Money guy, and I’ve been trying to figure out how to track ROI with Prosper semi-automagically. I had been entering the loans as loans, which doesn’t track ROI because M$ treats money that you’ve loaned out as a negative amount of money that you’ve borrowed.
The basic differences in your procedure for Microsoft Money is that one must create the brokerage account in the Portfolio Manager first, then add loans in much the same way (using investment type of “Other”, one can track shares and price per share as you do in Quicken). Reconciling each borrower payment is much the same as you described.
I finally decided to give you a little feedback ! well you got it! i love your site !!! no , really, its good…
So the goal you and Eric from RateLadder have is to calculate IRR only for the dollars actually loaned out at any given moment in time, rather than in your entire Prosper account? That seems to be the reason for accounting for interest payments and prosper fees and so forth.
On the other hand, I’m attempting to calculate IRR for my entire Prosper account, meaning dollars sitting idle negatively contribute to the IRR. This seems like a better calculation from a personal finance perspective because it fairly compares the return to the 24/7/365 return of CDs, stocks, etc. I believe this is also easier to calculate, although I would really appreciate some guidance if I’m wrong in my calculations:
I am counting only actual transfers into my Prosper account and transfers out of my Prosper account as cashflow. Then when the IRR is calculated, I compare the inflows&outflows and their dates with the current balance (using Excel’s XIRR). I belive this achieves what I’m looking for but if you guys disagree, I’d love to hear about it.
So that’s 2 questions I guess: are you attempting to calculate IRR only for dollars actually loaned out, and do you see any problems with my calculation?
Thanks a lot. By the way, I just recently found your blog and have been enjoying it.
Like anything in personal finance, you can measure things in a number of different ways, depending what you look for.
When I put money in a brokerage account to invest in stocks, I don’t include the returns of the money market fund when I ask the question of how my stock investments are doing.
It’s not wrong to include the idle cash – in fact, as you say, it may me a more accurate reading of “how much am I making from being involved with Prosper”. Carrying costs count.
However, if you want to compare Prosper investments to Bonds or other types, it might be misleading to include your cash in this analysis, but not your analysis of the other investments.
Hope this helps. Thanks for the kind words.
Comments are closed.