There are a few different ways you can pull stock prices into Excel. You can use the new STOCKHISTORY function, pull data from Google Sheets which has a native stock function, or you can also use Power Query. In this post, Power Query is what I am going to focus on and show you how you can pull data right from Yahoo Finance. Of course, if you don’t want to do it yourself, I also have a template that is ready to use and download (at the bottom of the page).
Creating the query
For this example, let’s pull Apple’s stock history for the past month. To do this, we can simply go to the Yahoo Finance page that shows the stock’s recent price history, located here and select any interval, whether it is five days, one month, or three, it doesn’t matter.
To get the complete data set, I’m going to copy the actual CSV download link from that page, not simply the URL. That way, it is possible to pull a much wider range than the default of 100 days.
I’m going to use that link to set up the query. To create it, go into the Data tab, select the From Web button next to Get Data:
On the next page, you’ll be given a place to enter a URL, and this is where I am going to enter the download link from Yahoo Finance:
Click on OK and Power Query will connect to the web page. Next, you will see a preview of the data and if it looks okay, you can just click on the Load button:
Then, the data will load into your spreadsheet and it should look something like this:
If that’s all you need, you can stop here. The only downside is if you wanted to look at a different ticker or change the date range, you would need to get a new link, and update the query manually, which is not ideal at all. This can be automated and takes a little more effort but it can be done by adding some variables and making some tweaks to the query.
Setting up the variables
In Power Query, you can utilize named ranges. In this case, I’ll set them up for the ticker symbol, as well as the start and end dates. That way, I can pull up a stock’s history for a specific time frame. The three named ranges I’m going to create are called Ticker, StartDate, and EndDate which can be entered all in the same place:
For the dates to work on Yahoo Finance, they need to be converted to a timestamp. This is what that calculation looks like:
=(A1-DATE(1970,1,1))*60*60*24
Where A1 is the date. This is what the dates look like when converted into this format:
Those timestamps are needed for the Yahoo Finance URL to populate properly. These are the values that need to be tied to a named range.
Next, these ranges need to be coded into Power Query. To do this, click anywhere on the table that the query created, and you should now see a section for Query in the Ribbon and click on the Edit button:
That will launch the editor. From there, you will want to click on the Advanced Editor button:
Then, you’ll see how the query is coded:
You can see the source variable is where the URL goes. To insert a named range from the Excel document into this code, we need to use the following format:
To keep things simple, I kept the name of the variable the same as the named range within the Excel file. Here is what the editor looks like after adding in the variables for the ticker, start date, and end date:
The one thing that I still need to adjust is the source. This is a hardcoded URL and it needs to be more dynamic, utilizing the variables.
In this part, I’ll need to adjust the query carefully to ensure that it is generated correctly. I will put the ticker variable where the ticker should go, and put the start and end dates (in Unix format). This is an excerpt of how the updated source data looks like:
Note that for the start and end date named ranges, I included the = sign to ensure the variable is read as text.
Now that the source is changed, all you need to do is update the variables and click on the Refresh All button on the data tab, and the table will update based on what you have entered.
If you want to download my template, you can do so here.
If you liked this post on how to get stock quotes from Yahoo Finance using Power Query, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.
To enter data in the price to earnings ratio calculator, start from top to bottom. Tabbing over or hitting enter will update the calculations.
Current Stock Information
Price $
EPS $
P/E
What-if Analysis
Change
to
What P/E is and why it’s important for investors
The price-to-earnings (P/E) ratio is a key metric that many investors use when analyzing whether a stock is well-priced and a good buy, given its level of earnings. The calculation takes the current stock price and divides it by the company’s earnings per share, typically over the last four quarters. You can also calculate a forwardP/E. This is what the ratio will be in the future, based on estimates of earnings.
This is a particularly useful calculation in a year like 2020 when the coronavirus pandemic has thrown many businesses out of whack and some are over or underperforming. And that means their P/E ratios may not be all that reliable right now.
Using a P/E ratio is particularly useful when comparing one stock against another. If a stock is trading at a very high P/E of 50 or more, it could be a sign that it’s overvalued. However, this can be skewed if a company is coming off a bad quarter where its profits were low. It’s always important to consider the context. And comparing different types of industries may not be helpful, either. A bank stock that is relatively stable and that may not achieve much growth will trade at a much lower P/E than a high-growth tech stock where its sales are climbing by 50% or more.
How to use this calculator
I wanted to create a calculator that could be useful for setting up alerts. For instance, if a stock is trading at a P/E of 50 and you want to set up an alert for when it falls to a lower multiple. You can use the What-if analysis section to plug in the P/E that you want to buy it at. It will then tell you the price it will have to fall to or the EPS that it will need to rise to.
You could also use it as a simple P/E calculator. While many financial websites may give you a P/E number they won’t always update quickly, like when a company reports its earnings. If you know what the new P/E is, you can plug it into the calculator. You can also do a what-if analysis to see what the ratio will be if earnings rises or falls to a certain number.
To enter data into this calculator, you’ll want to start from the top and work your way down. Enter the price and EPS first and then make your selections in the what-if analysis. If you go straight to the what-if analysis then the calculation won’t be correct. As you’re entering data and tabbing over, the formulas will automatically update. Hitting enter after entering in a number will also update the calculation.
Another calculator you may want to try is the average down calculator, which can help you determine how many more shares you’ll need to buy to get your average price down to a specified amount.
If you liked this post on the price to earnings ratio calculator, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.
Several weeks ago, I discovered that Excel had a new function called STOCKHISTORY. It’s able to pull stock prices and a great way to track stock prices and can help calculate returns. Excel does make it clear that it is not for trading purposes. However, it’s still a great way to stay on top of tracks and see how they’re performing. Below, I’ve created a template that will allow you to track stock prices and arrange them from best-to-worst.
Note that for this template to work, you need to have the STOCKHISTORY function on your computer, otherwise you’ll get nothing but errors. So your first step will be to check if it works on your file. Refer to the original post on the function as it will also explain how you can get it on your computer if you don’t already have it. If you’re running on old versions of Excel, you’re out of luck.
But for those that aren’t and that have access to the function, read on.
Entering the ranges that you want the macro to sort.
Let’s start with the first one, selecting stocks. I’ve already created three stock sections in this template, which you can of course change. Let’s look at one of them as an example:
The Start, End, and Return values are formulas. The only things you need to enter are the ticker symbols. Off to the left, shaded in light grey, I’ve also entered the code for the exchange. For the New York Stock Exchange, it’s XNYS, while the NASDAQ is XNAS. For a full list of the codes, refer to the original post on the STOCKHISTORY function. If it’s a popular stock that’s on one of the major exchanges, you may not need to enter it. I’ve included the exchange code for the sake of avoiding errors as it’s possible Excel might not know which ticker you’re looking for and select the wrong one.
You can extend the ranges to accommodate more tickers, you’ll just need to copy the formulas down in the Start, End, and Return sections.
Next: the date ranges.
Off to the right of the template, there’s a section where you can enter the start and end dates.
The template will adjust for weekends but not for holidays. If you see a #VALUE! error in the values, that likely means there’s an issue with the date, so you’ll just need to change one of the dates to ensure it doesn’t fall on a holiday.
Lastly: the ranges to sort.
To the right of the dates, there’s another area where you can enter which cells to sort:
Cells E8, K8, and Q8 on this template are where my ‘RETURN’ headers are located, and where the percentages are. If you add sections or modify this template, you’ll need to update the cells to sort. When you update the start or end dates, the template won’t automatically re-sort until you click on this button:
If you get an error on the re-sort button, make sure you check which cells are in the Cells to Sort area and ensure that they’re correct.
#CONNECT! errors
One thing you may run into on this template are #CONNECT errors. I’ve noticed this happens once you start adding too many ticker symbols. Sometimes it’s hit or miss and you’ll get all the prices updated, but if you’re planning to list every ticker out there, just be forewarned that you might run into issues here. It’s a separate error from the #VALUE! error and one that can’t be fixed through the template, without removing some ticker symbols, anyway.
If you liked this post on the StockHistory Template, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.
For a while, one of the big advantages Google Sheets had over Excel was the ability to pull stock quotes easily. But that’s no longer the case as there is a new function in Excel that allows you to pull in stock price history. Below, I’ll cover how to use the StockHistory function.
How the function works
The function itself is fairly simple and requires just two arguments at a minimum, and that’s the stock ticker and the start date. By default, the function will return the closing prices from the start date until today. For instance, if I want to pull Tesla’s share price since the start of the year, this is what my formula will look like:
=STOCKHISTORY(“TSLA”,”2020-01-01″)
The formula will then generate an array. Here’s a portion of what it looks like:
If you want to pull just the most recent share price, here’s what you can do:
=STOCKHISTORY(“TSLA”,WORKDAY(TODAY(),-1))
Using the WORKDAY formula you can ensure that you’re going back one business day. You may need to adjust this if you’re on a weekend but basically you just need to manipulate the date to make this work. Note that this doesn’t appear to give you the current day’s close. When I ran this on a Friday, the most recent closing price it returned was from Thursday’s close. It’s clear this function’s intended for historical data rather than live or even delayed stock prices.
If you want to specify an end date for your data, you can enter a date in the third argument, right after the start date.
The function gives you many options, including which data points you want to pull in and what intervals you want. You can pull prices on a monthly or weekly basis by selecting either a 0 (daily), 1 (weekly), or 2 (monthly) for the interval argument. Here’s how I’d pull monthly prices for Tesla:
=STOCKHISTORY(“TSLA”,”2020-01-01″,,2)
It’s important to note that these aren’t monthly averages, they’re just the stock prices as of the end of the specified month. Although the date for the first entry suggests January 1 (the markets weren’t open that day), that’s actually the January 31 closing price.
You can choose whether you want to see the headers and you can also add more fields, including the opening price, the high, the low, and the volume. You can even determine if you want to even see the date (although that’s probably not a good idea when you’re looking at historical data).
It’s easy to make a template with this function since it populates the data for you. Using variables for the ticker, the start date, and the end date, I can quickly set up a sheet that’s easily updatable:
The only formula that I enter is the one cell for the STOCKHISTORY function:
=STOCKHISTORY(C2,C3,C4,0,1,0,1,2,3,4,5)
Where C2, C3, and C4 refer to the stock, start, and end dates. The numbers 1 through 5 are needed to ensure that all the fields are extracted.
If you want more details about this function including the different arguments, you can check out Microsoft’s official page for this function.
How can I get other (non-US) tickers?
One of the things you’ll notice from the above examples is that I didn’t enter any prefix for the stock ticker. The StockHistory function knew I was looking for Tesla’s stock price. However, if you want to pull data from other exchanges, including those outside the U.S. markets, you’ll need to add a prefix to make sure that you’re getting the right quote. And since the function won’t actually return the company name, you need to make sure you’re entering the ticker correctly into the function.
Refer to this link for all the different market identifiers. For instance, if I wanted to pull the share price of Air Canada, which trades on the Toronto Stock Exchange, I’d need to enter the ticker as follows:
XTSE:AC
In most cases, it looks as though it’s just an X before the exchange’s usual prefix but you’ll want to double check to make sure.
Why you may not find the StockHistory function on your version of Excel
Since the function’s in beta, StockHistory is not available for most users. You can, however, sign up for Microsoft’s Office Insider program which will give you access to functions while they’re in beta. To join the program, follow the steps outlined here.
If you liked this post on How to Use the New Stock History Function in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.
Scroll further down if you would like to see details as to how this calculator works and a description of it.
There is a new version of this calculator available here (mobile-friendly) that will make calculations at multiple price points at once (use the new interval field to specify how much in price you want to jump by). It will also let you know how low you can average down at the current share price.
If you invest in stocks and want to know how much it would cost you to average down, this calculator will help you do just that. Averaging down is a great way to take advantage of a stock that’s dipped in value and that you’re confident won’t stay there. By purchasing more shares of a stock at a lower price, you’re bringing down the average cost of your total investment. And that means you’ll need the stock to rise to a lower price than before to turn a profit. Or if you’re already in the black, then you can put yourself in a great position to increase those profits.
How the average down calculator works
To use this calculator, you’ll need to enter the total dollars that you’ve invested in a stock, how many shares of it you own, what the current price of the stock is today (or the price that you plan to buy it at), as well as what price you want to average down to. Then, click on the Calculate button. The calculator will then tell you how many shares you’ll need to buy and how much it will cost you in order for you to get to that average.
Note that since you can’t average down below what the current share price is, you’ll have to make sure that your desired average price is higher than where the stock is today. Here is the calculator:
If you liked this free average down calculator, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download.
A tax-free savings account (TFSA) is a very useful tool for Canadian investors to shield investment gains and dividend income from taxes. However, it can be challenging to keep track of the rules and just how much you’re able to contribute and what your TFSA limit is for the year. But that’s where this TFSA template will be able to help you.
What this template will help you do
The purpose of this template is to help you keep track of both the contributions you make to your TFSA as well as the withdrawals so that you know what your limit is in a given year. If you’ve got multiple TFSAs and they aren’t all at one financial institution, keeping track of all your transactions can be a challenge. That’s where a spreadsheet can come in very handy; having all your information all in one place can make it much easier to stay on top of your TFSAs.
By logging your transactions each time you make a withdrawal or contribution from one of your TFSAs, you can have a complete picture of your balance at any given time. There’s no limit to the number of transactions you can enter in the template, and this can be used for a running total — forever. And with no macros and a simple, easy-to-use interface, the goal of this template is to make the process as painless as possible.
Why it’s important to know your TFSA limit and track your balance
Probably the main reason that you’ll want to keep track of your TFSA balance is that if you end up overcontributing you can end up with a hefty penalty.
At 1% per month of the overcontributed balance in a given month, the penalty can grow very quickly depending on how much you’ve overcontributed by and for how long. The last thing you want to see is your TFSA incurring costs rather than growing your savings. It would be a bit counter-intuitive, to say the least. Any fees that are incurred in a TFSA are not tax-deductible and that’s why overcontributing to it is something you want to avoid.
One easy mistake that can cause problems for TFSA holders
While it may seem simple to track your balance, there’s one issue that can cause headaches for TFSA holders, and that’s when it comes to withdrawing funds. One of the advantages of a TFSA is that since the funds that are contributed are after-tax, you don’t incur any penalties for taking money out. Unlike with an RRSP, you don’t have to worry about a withholding tax. With a TFSA, you can freely move money in and out of your accounts as you need it.
The caveat, however, is that when you withdraw funds, the contribution room isn’t replenished until the beginning of the next calendar year. And so if your TFSA had been maxed out on July 1st and you had withdrawn $10,000, then that will free up contribution room –- but it won’t be until January 1st. Any withdrawals that are made, regardless of the time of the year, won’t free up space until the beginning of the next calendar year.
That’s where much of the complexity comes into play when it comes to TFSAs. While contributions will reduce your available contribution room immediately, withdrawals won’t make that room available until next year. That lag can create many problems for TFSA holders. That lag can give people the misleading impressing that they have contribution room since they recently took money out, and that’s where overcontributing can happen very easily.
Suppose your TFSA is maxed out (2019 cumulative balance is $63,500) and you pull all the funds out today and they re-contributed them immediately after. In this scenario, you’ve now overcontributed by the entire balance -– meaning you’ll get a 1% penalty on that entire amount, which would amount to $635. And that’s just for one month. Leave that overcontribution in your TFSA and those penalties will pile up quickly.
While that may not be a common scenario that will take place, it’s an extreme example that helps to demonstrate just how costly it can be to make a very simple mistake. That’s why simply tracking the balance and looking at contributions and withdrawals is not enough, TFSA holders need to factor in the lag that happens with withdrawals. It’s a small but important detail that can make a big difference in determining how much contribution room you have available.
Using the template to track your TFSA limit
The template itself is very simple to use and there’s only one area where you’ll need to enter data, and that’s in columns K:N. There, you’ll enter the date of your transaction, if it was a contribution or withdrawal, and the amount. The year field is the tax year and it will auto-populate once you enter the date. You can keep adding to the list of transactions as you need to and the table will continue expanding.
The one thing to remember is that withdrawals should be negative and contribution amounts will need to be positive.
Once you’ve entered your transactions, the summary in columns A:I will update on its own:
The one time you will need to update the above table is when there is a new year to be added. Since there’s no guarantee what a future year’s TFSA limit will be, you’ll need to manually add the year as well as the new contribution room. As you can see, in prior years, the TFSA contribution limits have fluctuated, normally ranging from $5,000 to $5,500, with 2015 being the exception with a limit of $10,000.
However, to add a year is as simple as entering the new information below the most recent line. The table will automatically expand and the formulas will auto-update as well. And then you’ll just need to enter the current year’s contribution limit (column B), and the cumulative limit will be calculated automatically.
Here’s a breakdown of all the columns in the template:
Column A: Year – manually entered.
Column B: Annual Limit – manually entered to reflect the current year’s contribution limit.
Column C: Cumulative limit – this is automatically calculated based on the data in column B.
Column D: Contributions – this is the total amount of the contributions made during the year, based on the transaction data.
Column E: Withdrawals – this is the total amount of the withdrawals made during the year, based on the transaction data.
Column F: Cumulative Contributions – this is the running total of all the contributions you have made over the years.
Column G: Prior-Year Withdrawals – these are the withdrawals that were made a year ago that will be added to the current year’s TFSA contribution room. For instance, you’ll notice that the transaction from earlier that was a withdrawal of $5,000 made during 2010 is not added back to the TFSA balance until 2011.
Column H: Cumulative Withdrawals Added Back – this is the running total of the amounts in column G.
Column I: Available Room – this is your available contribution room based on all the transactions that have been entered.
Going over your TFSA limit
If the available room, column I, goes negative and indicates that you have overcontributed to your TFSA, the amounts will be highlighted in red. That being said, just because it hasn’t highlighted in red doesn’t mean your safe and should use this as a guide and not an absolute indicator of whether you’re okay or not.
There are many reasons why you could see differences from your own calculations versus how much room the CRA says you have. If, for instance, you’ve incurred gains or losses in your TFSA your contribution room will be affected. If you grew your TFSA to six figures or more and then went to withdraw those funds, then your contribution room would be replenished by your withdrawal amount, meaning you’d have a lot more room to use. On the flip side, if you’ve incurred losses, you can’t recoup that contribution room and are stuck waiting for the next year’s contribution limit.
Ultimately, your TFSA contribution room could look a lot different if you haven’t been eligible for TFSA since its inception, or if you’ve had gains or losses impact your available room. That’s why it’s important to take steps to ensure your information is up to date.
Planning makes perfect
Even if you haven’t made any transactions during the year, what you can do is to enter transactions you expect, or plan to make. Especially if you’re expecting to withdraw funds, you’ll want to budget for that in this template to ensure that it won’t cause a problem for you. Doing some planning beforehand can help prevent problems down the road, and save some costly surprises.
Checking your data
One of the most important things that you can do is to verify that your data is correct. Columns D & E in the template can be the most useful in this case because these amounts should reflect all the contributions and withdrawals that you made during the year. If you can’t reconcile to these numbers, then you know you’ve got a problem
Note that if you’ve made an error and overcontributed too much and then made a withdrawal to correct it, this template would still not reset the balance until the following year. In those cases, you may want to leave out the overcontributed amounts as well as the subsequent withdrawal to correct it.
When in doubt, your best bet is to confirm with the CRA. If you have My Account setup for access online, what you can do is access your balance as of the beginning of the year. While it won’t have all the contributions and withdrawals that you have made since the start of the year, you will have information on your available room as of January 1. This will at least give you a number that you can use as a starting point and then after factoring in your transactions, you can determine what your up-to-date balance is.
Downloading the file
As always, if you want to go ahead and try the file out just keep in mind there are no guarantees that come with it and that when in doubt, you should always verify your information with the CRA especially when it comes to determining how much room you still have available.
The
template will not factor in penalties and, ultimately, it’ll depend on how up to
date your recordkeeping is.
The file is completely free of charge with no limitations. If you like it, please give this site a like on Facebook and also be sure to check out many other templates that are available for download. You can also follow us on Twitter and YouTube.
Unfortunately, there’s no Excel formula that can add stock quotes for you. However, there is a workaround for that which can help you get what you’re after. In a previous post, I covered how to pull stock quotes using Google Sheets which is able to pull in prices and all sorts of other data. And in this post, I’ll show you how to get the data from Google Sheets into Excel.
In essence, Google Sheets is your data source or database, and you’re going to import that into Excel. It’s not specific to stock quotes, but it’s an example of how you can accomplish the same thing. So first up, you want to create your file in Google Sheets using that earlier post as a guide. Here’s an excerpt of what my file looks like in Google Sheets:
Once you’re ready, it’s time to link your Excel file to that Google Sheets file, and here’s how to do that:
How to Link Google Sheets to Excel
On the File menu, click on the button to Publish to the web
On the next screen, you should see something like this:
2. Select the tab that you want to export under the Link section and change Web page to Comma-separated values (.csv) and click on the Publish button, that will generate a URL:
3. Copy the URL that was generated in Step 2 and go back into Excel and under the Data tab click on the From Web button which is in the Get & Transform Data section
Paste it into the next screen’s URL field and click OK
On the next page you should see a preview of your data and if it looks okay then click on the Load button.
What you should see afterwards is what was on your Google Sheets tab from earlier:
And there you have it, your data from Google Sheets linked into Excel. If you make changes to your Google Sheets file, or if you want to refresh the stock quotes, right-click anywhere in the Excel sheet and select Refresh. Note that sometimes it may take some time before the file is updated on Google Sheets and before you’ll see any changes that you have made to the file.
It may not be an ideal solution if you’re looking to get stock quotes, but it gets the job done and avoids you having to try and find a complex formula or macro to pull the data that you want. You can use the Excel sheet with your Google Sheets data as a database and then lookup the stock prices from another sheet. The benefit of using Google Sheets is that you can have the best of both worlds – putting data online that you can easily update, and not be limited to Google Sheets and be able to edit and manipulate it as you need to in Excel.
Word of caution: if you delete or move around data in Google Sheets it could cause issues, especially if columns are missing and when you go to refresh it cannot find them anymore. If there is an error as a result of it or if you need to change the source, you’ll want to edit the query. When you click on the data in Excel you should see a section for Queries & Connections where you can edit the query. This is where you can select which data you want to include as well as change the source that you are pulling from. However, if it may be easier to just re-publish the data.
If you liked this post on How to Add Stock Quotes Into Your Excel Spreadsheet, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.
When you’re doing financial analysis, it’s helpful to add some context to growth numbers. That’s where using a Compounded Annual Growth Rate (CAGR) is very useful. Rather than saying a company has grown 50% over 10 years, you could say instead that they’ve grown by an average of 4.1% every year. It also gives you a percentage to use going forward if you need to forecast what you expect next year’s growth to be since you’ll have a starting point. The CAGR effectively tells you what the average growth rate has been during that period of time. It doesn’t mean that it’s grown every year by that rate, but that on average, it has risen by that amount. Below, I’ll go over how to calculate CAGR in Excel.
Using Amazon as an example
To calculate CAGR what you need to know are just two things: the total growth and the duration of time. Let’s use Amazon’s sales as an example. In 2018, total revenues for the year reached $233 billion. Back in 2010, the company had $34 billion in sales, meaning that Amazon’s revenues have grown by 585% during that time. Impressive, no doubt, but it doesn’t tell us how well it’s typically done on a yearly basis. This is where we calculate CAGR to help determine what the average growth was over that time.
From 2010 to 2018, that’s eight years that it took for sales to grow by 585%. To calculate CAGR, we use the following equation:
(Current Year Amount / Base Year Amount) ^ ( 1 / # of Years)
In the Amazon calculation, it would look as follows:
(233/34) ^ (1/8) – 1 = 27%
What this tells us is that Amazon for the past eight years has averaged an annual growth rate of around 27.2%. There’s an easy way we can prove this out. Starting with our base year of 2010, take the sales amount of $34 billion and multiply it by (1 + growth rate) each and every year. Here is how the level of growth looks like year over year:
Year
Prior Year Revenue
Growth
Current Year Revenue
2010
$34, 000
2011
$34,000
27.2%
$43,248
2012
$43,248
27.2%
$55,011
2013
$55,011
27.2%
$69,973
2014
$69,973
27.2%
$89,006
2015
$89,006
27.2%
$113,215
2016
$113,215
27.2%
$144,008
2017
$144,008
27.2%
$183,177
2018
$183,177
27.2%
$233,000
As you can see, if we assume a 27.2% growth rate each and every year, we arrive to the same end value. Although Amazon did not grow at this consistent of a pace, using CAGR helps to average the results over a period of time.
Whether you’re looking at sales, dividends, or any other kind of growth, using CAGR can be a very useful tool in putting into context just how the strong the rate of growth was.
Using CAGR to help forecast
Having CAGR is also useful if we want to forecast out future sales. Let’s assume that Amazon will have a more modest rate of growth for the next 10 years. That rather than a CAGR of 27.2%, it’ll be closer to 20% instead. Now, we can create a forecast around that and assume that sales will grow by 20% each year (on average) for the next 10. Our forecast would look as follows:
Year
Prior Year Revenue
Growth
Projected Revenue
2019
$233,000
20%
$279,600
2020
$279,600
20%
$335,520
2021
$335,520
20%
$402,624
2022
$402,624
20%
$483,149
2023
$483,149
20%
$579,779
2024
$579,779
20%
$695,734
2025
$695,734
20%
$834,881
2026
$834,881
20%
$1,001,857
2027
$1,001,857
20%
$1,202,229
2028
$1,202,229
20%
$1,442,675
To prove this out, what we can do is the following: 233,000 x (1.20^10) = 1,442,675
Based on these projections, we would expect amazon to hit the one trillion dollar mark in sales by the end of 2026. Maintaining a CAGR of 20%, however, will be difficult, even for a company like Amazon. Although all it takes is some big acquisitions and it will be well on its way!
If you don’t want to calculate CAGR on your own, you can use our free online calculator!
If you liked this post on How to Calculate CAGR in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.
