how to rebase a chart in excel

How to rebase a chart

Tl;dr: This is how you make a chart so that everything starts at 100 and grows from there so you can see the relative growth

Have you ever wondered what stock has performed better or worse over a period of a year? What do things look like a year later? Did one do better than another and by how much?

Well, in investment banking, this is a 101 skill you have to learn to do deals with public companies. As a trader, analysing share performance, it’s something you do all the time. For everyone else, well no one teaches you these things.

Moving on. You want to see relative performance in one chart but the issue is that the shares all have different prices and they fluctuate independently. That’s sort of obvious since each is an individual company, but really you want them to all start at the same place.

If you were doing a 100m sprint you want to start at the same point so that timing is the same.

Only, that involves some math. How do you do that?

Easy. Let me teach you.

Rebasing a chart

Rebasing is essentially bringing all the sprinters in a race back to the same starting point so that their performance is normalised to the same starting point.

Let’s say we have two companies:

  • Google: Share price of 100
  • Taobao: Share price of 200

Taobao is 2x Google. On a graph that’s clearly not the same starting point. It’s apples and oranges.

What we want to do is to bring Taobao to the same 100 starting point at Google. By doing this every % change over a time period, of say a year, is relative to one another. It’s apples and apples and oranges and oranges.

How the math works

The math is actually really simple. You just need to know how it works. So let’s learn.

A rebased chart brings everything to the same starting point, showing an absolute price change at each point of time and how the % price changes from the starting date we selected (The rebased date).

There are three numbers:

  • Rebase #
  • Starting Price
  • Ending Price

The formula is

  • =(Rebase # / Starting Price) * Ending Price

The rebase number you want to use is 100.

The starting price is fixed, so in Excel you will have a $ around the starting price. If, like in the example model I show, you have the rebase number (100) in an assumption box, you will also have $s in the cell).

  • =($L$4/$D$9)*D27
  • =($L$4 [Rebase #] / $D$9 [ Starting price])*D27 [Ending price]

What a chart looks like without rebasing

The share price data is normal, just not from the same starting point.

normal chart

What a chart looks like with rebasing

Looky, looky. Everything starts at 100.

rebased chart

What the data set looks like

The dataset on the left is the unedited share price data.

The dataset on the right is the rebased table. You can see everything starts at 100 and then starts moving around.

rebasing data

Get the free template

I’m sure that was all super-fun reading, but you want to really have a play so you can learn.

So do that. Download the file and have a go! Get the nerd on.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.