Keeping track of your P2P profits (and everything else) in Google Sheets

Over the years, I've got a lot of questions about how I track my P2P portfolio. I've set my tracking up with tools that are difficult to share, but I wanted to finally help all of you do it more easily. Here it is, the one-size-fits-all approach to all investments that require the absolute minimum amount of tracking and you can do it just as often or rarely as you want. It won't break if you skip tracking for a month.

Want a shortcut? Go directly to the Free investment tracking Google Sheet.

The challenge in tracking P2P investment performance

The challenge in tracking investment performance is that all platforms have different, colorful ways of reporting performance. Some platforms have multiple different CSV exports. E.g. Bondora has a monthly cashflow report as well as an account statement. The first one is a 'wide' format with multiple columns that you can customize yourself. The second one is a 'tall' format that is extremely useful for a data scientist such as me, but has so much detail that analyzing it by most is tedious.

So which do you choose even?

Then, when you look at another platform, e.g. Crowdestor and Mintos, they both have the detailed 'tall format' account statement report. They contain essentially the same information, but they are a little different formats. My approach has been to download these account statement reports and with tools I manipulate the reports to the same 'tall' format and apply charting and functions on that dataset. If all platforms had a similar-enough data export, then recommending people to do it this way might be a fine approach but there are two issues:

1. Firstly, not all platforms have this. Creating that dataset manually is very tedious.
2. You need the full dataset, which gets extremely long especially on peer lending platforms such as Mintos. The dataset grows big and platforms sometimes limit the exports to e.g. last 3 months (this happened to me with Lainaaja, a Finnish P2P platform). If you miss the export time window, it gets complicated to fill the gap.

Therefore, this approach doesn't work very well over time. There has to be a better way.

The data points you really need

You really need only two to three data points to track investment performance:

1. Deposits and withdrawals
2. Changes in value
3. Payouts / costs (which is effectively a change in value coupled with a deposit/withdrawal)

Here are some examples.

Deposits and withdrawals

These actions transfer money from you to the asset or back, resulting in a change in the value of the asset. This is typically the first action you make with an asset. For example, when you transfer money to your Estateguru account, or make a withdrawal from your Lynx broker brokerage account.

If you track your stock brokerage account as a whole, you can indeed consider deposits to and withdrawals from the account as a whole and be fine. However, if you choose to track individual stocks or ETFs instead, you can consider a deposit as buying the ticker and withdrawal as selling the ticker.

With regards to rental properties, the price you pay for them is the deposit. If you have a mortgage, then this category will hold the down payment as well as future mortgage payments. If you include the mortgage interest part in the deposit transaction type, that part will inflate the book value of your rental property. This is fine, for as long as you periodically re-evaluate the property value (see next step).

Changes in value

When tracking any p2p platform, it's always easy to check what the total account value is at any point. When you know the previous point and current one, the delta is the change in value. Typically this is interest that happens inside the account, or defaults.

If you are tracking the total brokerage account as a whole, then dividends paid by individual stocks stay inside the brokerage account and therefore add to the value of it. In this case, they should be marked as changes in value (my recommendation).

If you own rental properties, you can periodically check what the value of your property is, but if it has been financed with a mortgage, then consider only the net value.

Payouts / costs

Lastly, this is like a deposit or withdrawal but doesn't change the value of the asset. When you are tracking a peer lending platform, then interest paid inside the account is not this. Interest paid inside the account increases the total account value and is therefore a Change in value. However, if you've set up e.g. RoboCash to pay out interest automatically, then you'd use this. In that case, the interest is not increasing the account value anymore, since it is paid out to your account.

If you are tracking individual stocks, then dividends create a payout from the asset and this is the right transaction type.

In the case of rental properties, you likely get rent every month and you likely pay a housing maintenance fee (at least in Finland). These don't affect the value of the property so they are not deposits and this category is therefore the right one.

Recommended setup

In my Google Sheet, I have a two-stage categorization: a top-level category and a sub-category. As the top-level category, I have chosen asset types: Stocks, P2P and Real Estate. The more items you have on the second level, the more tracking you will need. The simplest way to set up the second level is:

• Have each real estate property as their own. This is a no-brainer.
• Have each P2P platform as their own asset.
• For stocks, if you have multiple brokerage accounts, you can have each of them as the 'asset' - this is most simple and granular enough and also the way I'm tracking things. Or, if you have multiple portfolios (e.g. value, growth and dividend stocks), you may bundle them - for as long as it's easy for you to track them. Or, if you only own a handful of tickers, e.g. ETFs (check my Gone Fishin' list from Should you hire a robo advisor), they can certainly be tracked separately.

So here's what my structure will look like roughly:

• P2P
• Mintos
• Bondora
• Crowdestor
• Estateguru
• SunExchange
• ...
• Stocks
• Interactive Brokers
• Nornet
• Real Estate
• Property 1
• Property 2

How to track

Regardless of the structure, you can track your investments at any point. Once a month is totally fine, but even if you skip a month, track weekly of every 7 weeks, doesn't matter.

The only things you need to keep track of are:

• Deposits or withdrawals to and from the asset: Easiest is to do the update to the Google Sheet when you do this as this is always a self-initiated action. Or you can check your bank statements for this traffic
• Current value: when you log into your peer lending platform, simply register the account value in the sheet. The sheet will automatically create a value change record.
• Payout/cost: In my recommended setup, only required for rental properties, when the tenant pays rent or when you pay housing maintenance fees. These are very predictable and can easily be verified from your bank statements.

It's that simple.

Sure, if you update the account values infrequently, the IRR calculation will be a little choppy and slightly off, but you don't need to worry about it too much. The impact is small.

The free investment tracker Google Sheet for peer lending and everything else

The Free Investment Tracking Google Sheet page contains essentially the same instructions as this page, with a little more focus on instructions. Head over there to get it and start using it.

The first view in the investment tracker Google Sheet is the full historical dashboard. Underneath the historical Internal Rate of Return chart you can see each investment category and their respective stats including Internal Return Rate or CAGR.

The reason for the choppy line is because I've been lazy with my dummy data and only reported stock value changes yearly. With real data tracked more regularly you'll see a smoother curve.

On the second sheet you can have a look at individual investments, their transaction history as well as do the tracking. Simply insert a value to one of the three fields and hit enter. The scripts I've written will do everything for you.

The sheet contains mostly dummy data, except for Bondora and Crowdestor.