Most of us don't have the skills to know/track what is the average price of our total buys nor how much will we profit if we sell a small % of our bags.

This post is a tutorial on how to make an Excel/Calc table to help you with that.

I'm using LibreOffice Calc, but you can do the same with Excel. Let's go!

Setting up the basics

1. Open the software and on columns A, B, and C type in "Date", "Price" and "My buy", respectively. The first is the date of buy, the second is the price of the coin at the time of the buy and the third is how much you've put in.
2. On the "A" above the first column to select all the cells, and then Ctrl+Shift+3 to format as date (or click on the calendar symbol with a 7 in the tool bar):

Fig. 1 - Formatting as date

1. Do the same for the columns B and C, but select the Format as currency button (or Ctr+Shift+4). I've input some numbers to help us later on.

Fig. 2 - Formatting as currency

1. On cell D1, type "Coins". This will track how many fractions of the coin you've got, which is simply how much you've put in divided by the price of the coin, i.e. the values in the C column divided by the ones in B column. On cell D2, type = C2/B2. It should now look like that:

Fig. 3 - Computing how many fractions of the coin one got

1. Now see that small square on the bottom right of selected cell D2? When you put your mouse cursor there, it will change to a cross. You now click, hold and drag it down. The other cells will automatically shift to C3/B3, C4/B4 so forth and so on:

Fig. 4 - Changing many cells at once

It shows #DIV/0! Because the software is trying to divide the values on column C by the values of column B. Since there are none in the latter, it shows an error saying that you ask it for a division by zero. As soon as you input values, in B and C, the error vanishes:

Fig 5. - Tracking buys and dates

If you don't like how many numbers you have after the comma, click on the "0.0" button in the menu bar. It will round for two decimal or so (I prefer not to).

Now let's calculate the average price!

Calculating the average price

Your average is simply how much you've spent divided by how much of the coin you have. So if in total you've spent e.g. $5000 and now have 2.5 coins, your average is $5000/2.5 coins = $2000 per coin.

1. In cells E1, F1 and G1, type in "Total spent", "Total Coins" and "Average", respectively. The first is how much you've put in in total, the second how many coins or fractions of it you've got in total and the third is your average price.
2. In cell E2, type =SUM(C2:C6). What this does is that it will sum all the values in column C, from cells 2 to 6. Remember that we're calculating our total spent, and the money we spent is in column C:

Fig. 6 - Calculating the total spent

1. To calculate the total coins, simply sum the cells in column D by typing =SUM(D2:D6) in cell F2:

Fig. 7 - Calculating the total coins

1. Finally, the average is the value in E2 (how much you've spent) divided by F2 (total coins you have):

Fig. 8 - Average price calculated

Note: I've put the summations up to row 6 because I only have data until there. If you have more data, the sum must go until the last data point. E.g. if I have data until row 555, I'd have put =SUM(C2:C555) in my total spent math.

Calculating profits

For you to have a profit, you have to sell higher than your average buying price. Let's say you want to sell 0.1 of our coin. Your average price is $17.920,30, so you paid 0.1 x $17.920,30 = $1.792,03 for this 0.1 coin. If you sell it for $25.000,00, you'll get $2.500,00. So your profit is $2.500,00 - $1.792,03 = $707.97.

Let's implement this in our table!

1. In cells I1, J1 and K1, type "Selling price", "Coins" and "Profit". The first is the price when you sold, the second is how many coins or fraction of it you got rid off and the third is your profit.
2. In cells I2 and J2, put $25.000,00 and 0.1, respectively (as per our example above)
3. In cell K2, type =(I2-$G$2)*J2, i.e. (selling price - average) x coins sold. You should now get something like this:

Fig. 9 - Calculating profits

Observations

• In cell K2, we used $G$2 and not G2 because the average value will always be on cell G2. The $ symbols make sure that if we use the click, hold and drag as in Step 5 of Setting up the basics part of this tutorial, the cell won't shift. Also if you want to add more data on your profit columns, just type it in
• The profits column will also calculate losses if you sell for a price lower than your average. The result will be then shown with a minus sign
• There are other ways of calculating averages and such, but this is what I use on my day-to-day and find it to be simpler and more useful
• You can also play changing the colors of your table and personalizing it
• There are online tools for that, too. The advantages of doing it yourself are (1) you know exactly what and how it is being calculated and (2) you have privacy over your portfolio

I hope it helps you somehow.

Godspeed and good profits!