Calculating Log Returns in Excel
What is a log return?
It’s simply a change in price expressed as percentage. You often hear S&P 500 is up today by 0.5%. A log return of S&P 500 for today would be 0.5%.
How to calculate log returns in Excel?
First you’ll need some data, you can get it for free on Yahoo! Finance website. Here in our example we’ll use BTC/USD, you can download it here.
When you download the file, it will be a CSV file which you can open with Excel like any other CSV file. It will look like this:
Now, that you have the data ready, we start calculating log returns. In calculating returns we always skip the first data point, because we need to calculate the difference between current day and previous day.
The formula in Excel for calculating our fist log return in our example is:
=ln(f3/f2)
Pasting that formula will give you similar result like the this screenshot:
Now, we just apply this formula for all the following cells in the H column, and we apply some formatting to get it to look like this:
We now have our log returns ready, but what can we do with them?
Check these tutorials to continue: