For anyone with a new year's resolution to track their p2p portfolio more closely I've shared the spreadsheet I use to track mine. Hope you find it useful!
The main goals I have for portfolio tracking are to see at a glance with minimal input the: 1. Balance on each platform for my own records in case of platform failure 2. Weighting of each platform balance as a percentage of total investment to keep an eye on platform exposure 3. Balances after expected bad debts (since a lot of platforms don't estimate these and just hold a bad loan at full value on the books for an indeterminate length of time) 4. Total earnings (including interest, capital gains, cashback, losses, etc) 5. Internal rates of return before and after expected bad debt to compare platform performance 6. Expected p2p income for the next 12 months based on current portfolio
The actual manual input required for this information is the date, amount transfered and the platform balance every time you do a bank transfer to or from a platform plus an estimated capital loss if a loan defaults. I've included some fictional transactions as an example.
I used OpenOffice Calc 4.1.1 (free to download, .ods file extension) to make this. A copy is provided in Excel 97/2000/XP (.xls) format but I haven't opened it in Excel. It should work but the formating might be a bit off.
Nice one it seems to work ok in excel 2007, sadly its a bit over kill for me though as I only have one platform (Zopa) i'm on. Admittedly I have 6 different products overall and manage mine and Mrs Aju with heer having 4 products across both invest and ISA.
I too am a spreadsheet Junkie - Sadly one needs to be on Zopa these days since they removed all the best tools for tracking defaults that is.
Mine are a bit huge as well having nearly 10 years of statements data. In fact my statements csv sheets alone is some 30MB and runs to nearly 200,000 records. Unfortunately its the only way to track defaults since they removed the excellent loanbook tools to the current rubbish that's not worth the page it takes up IMHO.
I have some simple tracking at a higher level for Loanbooks (Both current and alltime) similar again to the old high level info on the original Zopa reports. Occasionally I join the tables together to get a tool similar to the old reporting tools allowing me to track both high level on a loan and its low level payments data. Generally i just populate them monthly when all the latest data is available.