My bookkeeping spreadsheets
for p2p platforms

Why do you need to monitor your investments on a spreadsheet?

One of the first things I asked myself when I started investing in this sector was: ok, but how much am I really earning? I lacked a general picture that would show together all datas from the various platforms. Furthermore, the calculation of the percentage interests varied slightly from platform to platform and therefore made them incomparable in practice. These are the reasons that led me to create the following spreadsheet.

Collecting and entering data in the spreadsheet

Opening the sheet from the link above or looking at the first image of the gallery, you will see that the spreadsheet is composed of a tab for each of the platform on which you invest, a summary tab and a projection tab that we will discuss later. For each platform you can:

• Add a movement in the space on the left, inserting a new line on top of the existing movements. You will have to enter the date on which you made the movement, the amount and eventual notes (respectively A4, B4 and F4), the rest of the columns can be copied by dragging up the lower line. At the end of every investment year, you will have to add a new movement representing the total interests occurred during the year.
• Update the current value by manually taking it from the platform (M2)

Graphs, results and projections

The results are visible on the summary tab (“Riassunto”). In detail:

Summary

On this tab you will see a summary of the current situation with every column representing a lender and the last one on the right that represents the total or average between all platforms. The rows represents:

• The current value of every single platform and the total sum (row 2)
• The interests occurred during the current year on each platforn and the total sum (row 3)
• The interests percentage  in the currenti year for each platform and a weighted average (weighted on the platform invested capital) (row 4)
• The duration of the investment on each platform in number of days in the current year and the average duration (row 5)
• The annual interests projection for each platform and their sum. This value answers to the questions “Hom much will I earn this year?” (row 6)
• The annual interests projection in percentage for each platform and the weighted average (weighted on the platform invested capital) (row 7)
• The daily average earning for each platform in the current year and the total sum (row 8)
• The monthly average for each platform in the current year and the total sum (row 9)

In the lower data block you can insert (by hand) the yearly earnings in euro ed and in percentage to keep historical data. You will also find some automatic fields that will show durations, interests, daily average earnings, and monthly average of the investment.

The tab also contains a pie graph with your money distribution across the platforms; it will help you decide if you are differentationg enough or not.

Projection

In the projection tab you will see how your investments wil levolve in the coming months/years in terms of interests and total invested amount. Each row represents a month of investment in the current conditions, and will help you figuring out how long  will it take to get your target monthly income. Obviously it assumes that you reinvest all the earned interests and that you add a monthly sum of money stated in the cell D2.

Share is care

If you have any idea on how to improve this spreadsheet model or you want to share your own spreadsheets please leave me a comment; I’m very curious to know how you deal with bookkepping cause I have no experince in this field.