A Step-By-Step Plan to
Calculate Hubbert’s Curve

Ing. Hans Zandvliet, April 2014

Table of Contents
Do the Math of Peak Oil and Convince Yourself
Table of Contents
Introduction
Download and Organize CDIAC Data
Methodology of Calculating Hubbert’s Curve
Do the Math!
Back to Our CDIAC Database
Defining the Linear Trend Line
Calculating Hubbert’s Curve
Matching Hubbert’s Curve with Historical Data
Combining Hubbert’s Curve and Historical Data in a Graph
Conclusions
Recent Developments
Bibliography

Introduction

Until Colin J. Campbell and Jean H. Laherrère published their paper The End of Cheap Oil in 1998 (Campbell & Laherrére, 1998), the petroleum geologist Marion King Hubbert (1903 – 1989) was all but forgotten, including his correct forecast – back in 1956 – of the US’s peak of oil production in 1970 (Hubbert, 1956). In their paper Campbell and Laherrère warned that:

“Barring a global recession, it seems most likely that world production of conventional oil will peak during the first decade of the 21st century.”

It took another 12 years, but eventually the oil production optimist par excellence, the International Energy Agency (IEA, of the OECD countries), also had to admit the undeniable in their World Energy Outlook of 2010 (IEA, 2010):

“Crude oil output reaches an undulating plateau of around 68–69 mb/d by 2020, but never regains its all‐time peak of 70 mb/d reached in 2006, while production of natural gas liquids (NGLs) and unconventional oil grows strongly.” (emphasis added)

Since Campbell’s and Laherrère’s paper, the peak of global oil production has been studied and commented on intensely. Centre stage in the debates has been Hubbert’s peak-oil calculation. However, in order to calculate Hubbert’s curve, one needs to have reliable petroleum production data and this is precisely the biggest problem. Oil reserves and production data of many countries and oil companies are kept secret, so one has to circumvent those obstacles by piecing together national import and export data from all countries in the world. Doable for a petroleum geologist, but not for an ordinary college student.

And this is a pity, because ordinary people cannot verify what the experts say about the timing of the peak of global oil production. The entire calculation remains a kind of magical mystery in which to either believe or not. The calculation itself is not the problem; any college student with a bit of affinity for mathematics should be able to do these calculations. The lack of reliable data is the problem.

Download and Organize CDIAC Data

Fortunately there is a fairly accurate solution to this problem, freely accessible to the general public. It’s not direct petroleum production data, but a closely related proxy: carbon emission data from liquid fossil fuels. On the basic assumption that burning one barrel of petroleum will always produce roughly the same amount of carbon-dioxide, it doesn’t really matter whether one calculates the peak of global oil production or the peak of global carbon emissions from liquid fossil fuels. The unit and vertical scale of the curve will be different, but the general shape of it and the timing of its peak will remain the same.

This database of carbon emissions from fossil fuels has been put together by CDIAC (Carbon Dioxide Information Analysis Centre), which “[…] has served as the primary climate-change data and information analysis center of the U.S. Department of Energy (DOE) since 1982.” So, CDIAC is not some kind of obscure website with dubious and poorly documented information, but an authoritative source used by climate scientists.

So, let’s go and take a look at CDIAC’s website: http://cdiac.ornl.gov/. On CDIAC’s home-page, in the pull-down menu “Data”, click “Fossil-Fuel CO2 Emissions”. Now, click “Global, Regional, and National Annual Time Series (1751-2010)” on top of the list on this page. Here you can choose between national, regional and global data. It’s worthwhile to have a look at some national data (like those of the U.K.), imagine the work to get these data together from historical archives and appreciate the magnitude of this database. (By the way, for the interested, it’s a nice cross-check to use the U.S.’s national emissions data to calculate the U.S.’s peak of oil production back in 1970.) For the purpose of this article, we click “Global” emission data. Now you can choose between three data formats: “Graphics”, “Digital Data (ASCII, Fixed Format)” and “Digital Data (ASCII, Comma-Delimited)”. Choose the third option and open the data file (presented in CSV format) with Excel. Now save the CSV-file as an XLS-file (or XLSX-file) in a map of your choice. Additionally, going back to the “Fossil-Fuel CO2 Emissions” page, click “Preliminary 2011 and 2012 Global & National Estimates” to update your database with two more years. This only requires a bit of copying and pasting of a few numbers between two Excel sheets.

