With the lender statments taht were released this weekend it is possible to update my Quicken ROI…

Last month being a quiet “normal” month one would expect an increase in the quicken value…  My Quicken ROI increased from 8.11% to 8.99%.

• Quicken ROI Update = 11.22%

• RateLadder Quicken ROI Update November 1, 2007

• Quicken Updated To Reflect Borrower Loans In Lawsuit

• Quicken ROI Update = 8.11%

• A Look At the Graphs and Charts from Quicken in My Prosper.com Account

Recently it was revealed that Prosper is Going To Sue Deadbeat Borrowers and that lenders on the loans would be given a choice between taking a guaranteed upfront buyout at the price of the last junk debt sale or participating in the loan pool suit and reaping the consequences or rewards depending on the outcome.

I have no idea what the outcome will be, but either way I would need to account for the real world situation in Quicken as accurately as possible.  If you take the buyout your accounting is fairly straight forward: Debt Sale Accounting via Quicken.

However, for reasons that are more informational than financial I am choosing to participate in the loan pool and therefore need to account for the process.

(This is not clean accounting, but at the end of the process it will accurately reflect what happened.)

Step 1: Write off all principal in the pool as worthless.  For me, this amounts to a sale transaction of 325.26 shares at a price of $0 per share dated on the day Prosper marks the loans defaulted.

Assuming the loan pool produces net recoveries for each recovery amount:

Step 2: Adjust the Step 1 transaction reducing the number of shares in the sale transaction by the amount of the recovery at a 1:1 ratio of shares to dollars. If the net amount recovered  equals or exceeds the original principal amount simple delete the Step 1 transaction.

Step 3: Enter a new sale transaction dated for the recovery payment date with the number of shares equal to the number of dollars recovered and a sale price of $1 per share.

This will result in no assumptions about recovery in Quicken.  On Day 1 my quicken account will look like it lost $325.26 of assets and value.  My quicken ROI will take an immediate hit, but potentially this loss will be removed over time.

For me, the $33.11 I have to “pay” to be part of the pool is worth it for the information.  I am glad it is not a larger amount as I do not know what the eventual outcome will be.

• Quicken Updated To Reflect Borrower Loans In Lawsuit

• Quicken ROI Update = 8.11%

• Quicken ROI Update = 11.22%

• Prosper Collections Test In Progress

• New Agency Test Results

• Price Guides Supplies Comics Collectibles

• Byers Choice Decorative Collectible Brands Decorative Collectibles Collectibles

• Swimming Pool Pool Spa Covers Pools Spas Yard, Garden Outdoor Living

• Pool Cleaners Pool Parts Maintenance Pools Spas Yard, Garden Outdoor Living

• Pool Filters Pool Parts Maintenance Pools Spas Yard, Garden Outdoor Living

It took a little tinkering, but I finally hit upon the perfect way to integrate Prosper (or LendingClub or …) with Quicken (or Money or …) given the current lack of integration between the services (see here if interested.)

Since implementing that solution I have had defaulted debt sold.

With enough experience behind me I thought that a comparison of the Quicken and Prosper accounting differences and the implication on ROI.

Quicken enters the money into the account the moment the money leaves my bank account and it only acknowledges interest after it has been paid.  Prosper only counts money in loans and acknowledges interest the moment it is accrued.  Both acknowledge default sale amounts the moment they happen.  Neither approach attempt to project a future loan’s value.

What does this mean?  Well it means that with Quicken you can get the ROI for the moment the money enters the account with interest only for actual payments received.  With Prosper you get the ROI for the loans with all accrued interest.  Since you can only deduct defaulted principal (cash basis) I feel that Quicken’s approach is correct.

That being said let’s compare ROI values for my account… Other than the gap the curves are remarkably similar. I am interested to see what the rumored December debt sale looks like.

• Quicken Updated To Reflect Borrower Loans In Lawsuit

• Accounting for the New Agency Test in Quicken

• RateLadder Quicken ROI Update November 1, 2007

• Quicken ROI Update = 11.22%

• Debt Sale Accounting via Quicken

• A Perfect Circle A Rock, Pop Music Memorabilia Entertainment Memorabilia

• Current Militaria 2001-Now Militaria Collectibles

• Current Pez Pez, Keychains, Promo Glasses Collectibles

• Reproductions Current Militaria 2001-Now Militaria Collectibles

• Current Year, Next Year Calendars Paper Collectibles

In the recent default debt sale I had a loan sold.  This is my first opportunity to account for such an event.  I use Quicken for my personal finances including my Prosper account (read about it here).

