Base Of Natural Log In Excel
Finding Natural Log In Excel

Understanding Log Return and Continuous Compounding

The natural logarithm is the logarithm with base e (Euler number is approximately 2.718). LOG function in Excel is used to calculate the logarithm of a number, and the base of the logarithm can be specified explicitly as the second argument to this function. The Excel LOG function returns the logarithm of a given number, using a supplied base. The base argument defaults to 10 if not supplied. LN Function Overview The LN Function Calculates the natural logarithm of a number. To use the LN Excel Worksheet Function, select a cell and type: (Notice how the formula inputs appear).

Intermediate

Log Return is one of three methods for calculating return and it assumes returns are compounded continuously rather than across sub-periods. It is calculated by taking the natural log of the ending value divided by the beginning value. (Using the LN on most calculators, or the =LN() function in Excel)

Synonyms: logarithmic return, continuously compounded return

For context, it's important to recall from high school math that the letter e is a constant value rounded to 2.71828. The unrounded number e can be found in Excel using =EXP(1).

Much like pi which describes the ratio of circumference to the diameter of all circles, e can be used to quantify all continuously compounding processes like inflation, interest rates, population growth and even radioactive decay.

For practicality in Finance however, banks don't pay interest on a continuously compounding basis, so keep that in mind.

How to Calculate Log Returns

As an example to find a continuously compounding rate of return, let's say the ending value of an investment was $11 and the beginning value was $10. The Excel function would read =LN(11/10) for a result of 9.53%.

To calculate a continuously compounded ending value on a $100 investment at 3% per year for 5 years would result in an ending value of $116.18, using the formula:

Ending Value = Beginning Value * (e)^(rate * time)
$116.18 = $100 * 2.71828^(0.03 * 5)

In math and statistics, a distinction is often made between discrete and continuous data. Log return is the more theoretical continuous version. In the practical world, however, most people think of returns broken into discrete periods instead.

Arithmetic return - One period, not-compounded, discrete.
Geometric return - Multi-period, compounded, discrete.
Logarithmic return - Infinite-periods, compounded, continuous.

The corresponding ending value using non-compounded returns (arithmetic) would yield $115.00. If compounded five times (geometric) the ending value would be $115.93.

So log return is the non-discrete version, so continuous, meaning it answers what the ending value would be if the whole period was broken into an infinite number of sub-periods. See, it's theoretical.

Other than for very short periods of time (typically less than 1 day), and for theoretical applications involving calculus and precisely measuring curves and tests for the normality of investment return distributions, log returns are not as common as arithmetic and geometric returns.

In a Sentence

Tom: If log return compounds most frequently, why is the rate the lowest?
Doc: Chew on this. Let's say $1 grew to $1.10 in a year; the non-compounding return is 10%.

Video

Many terms have 4-5 minute videos showing a derivation and explanation. If this term had one, it would appear here.

Videos can also be accessed from our YouTube Channel.

Video Script

If this term had a video, the script would be here.

Quiz

Click box for answer.

The log return is best suited for long periods of time. | True or False

False. It is best suited for daily or hourly periods.

What is the ending value of an investment of $1,000 continuously compounded for 10 years at 7% annually? | $1,700.00 or $1,967.15 or $2,013.75

$2,013.75. The others represent arithmetic and geometric total returns, respectively. To confirm type =LN(2013.75/1000) in Excel to get 7%.

Questions or Comments?

Base Of Natural Log In Excel

Still unclear on log returns? Check out the tutorial page and video on Stock return calculation methods from the Quant 101 Series on YouTube. There we go over when to use arithmetic, geometric and log returns.

Related Terms

Our trained humans found other terms in the category return math you may find helpful.

In Custom Axis, Y = 1, 2, 4, 8, 16 I showed axes with base 2 logarithmic scales in both Excel 2003 and 2007. In Excel 2003 it is necessary to transform the data to get the intended result. In Excel 2007, the axis can be achieved with the untransformed data.

In the previous post, the data was widely spaced, so it filled a base 10 log scale with two decades with only a reasonable amount of space above and below the data.

If the data isn’t spread out so nicely, you lose the opportunity to use a regular logarithmic axis. We’ll look at this for Excel 2003 and for Excel 2007, and then we’ll look at native logarithmic axis labeling.

Excel 2003

The data in the table below has a narrow range, from 8 to 12, and the range spans a power of ten.

Here is the data charted using a linear axis.

When we apply a logarithmic scale axis, the data spans across 10, so by default the axis ranges from 1 to 100. The data is squeezed into the middle of the chart.

Since Excel 2003 only permits the axis to begin and end at powers of ten, we’re stuck with this, and the fanciest labeling doesn’t make the data easier to read.

Following the steps in Custom Axis, Y = 1, 2, 4, 8, 16 we can plot the logs of the data on a linear scale, from log(8) = 0.903 to log(12) = 1.079.

We can hide the default labels, add a series with points where we want our custom labels using log(Y) data, and use the Y values as data labels.

Here’s the chart. With such a narrow spread in the data, it’s not immediately apparent that the Y scale isn’t linear, but if you took out your ruler (you still have one of those, right? you’re not completely digital?) you can tell that the span between 8 and 9 is larger than that between 11 and 12.

Excel 2007

As described in Custom Axis, Y = 1, 2, 4, 8, 16, Excel 2007 makes working with log scales a bit easier.

Gearhead web camera f%2f%232.0 driver. Here is the data plotted on a linear scale.

Here is the same chart, with the scale transformed to a logarithmic scale, using the default base 10. Same as Excel 2003.

Here the log scale has been changed to base 2.

In 2007 we can change the min and max of the log axis to values other than 10 (i.e., other than the base). Here the axis ranges from 8 to 80, still a decade on the base 10 log scale. Instead of the minor ticks being located at multiples of ten (20, 30, 40, 50,…), they are located at multiples of 8 (16, 24, 32, 40,…)

Finding Natural Log In Excel

We can also change the maximum so that the axis spans a non-integral number of cycles. Here the maximum is 12 to fit the data. This looks the same using base 10 or base 2.

We don’t get labels other than at the minimum because the axis spans less than a power of base 10 or of base 2, but we can use the same protocol as above to add points with data labels. The advantage Excel 2007 has over 2003 is that we can use the actual values to locate the points, and we can simply use the Y value data label option. In Excel 2003 we had to use the log of the Y positions, and eith