Post by copacetic on Jan 4, 2018 0:07:50 GMT
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.
P2P Investments - sample copy.zip (362.6 KB)
I'm a bit of a spreadsheet junkie so if anyone else wants to post theirs I'd love to see it for ideas!
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.
P2P Investments - sample copy.zip (362.6 KB)
I'm a bit of a spreadsheet junkie so if anyone else wants to post theirs I'd love to see it for ideas!