You now have a database of global carbon emissions from fossil fuels running from 1751 until 2012. The data differentiate between solid (like coal and peat), liquid (predominantly petroleum) and gaseous (mainly natural gas) fossil fuels. Row number 1 contains the headings of the table, but they are very long. For convenience, rename them to something shorter, like “Year, Total, Gas, Oil, Coal, Cement, Flaring and Per Capita” (cells A1:H1). Row number 2 mentions the source of the database. Copy this information away to somewhere on the right side of the table and use row 2 to mention the units of the data (million metric tons of carbon [Mt C] for all columns, except the per capita data in column H (after 1949): metric tons of carbon [t C]). Your Excel sheet should now look like this: 

Methodology of Calculating Hubbert’s Curve

This article follows the explanations of Kenneth S. Deffeyes’ book Beyond Oil, the View from Hubbert’s Peak (Deffeyes, 2005). Deffeyes is a geologist and was a colleague and friend of Hubbert when he started his career at the Shell Research Lab in Houston, back in 1958. He wrote this book with the intention of explaining how Hubbert’s curve is calculated, so in this sense my article is not that novel. However, my aim is to get more into the details of how to actually do it, step by step, by means of an (Microsoft Office 2007) Excel sheet and (which I think is novel indeed) CDIAC’s carbon emission data from liquid fossil fuels.

Marion K. Hubbert discovered a peculiar pattern in oil production data, which allowed him to make a fairly accurate estimate of the amount of oil “yet to be discovered”. This almost sounds like a contradiction of terms: how can you know how much oil you are going to discover in future decades? Still, Hubbert discovered a linear trend in historical oil production data and a linear trend can easily be extended into the future. The following graph shows the pattern of oil production that Hubbert discovered: 

The vertical axis represents the oil production of a certain year divided by the cumulative oil production up to that year (“P” stands for annual oil production and “Q” means cumulative oil production). The horizontal axis represents the cumulative oil production in billions of barrels. 

Every dot represents the data of one year. At first the cumulative production is still little, so the quotient of P/Q gives relatively high outcomes (the blue dots in the graph). But the cumulative oil production keeps rising steadily, so the outcome of the quotient P/Q trends downward. Most importantly, from 1983 onward the dots start to line up fairly close to a straight line (the red dots in the graph) and form a linear trend (the black line in the graph).

If we extend this linear trend line until it reaches the horizontal axis, we can get an idea of the ultimate recoverable amount of oil. Upon reaching the horizontal axis, the quotient P/Q becomes zero, meaning that the annual oil production has become zero and no more oil can be produced. Therefore, this point indicates the total cumulative oil production at the end of the age of oil. Once you know how much oil you can ultimately produce, you have the key to calculate the future trend of annual oil production.

Do the Math!

In mathematical terms, the equation of a straight line is very simple: 

Where “b” is the y-value where the line intersects with the y-axis. In other words, the y-value when the x-value is zero.

The “a” indicates the inclination of the line. When two points on the line are known, this inclination can be calculated as follows: 

Or, the difference in y-values divided by the difference in x-values of the two known points. A negative outcome means the line slopes downward from left to right.

So, if we can define this linear trend line, we can calculate the future trend of oil production and to quote Deffeyes: “Since we want to estimate likely future trends, nothing beats a straight line on a graph.” No cooked up magic added, just straight forward mathematics.

First we translate the mathematical terms into oil production terms, taking the intersections of the trend line with the x and y axes as the two known points: 

With these translations, the mathematical formula becomes the following oil production formula: 

Or simplified: 

To calculate annual production data, you’d like to know “P”, not some incomprehensible quotient “P/Q”. This is easily achieved by multiplying both sides of the equation with “Q”: 

To define “b” and “Qt”, we will make use of Excel’s linear trend line option. Then we will come back to this formula. 

Back to Our CDIAC Database

Time to roll up some sleeves, because now we’re starting to elaborate our Excel calculations. First, insert two new columns (E and F) to the right of the column with carbon emission data from liquid fossil fuels (D). Merge cells D1, E1 and F1, rename cell D2 into “P [Mt C]”. Name cell E2 “Q [Mt C]” and F2 “P/Q”. Your Excel sheet should now look like this: 

The cumulative emission data are easily calculated by adding the emission data from the present year to the cumulative emission data of the previous year. Of course, 1751 doesn’t have a previous year in this database, so here (cell E3) we enter “0”. In cell E4 enter the Excel formula “= E3 + D4” and copy it down to all cells of column E (cells E5 till E264).

Petroleum was not yet discovered in 1751, so everything is still zero. As from 1870 the first emission data from liquid fossil fuels appear in the database. For convenience, you can split the window (menu tab “View”) to keep visible the heading of the table. Go to cell F122 (of the year 1870), enter the Excel formula “=D122/E122” and copy it down to the rest of column F (cells F123 till F264). Your Excel sheet (as from 1870) should now look like this:

Defining the Linear Trend Line

From the menu tab “Insert”, select the graph type “Scatter with only Markers” and a blank window opens up to design a chart. From the menu tab “Design” (“Chart Tools” are only visible when you select the chart window), click the button “Select Data” and a dialogue box opens. Click “Add” (under “Legend Entry (Series)”) and yet another dialogue opens in which you can type a name for the data series and the range of x-data and y-data. Under “Series Name” type something like “P/Q (1900-2012)”. I suggest making the graph as from 1900, because initially P/Q values start at 1, so the vertical scale becomes very large and the final and most relevant values between 0.04 and 0.02 become barely visible. For your x-data select the values E152:E264 and for your y-data select the values F152:F264. With some layout editing, your graph could now look like this: 

Below a value of 0.04, you can see the dots lining up in a roughly linear way. When you move the cursor over the first dot just below the 0.04 line, an information box shows the coordinates of this dot: “Series “P/Q (1900-2012)” Point “55,931” (55,931, 0.0389)” Looking up these data in the table and you will find that they belong to the year 1983. So, as from 1983, the carbon emission data from liquid fossil fuels start to line up in a linear way.

Before we can let Excel calculate a linear trend line, we first have to make a second graph of the 1983-2012 data. Do this in exactly the same way as the former graph, but only select the 1983-2012 data. Your graph should now look like this: 

Of this second graph (of red dots) we let Excel calculate a linear trend line. You could calculate it yourself (the WikiPedia page “Linear Regression” will tell you all you need to know), but it’s very tiresome work and too farfetched for this article.

From the “Chart Tools” select the menu tab “Layout”, click the “Trendline” button, select “More Trendline Options” and select the “P/Q (1983-2012)” series. In the “Format Trendline” dialogue box, select the “Linear” type of trend line. At the bottom, tick the selection box “Display Equation on Chart” and close the dialogue box. You now see the linear trend line as a thin black line in your graph, together with its mathematical equation.

To improve the precision of this equation, right-click on it and from the quick menu that pops up, select “Format Trendline Label”. Now format the numbers of the formula as “Scientific” with two decimals (more decimals will only give a false impression of high accuracy). In this case scientific numbers come in quite handy because the P/Q-numbers are very small and the Q-numbers very large (so, don’t bother about them and let Excel do the job). Brightening up your chart a bit, your graph should now look like this:

Now we have the values for “a” (-1.84·10-7) and “b” (4.74·10-2) and we can convert the mathematical trend line formula into our carbon emissions formula by changing “y” into “P/Q” and “x” into “Q”: 

Annual emission data follow by multiplying both sides of the equation with “Q”: 

We can now calculate the ultimate amount of carbon emissions from liquid fossil fuels at the end of the age of oil (Qt). This is when P = 0: 

So let’s solve this equation: 

Calculating Hubbert’s Curve

Now we can calculate Hubbert’s curve of CDIAC’s carbon emissions from liquid fossil fuels. First we not only want to calculate our Hubbert’s curve up to 2012, but through the end of this century, so we have to extend column A to 2100. The quickest way is to enter the Excel formula “=A264+1” in cell A265 and copy this formula down to the cells A266 till A352.

To the right of column F (with the P/Q data) we insert two new columns G and H. Merge cells G1 and H1 and type something like “Hubbert Oil”. Something similar you can do with cells D1, E1 and F1, naming them Historic Oil. In cell G2, write “P [Mt C]” and in cell H2 “Q [Mt C]”. Your table (as from 1870) should now look like this: 

We need to put the values of “b” and “Qt” somewhere in the sheet. I put them in the following cells:

• Cell M265: “b [P/Q]”
• Cell M266: “4.74E-02” or “0.0474” (same value, different formats)
• Cell M267: “Qt [Mt C]”
• Cell M268: “258,000”
• Cell M269: “Q-1870 [Mt C]”
• Cell M270: “1” (for the time being, later you will see why)

In cell H122 enter “=M270” (To check, this is Hubbert’s cumulative emissions in the year 1870). Since the calculation of cumulative emissions remains the same, copy cell E123 to the cells H123 till H352.

We have to be careful now, because we’re going to use Q to calculate P and P to calculate Q, which results in a circular reference. However, given the right settings, Excel can easily deal with that. Click the Office button in the top-left corner of Excel and click the “Excel Options” button (bottom-right). Now click the “Formulas” button (top-left) and activate “Enable iterative calculation” (top-right). Usually it’s not necessary to tinker with the maximum amount of iterations and change. Leave the Excel Options by clicking “OK”.

Now we’re going to use the formula we arrived at on page 4 (repeated here, for convenience):

In Excel-format, enter in cell G122: 

The dollar-signs mean that the row-numbers of these cells don’t change when the formula is copied. This is necessary, because M266 and M268 are the two numbers “b” and “Qt”, which occupy one fixed cell. Copy cell G122 down to the cells G123 till G352. Your table should now look like this: 

You can see I gave the cells E264 and H264 a bold and blue format to let them stick out. Likewise I made cell M270 bold and red. It’s handy to do the same.

Matching Hubbert’s Curve with Historical Data

So what do we have now? We have a Hubbert’s curve (for sure, if you copy it down another century), but it’s not yet matched with the calendar years of historic emission data. To imagine what needs to be done, we need to move our Hubbert’s curve horizontally across the chart, until it best fits with the historical data. The most acurate way to do this is to adjust the initial value of cumulative emissions in 1870 (we set it to “1”, for time being) such that Hubbert’s cumulative emissions in 2012 equal the historical cumulative emissions of that same year (that’s why I marked them blue and bold for comparison).

Now finding this match is a matter of changing the value “1” of cell M270 and seeing what happens. To avoid having to scroll up and down between 1870 and 2012, I placed this adjustment cell at M270, while H122 uses M270 as its starting value (now you see why).

So, give it a try and change cell M270 from 1 to 10. You should now see that the blue cell H264 has increased from 984 to 9,498. So, we’re heading in the right direction, but we’re not there yet. Try 100 instead of 10. H264 has now increased to 70,639. It seems we’re half way now, so let’s try 200. H264 is now 110,198, so we’re getting there. Say, 250? H264 = 124,157. M270 = 300? H264 = 135,637. That’s just a tad too much! M270 = 299? H264 = 135,427. Still a tiny bit too much. M270 = 298? H264 = 135,217. For the record: M270 = 297? H264 = 135,006. So, 298 is a much closer match than 297: M270 = H122 = 298! We have matched Hubbert’s curve horizontally with the historical emission data.

