How to create a Cost Averaging spreadsheet for crypto

What is Cost Averaging?
Setting up your Cost Averaging spreadsheet
Appendix - Get clever with Googlesheets

If you want to invest on crypto but don’t have a lump sum to invest or feel confident timing the market, one of the most sensible ways for newcomers to gradually build a portfolio is through Cost Averaging. Buying at regular weekly or monthly intervals. If you’re ready to start cost averaging ,then it's good practice to record everything. This article will show you how to create a Cost Averaging Spreadsheet

What is Cost Averaging?

Cost Averaging, often called Dollar Cost Averaging or DCA for short, is an investment strategy where you buy a fixed amount of a cryptocurrency at regular intervals - weekly or monthly - in order to attain an average entry price over the long run.

Cost Averaging is especially useful for volatile assets where trying to time the market with a single lump sum investment is difficult, which is why it is so popular with crypto investors.

Though the entire crypto market is valued at over $2trillion we are still in the early stages of adoption. Anyone who has held Bitcoin, for example, for over a year, will be in profit, but day-by-day it fluctuates considerably based on changing narratives - regulation, environmental concerns, China bans, Elon Musk.

So if you’ve done your research, and believe in Bitcoin’s fundamentals, but find trying to second-guess daily price gyrations each day a mentally exhausting exercise, Cost Averaging provides a simple structured plan which can save you a lot of sleepless nights.

Most crypto exchanges offer recurring purchases as standard so you can set up a weekly/monthly schedule - ideally just after pay day - connected to a debit card or bank account.

Setting up your recurring purchase is, however, just half of the challenge. You need a way to track the performance of your Cost Averaging purchases. One of the best ways to do that is recording everything in a spreadsheet.

Not only will this give you an honest and accurate measure of how your investment is going, for many it can be the first step towards crypto trading, where recording and planning your activity is crucial.

Don’t worry if excel isn’t your bag, as we’ll walk through the set-up and even provide a template for you to download.

For balance, it is important to understand that Cost Averaging doesn't guarantee a profit, and actually works best in a Bear Market, So requires commitment, and given it is a record of your personal portfolio, should be information you protect behind a suitable layer of security.

Setting Up Your Cost Averaging Spreadsheet

Ideally, your spreadsheet should record each individual transaction, then aggregate that data into a helpful Summary. If you are making recurring purchases of more than one cryptocurrency, they should have a separate tab, which you can then pull into a separate Summary tab.

We recommend recording the following data columns for individual transactions. This detail will be available as part of purchase confirmation provided by your exchange/app. You just have to get into the habit of inputting that data once the transaction happens. The longer you leave it, the more work is involved and the less likely you are to follow through.

For an example Costa Averaging tracker, let’s assume you are buying €20 of bitcoin every Thursday at 10am. The following are the data column headings and what they represent. The formulas are built into the downloadable file:

Date of Purchase - The date the purchase was executed. You can include time if you want to get really granular.
Value of Purchase - This is the amount in your local currency that you spent on the purchase. Note, this isn’t the overall cost of purchase, as you want to separate out the fee.
Fee - The fee charged for processing the recurring purchase
Cumulative Value of Purchases - This gives you a running total of how much you have actually invested in bitcoin.
Cumulative Fees - This gives you a running total of how much it’s cost you to execute your recurring trades which you’ll need to adjust your Profit/Loss figure to get a Net Profit/Loss value.
Purchase Price of Coin - The price at which you purchased bitcoin in your local currency.
Number of Units Purchased - The amount of bitcoin that your €20 bought you at the time of execution i.e 20/price
Cumulative Units Purchased - This gives you a running total of how much bitcoin you now hold in total.
Current Value of Transaction - the value of each transaction based on the current price. You might be tempted to input the price each time you refresh the sheet, but there are some tricks that allow you to automate this. We’ll cover these at the end of the article so Excel-phobes aren’t frightened off.

Your transaction data will give you a line-by-line record of your recurring purchases, but it is handy to have a Summary using simple formulas to tell you the following:

Total Invested - How much in total have your invested in your local currency
Total bitcoin - How much bitcoin you now own
Average Price - The average purchase price across all transactions
Current Price - Using the GoogleFinance formula (see below)
Total Value of bitcoin - How much your bitcoin is worth in your local currency at the current price
Total Paid in Fees - The total cost of the fees for all recurring purchases
Investment Profit & Loss - Subtract the Total Value of Your Coin from the Total Invested minus Fees
Investment Profit & Loss % - Measuring your P&L as a % value.

If you have earned bonuses or referrals from the recurring purchases - which has been the case at Coinbase with $10 offered after week four and eight - then you can add these as a separate cell.

Total bonuses - How much bitcoin from your total is from referrals or promos.

You are now in a position to record all the details of your Cost Averaging activity and provide a clear summary of its performance. Cost Averaging doesn’t have to be just about accumulation. If you want to take some profit out, you can follow the same logic to sell fixed amounts at regular intervals.

Our Knowledge Base explains how Cost Averaging can be a gateway to trading as you can use what are known as Technical Indicators or external models to adjust your purchase amounts up or down based on whether you think the market is over/under valued.

Once you get to that stage you should add a column with the logic of that decision, which turns you Googlesheet from just recording Cost Averaging to the beginnings of a Trading Journal.

Appendix - How to pull in live crypto price data into googlesheets

If you are using Googlesheets and want the price for Bitcoin use the following formula: =GOOGLEFINANCE("CURRENCY:BTCEUR")you can pull in different fiat combinations with Ethereum (ETH), Litecoin (LTC) or Bitcoin Cash (BCH) just replace BTCEUR as applicable.

If you want to pull in data for a coin that isn’t supported by Googe Finance there is another trick using a Googlesheet function called IMPORTXML. It might sound scary but it is really simple but powerful as it can pull in live data from any website. You just need the website URL, then to isolate the source code that is generating the price on the page.

To do this use the ‘Inspect’ function when browsing a page, hover over the price and copy the class, tag or div that is next to the price. Here’s an example for Polkadot price from Coinmarketcap:

=IMPORTXML("https://coinmarketcap.com/currencies/polkadot-new/","//div[@class='priceValue ']")

Adding that formula to a cell will give you the current dollar price, but the sheet thinks it is text not a number so you can use a Substitute formula to remove the $. Where L8 is the cell with the IMPORTXML function.

=SUBSTITUTE(L8,"$","")

The output is in dollars by default, so if you want to convert you use a Google Finance function in another cell for current dollar rate against your chosen currency, which for Euro looks like:

=GOOGLEFINANCE("CURRENCY:USDGBP")

You can then update the price cell formula, assuming your GBP rate is in cell L9

=SUBSTITUTE(L8,"$","")*L9