Here are the details of my defaulted loan:

CreditGrade: AA
Homeowner: Yes
Verified Account: Yes
Debt to Income Ratio: 25%
Very Clean Extended Credit
Sale date: Aug-24-2007
Sale reason: Delinquency
Principal balance before sale: $143.87
Loan value before sale: $152.68
Proceeds from sale and forfeited group rewards: $36.63

$36.63 / $143.87 = .2546

So what did I enter into quicken?

I entered a Sale Transaction for 143.87 shares at a per share price of 0.2546.  This generated proceeds of $36.63.

I had to immediately set the share price of the Prosper Loans security back to $1.

Works great.  Now Quicken thinks my investment has an average annual return of 8.41%

• Accounting for the New Agency Test in Quicken

• Quicken Updated To Reflect Borrower Loans In Lawsuit

• RateLadder Quicken ROI Update November 1, 2007

• Quicken vs Prosper — ROI Accounting

• Another Default Debt Sale

• Credit, Charge Cards Trading Cards Collectibles

• Event Covers Covers United States Stamps

• First Flight Event Covers Covers United States Stamps

• Naval Event Covers Covers United States Stamps

• Space Event Covers Covers United States Stamps

I updated my Quicken Prosper.com brokerage account per yesterday’s post: http://www.rateladder.com/2007/04/10/adam-nash-merged-with-rateladder-to-form-the-ideal-quicken-solution-without-prosper-integration/

The one things that is even easier is that if you use your monthly statement you don’t need to enter a fee transaction…  However, I also used my account history to update my account for March…  I plan on using my statement to reconcile these numbers.  The March estimate also needed a fee transaction since the prosper history includes the fees.  Or I could have just waited another day or two to get my March statement.

The bottom line is that for each month there are 3 transactions…  A purchase for new loans, a sale of principal portions of payments, and in interest distribution for interest portion of payments.  If you have a principal reduction (I haven’t had one yet, but I think they come from debt sales or bankruptcy) then you would also need a transaction for this…  I haven’t decided how to enter this, but I think it is a sold transaction for a $1 with a commission for the principal reduction.  Thoughts?

The benefits

• Low Number of Transactions to Enter
• Cash vs Loans Account Balance Tracked
• ROI for Account and Prosper Loans Tracked
• Prosper Loans Called Out as Different Asset Class

The downside

• All loans Rolled into a Single Security (Individuality is Lost.)  (You will be Assimilated.)

My YTD results are 12.78% for account, 14.72% for loans.

My all time results are 7.33% for account, 9.55% for loans.

I would like to thanks Adam Nash of Psychohistory for his post regarding his Prosper and Quicken Solution which I combined with my original approach to come up with the current approach.

• A Look At the Graphs and Charts from Quicken in My Prosper.com Account

• Debt Sale Accounting via Quicken

• Quicken Updated To Reflect Borrower Loans In Lawsuit

• Quicken vs Prosper — ROI Accounting

• Prosper.com and Quicken — The workable solution for playmates that don’t get along.

• Postal History Worldwide Stamps

• A Simple Plan A Rock, Pop Music Memorabilia Entertainment Memorabilia

• Postal History Covers United States Stamps

• Post-1950 Beds Furniture Antiques

• Post-1950 Bookcases Furniture Antiques

 I have found what I think is the most complete solution to the Quicken and Prosper dilemma, but that doesn’t mean it is the best solution…  Let me start with the negative and work to the positive…

The problem with his solution is that it is too much work…  I have 70 loans and if I had to update each one it would require 210 transactions a month by hand.  I communicate regularly with people with 100-1000 loans.  Suscintly, the problem with the solution is accurate accounting requires 3 transactions per month per loan by hand.  He even admits as much…

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.

I have called for Prosper integration in the past.  With such integration Adam’s solution would be the best solution for the integration.

Here is his solution: 

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 works accurately on a number of different fronts, but most appropriately by following the Quicken model enough so that Quicken can calculate your IRR, and you are tracking principal, interest, and fees on a per loan basis.