Now, curious in which year Hubbert’s peak occurs? Look at the annual emissions of Hubbert’s curve in column G. In which year does it reach its highest value? In 2010! 

Combining Hubbert’s Curve and Historical Data in a Graph

From the tab menu “Insert”, select a 2-D line graph. From the Chart Tools select the menu tab “Design”, click the button “Select Data” and click the button “Add” (under “Legend Entries (Series)”). In the dialogue box that opens up, enter as a “Series Name” something like “Hubbert’s Curve (1870-2100)”. Select the data cells G122 till G352. You can now see you have added a legend entry, but you still have to edit the “Horizontal (Category) Axis Labels”. So, click on the “Edit” button and select the cells A122 till A352.

Now add a second data series of the historic annual emissions of cells D122 till D264 (Chart Tools -> Design -> Select Data). However, select as its calendar series the same range of years as the Hubbert’s curve (1870-2100): cells A122 till A352. After tidying up a bit, your chart should look like this: 

Conclusions

Based on CDIAC’s global data of carbon emissions from liquid fossil fuels (i.e. predominantly petroleum) and Marion King Hubbert’s method of calculating the oil production curve, we have arrived at a theoretical emissions peak of carbon from liquid fossil fuels (i.e. oil production peak) in 2010.

Compared to earlier forecasts of oil-geologists and other experts using Hubbert’s methodology, the calculations based on CDIAC data get quite close to the mark. Most of these experts (like Collin Campbell, Jean Laherrère, Kenneth Deffeyes and Matthew Simmons) forecast peak-oil between 2005 and 2010, so the CDIAC data turn out to be a fairly reliable source for this purpose.

Theoretically, we’re already 4 years past the peak, although historical data still show growth. This is very well possible for a limited period of time. Given the frantic drilling and fracking spree we’ve seen since about 2007, it’s not even surprising. Extraction methods have improved as well over the last decade or so: horizontal drilling, hydraulic fracturing, deep-sea drilling, more accurate 3D soundings of geological formations, etc. The much higher oil price has also made it worthwhile to drill for harder-to-get oil. So, the ultimately recoverable amount of oil has increased indeed (though I don’t know by how much) and this is not (yet) reflected in the historical data.

Recent Developments

Since a few years, there has been a lot of talk that there are so much fossil fuel reserves left that we can only responsibly burn about one third of them (leaving two-thirds in the ground) in order to keep global warming below 2°C (IEA, 2012). This also implies that the shares of fossil fuel companies are over-valued by up to 80% (Carbon Tracker, 2012; Carbon Tracker, 2013). However, there are several caveats against this analysis:

1. The reserves are based on stated reserves of fossil fuel companies and governments, but we don’t know to what extent these reserves are overstated. Of the OPEC-countries it’s quite obvious that (for political motives) they overstated their reserves dramatically between 1987 and 1990. The sudden jumps in stated reserves were not accompanied by announcements of newfound oil fields. Also, it doesn’t add up that oil reserves have remained virtually constant during decades of high rates of oil production and low rates of new discoveries:

   Source: own compilation of U.S. Energy Information Administration data
   (EIA, 2013)

