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
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.
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:
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:
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.
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.
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