I think a hybrid of his approach and mine (simplified cashflows only: http://www.rateladder.com/2006/12/31/prospercom-and-quicken-the-workable-solution-for-playmates-that-dont-get-along/) would work great and be manageable…  Here is my proposed merge my existing approach with his…  (I haven’t tried this yet, but I think it will work great.)

Step 1: Create a brokerage account called Prosper.com.  For each transfer from your bank transfer money into the cash balance of this account.  This is easy as your bank likely integrates with Quicken.

Step 2: Create a Single security called “Prosper Loans”.  Set the asset class to “Unclassified” to separate this security from more traditional in your portfolio.

Step 3: For each month, using your lender statement… Buy shares for for the entire principal loaned for the month at $1 per share.

Step 4: For each month, using your lender statement… “Sell Shares” to represent principal repaid during the month; use an “Interest Income” transaction to represent the receipt of the interest payment; and Lastly, use a “Miscellaneous” transaction to record the Prosper fees charged

The hybrid approach retains all of Adam’s original accuracy expect it rolls it up into a single security instead of a security for each loan.  It also, maintains Rateladder’s goal of not spending my entire life tracking Prosper in Quicken.  Most importantly, as you portfolio grows the time required for tracking does not increase rapidly linearly; let alone exponentially.  Anybody care to guess at BigO.  Constant?  LogN? or NLogN?  I think constant, but I will let you know after I update Quicken.

Update 8/28/2007: Default Debt Sale Accounting with Quicken

• Quicken ROI Update = 8.99%

• A Look At the Graphs and Charts from Quicken in My Prosper.com Account

• Quicken Updated My Prosper Account YTD ROI — 12.78% for account, 14.72% for Loans

• Prosper.com and Quicken — The workable solution for playmates that don’t get along.

• RateLadder Quicken ROI Update November 1, 2007

• Work Womens Shoes

• Complete Bicycles Bicycles Transportation Collectibles

• Nash American Automobiles Advertising Collectibles

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:

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.

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

• Another Excellent Email from TB on IRR

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

• My Prosper IRR Update

• What is my Internal Rate Of Return on Prosper?

• Internal Frame Packs Backpacks Camping Hiking

• VI Return of the Jedi Star Wars Science Fiction Collectibles

• Heart Rate Monitors Monitors Pedometers Gym, Workout Yoga Exercise Fitness

• Return of the King Lord of the Rings Sci-Fi, Fantasy Trading Cards Collectibles

• Home-Based Businesses Businesses for Sale Opportunities

Prosper.com and Quicken (or Money) do not play nicely.  In fact, they can be downright burdensome to make them bridge the gap.  Here I present my workable solution, but this should be addressed by Quicken, Money, and Prosper.com  

The heart of the problem is that Quicken doesn’t understand the Prosper.com model…  Sure you can declare a single loan  at a time but try that with 10, 20, 46, or more loans.  Keeping them all up to date quickly becomes an all day affair.  Believe me I tried this approach for 2 months.  If the accounts were linked via some automatic download like credit cards, bank, or investment accounts this is the correct way to account for the information today.  Yet handling defaults and net gain or loss is much simpler with an umbrella account. Ergo, Quicken should create a new type of account and Prosper.com should implement automatic Quicken download.

I love both Quicken Deluxe 2007 (even require) and Prosper. So what was I to do?

I created a new investment account that I called Prosper.com. This makes it easy to track the money into Prosper the moment it leaves the bank account by setting the category of the bank transaction to [Prosper.com]. Now that the money is in the Prosper.com investment account (as cash), how do you track the individual loans and their performance? The short answer is you don’t.

The long answer is that leaving it in cash is the easiest thing to do. Then when you get your monthly statement from Prosper.com you enter the realized interest earned for the month as a single interest payment.  This allows you to track your Prosper.com account globally.

Prosper.com or Quicken if you are listening, please address this issue. Either via a case study or white paper addressing the proper way to account for this information or via a tight integration as a new type of account. Personally I vote for an integration.

As I am sure all Prosper.com lenders track their finances through Quicken (or Money)… I would be very interested to hear if anyone has a better solution than mine.

I found Quicken 2007: The Official Guide to be an excellent guide to Quicken; not only the basics, but also the advanced features.

Update 4/10/07: This is my current approach: Adam Nash Merged with RateLadder to form the Ideal Quicken Solution (without Prosper integration)

• Adam Nash Merged with RateLadder to form the Ideal Quicken Solution (without Prosper integration)

• Best of RateLadder My 1st 3 Months

• A Look At the Graphs and Charts from Quicken in My Prosper.com Account

• Quicken ROI Update = 8.99%

• RateLadder Quicken ROI Update November 1, 2007

• Investments Opportunities to Make Money

• Heart Rate Monitors Monitors Pedometers Gym, Workout Yoga Exercise Fitness

• Model Ships Maritime Antiques

• Model Horses Horse Animals Collectibles

• Breyer Model Horses Horse Animals Collectibles