2. It’s not the amount of fossil fuel reserves that counts; it’s the rate at which they can be extracted. The big oilfields with high quality and easy to get petroleum are mostly depleted. We are now looking for ever smaller fields in ever harder to reach locations, like the ocean floors and the arctic. When it’s ever harder to get the oil, this will result in a slowdown of the rate of extraction (i.e. going down the other side of Hubbert’s curve).
3. The Energy Return on Energy Invested (EROEI) has already gone down seriously. The EROEI is an analysis of how much energy it takes to produce fossil energy (produce materials and operate machinery needed to construct the facilities to drill for oil and get it to the market, like drilling rigs, pipelines, oil tankers, refineries, etc.) and how much energy you get from all this. The EROEI used to be 100:1 in the 1930s – 1950s. Talking about tar sands the EROEI is already down to something like 5:1. This means that for every 5 barrels of oil (equivalent) one has to be invested into getting it, 4 are left as “profit”. Once it takes one barrel of oil to get one barrel of oil, there won’t be any profit left and it’s pointless to continue. Actually well before this point (say 2:1) because an oil company needs to sell at least some oil to pay for the investments in wages and equipment.
4. The cost of oil extraction is already such that the oil price has to be above $100/barrel in order to be profitable. On the other hand, how high a fossil fuel price can the world economy afford in order to function healthily? We’ve seen the world economy crash into the Great Recession in 2008, when the oil price soared to $147/barrel (I daresay it wasn’t exactly a housing bubble…) and the price is again hovering around $100/barrel. No wonder economic growth doesn’t want to pick up! Oil consumption simply responds to the economic law of supply and demand. When oil gets too expensive, people will turn to cheaper alternatives and oil consumption goes down (the other side of Hubbert’s curve) just the same.

So there are many ways to look at peak-oil (environmental, geological, economical, social, etc.). One way doesn’t exclude the other, so I surely don’t want to contribute to infighting between peak-oilists and environmentalists. Both are right and allies to the same cause: we have to think hard and take swift action to create a sustainable way of living in harmony with the world wide web of life on our planet, in order to prove that human intelligence was not an evolutionary error.

Bibliography

Campbell, Colin J., and Jean H. Laherrère. “The End of Cheap Oil.” Scientific American 278.3 (1998): 78-83. Print. Download at http://josiah.berkeley.edu/2007Fall/ER200N/Readings/Campbell_1998.pdf

“Carbon Dioxide Information Analysis Center.” (CDIAC). Web. 24 Apr. 2014. Website http://cdiac.ornl.gov/

Global Fossil-Fuel CO2 Emissions. CDIAC. Web. 24 Apr. 2014. Website http://cdiac.ornl.gov/trends/emis/tre_glob_2010.html

The Crisis of Civilization. Dr. Nafeez Mosaddeq Ahmed. Dead Dean Films, 2011. Film. Watch at http://www.youtube.com/watch?v=pMgOTQ7D_lk

A Crude Awakening. Dir. Basil Gelpke and Ray McCormack. 2006. Film. Watch at http://www.youtube.com/watch?v=7qGM9ypR-UI

Crude Impact. Dir. Jennifer Jandak Wood, James Jandak Wood, and Joanne Shen. 2006. Film. Watch at http://www.youtube.com/watch?v=bvyDH8-y-AE

Deffeyes, Kenneth S. Beyond Oil: The View from Hubbert’s Peak. New York: Hill and Wang, 2005. Print.

Hubbert, M. King. Nuclear Energy and the Fossil Fuels. Houston: Shell Development, Exploration and Production Research Division, 1956. Print. Download at http://www.hubbertpeak.com/hubbert/1956/1956.pdf

International Energy Statistics. EIA, 2013. Data. Download at http://www.eia.gov/cfapps/ipdbproject/iedindex3.cfm?tid=5&pid=57&aid=6&cid=CG9,&syid=1980&eyid=2013&unit=BB

Unburnable Carbon – Are the World’s Financial Markets Carrying a Carbon Bubble? Rep. Carbon Tracker. 2011. Print. Download at http://www.carbontracker.org/wp-content/uploads/downloads/2011/07/Unburnable-Carbon-Full-rev2.pdf

Unburnable Carbon 2013: Wasted Capital and Stranded Assets. Rep. Carbon Tracker. Print. Download at http://carbontracker.live.kiln.it/Unburnable-Carbon-2-Web-Version.pdf

World Energy Outlook 2010, Executive Summary. IEA, 2010. Print. Download at http://www.iea.org/textbase/npsum/weo2010sum.pdf

World Energy Outlook 2012, Executive Summary. IEA, 2012. Print. Download at http://www.iea.org/textbase/npsum/weo2012sum.pdf