Exponential Regression using a Linear Model

Basic Concepts

Sometimes linear regression can be used with relationships that are not inherently linear but can be made to be linear after a transformation. In particular, we consider the following exponential model:

image5101

Taking the natural log (see Exponentials and Logs) of both sides of the equation, we have the following equivalent equation:

image5102

This equation has the form of a linear regression model (where I have added an error term ε):

image5103

Observations

Since αeβ(x+1) = αeβx · eβ, we note that an increase in x of 1 unit results in y being multiplied by eβ.

A model of the form ln y = βx + δ is referred to as a log-level regression model. Clearly, any such model can be expressed as an exponential regression model of form y = αeβx by setting α = eδ.

Example

Example 1: Determine whether the data on the left side of Figure 1 fits with an exponential model.

Log transformation

Figure 1 – Data for Example 1 and log transform

The table on the right side of Figure 1 shows ln y (the natural log of y) instead of y. We now use the Regression data analysis tool to model the relationship between ln y and x.

Regression analysis log transform

Figure 2 – Regression data analysis for x vs. ln y from Example 1

The table in Figure 2 shows that the model is a good fit and the relationship between ln y and x is given by

image7110

Applying e to both sides of the equation yields

image1792

We can also see the relationship between x and y by creating a scatter chart for the original data and choosing Layout > Analysis|Trendline in Excel and then selecting the Exponential Trendline option. We can also create a chart showing the relationship between x and ln y and use Linear Trendline to show the linear regression line (see Figure 3).

Log transformation plot Excel

Figure 3 – Trend lines for Example 1

As usual, we can use the formula y = 14.05∙(1.016)x described above for prediction. Thus if we want the y value corresponding to x = 26, using the above model we get ŷ  =14.05∙(1.016)26 = 21.35.

We can get the same result using Excel’s GROWTH function, as described below.

Worksheet Functions

Excel Functions: Excel supplies two functions for exponential regression, namely GROWTH and LOGEST.

LOGEST Function

This worksheet function is the exponential counterpart to the linear regression function LINEST described in Testing the Slope of the Regression Line. Once again you need to highlight a 5 × 2 area and enter the array function =LOGEST(R1, R2, TRUE, TRUE), where R1 = the array of observed values for y (not ln y) and R2 is the array of observed values for x, and then press Ctrl-Shft-Enter. LOGEST doesn’t supply any labels, and so you will need to enter these manually.

Essentially LOGEST is simply LINEST using the mapping described above for transforming an exponent model into a linear model. For Example 1 the output for LOGEST(B6:B16, A6:A16, TRUE, TRUE) is as in Figure 4.

LOGEST Excel

Figure 4 – LOGEST output for data in Example 1

GROWTH Function

This worksheet function is the exponential counterpart to the linear regression function TREND described in Method of Least Squares. For R1 = the array containing the y values of the observed data and R2 = the array containing the x values of the observed data, GROWTH(R1, R2, x) = EXP(a) * EXP(b)^x where EXP(a) and EXP(b) are as defined from the LOGEST output described above (or alternatively from the Regression data analysis). E.g., based on the data from Example 1, we have:

GROWTH(B6:B16, A6:A16, 26) = 21.35

which is the same result we obtained earlier using the Regression data analysis tool.

GROWTH can also be used to predict more than one value. In this case, GROWTH(R1, R2, R3) is an array function where R1 and R2 are as described above and R3 is an array of x values. The function returns an array of predicted y values for the x values in R3 based on the model determined by the values in R1 and R2.

Observation: Note that GROWTH(R1, R2, R3) = EXP(TREND(R1, R2, LN(R3)))

References

Microsoft Support (2012) LOGEST function
https://support.microsoft.com/en-au/office/logest-function-f27462d8-3657-4030-866b-a272c1d18b4b#:~:text=In%20regression%20analysis%2C%20the%20LOGEST,entered%20as%20an%20array%20formula.

Microsoft Support (2012) GROWTH function
https://support.microsoft.com/en-gb/office/growth-function-541a91dc-3d5e-437d-b156-21324e68b80d

128 thoughts on “Exponential Regression using a Linear Model”

  1. Hi Charles! I’m a novice learning about price elasticity. Can you point me to a method for calculating multiple exponential regression? I’m not looking for a model, and I know how to use Excel’s solvers and analysis toolpacks. More than anything I’d like to understand how to calculate it the old-fashioned way. Something that tells me the various parts and how to calculate them. Do you know of anything like this?

    Reply
    • Hello Andy,
      The approach is very similar to that described for Exponential Regression.
      Approach 1: Take the natural log of both sides of the equation and then use ordinary linear regression
      Approach 2: Use Solver, exactly as described for Exponential Regression.
      Charles

      Reply
  2. Dear Charles.

    Thank you very much for the exponential regression explanation.

    I just have 1 question. For the exponential regression equation y= A e^rx , How do we find the value for r for only 2 data points example (0, 1.3964) (1, 1.3423) in excel without plotting a graph.

    Reply
    • Chris,
      You have two equations in two unknowns:
      1.3964 = A e^(0*r)
      1.3423 = A e^(1*r)
      From the first equation, A = 1.3964. From the second equation
      1.3423 = A e^r = 1.3964 e^r. Taking the log of both sides, we get
      ln(1.3423) = ln(1.3964) + r
      Charles

      Reply
  3. Hi,

    Can the following equation be expressed linearly? if so, how?

    y = e^B0 + B1 * X1 + B2 * X2 + E Note: B= Beta

    Reply
    • Hello Gerald,
      If I understand the equation correctly, this equation is of the form y = B1 * X1 + B2 * X2 + C where C = e^B0 + E is the intercept coefficient. Once C is found, then B0 = ln(C-E)
      This can be done by OLS linear regression provided the intercept coefficient takes the correct form.
      Charles

      Reply
  4. Charles,
    Great article. I recently came across this so called exponential regression and as you mentioned it is a linear regression after some transformation. So the question arises that one needs to check all the appropriate assumptions for linear regression before applying this excel function, right? In addition, if the data is a time series then one needs to check for stationarity as well. Please let me know if you agree or not

    Reply
    • Hello Bilal,
      Glad you found the article useful.
      1. This depends on how you will be using exponential regression. Most of the assumptions are related to confidence intervals for the regression parameters. If you are just using the regression model for predicting then you don’t need to meet all the linear regression assumptions.
      2. For time series data, the key issue is autocorrelation, not stationarity. If there is autocorrelation, you need to correct for this. This is described on the Real Statistics website.
      Charles

      Reply
    • 1. You can calculate the lower and upper interval for each point in linear regression as described at
      https://real-statistics.com/regression/confidence-and-prediction-intervals/
      You can use this to create three graphs on the same chart (lower and upper points as well as data points). You can then add a linear trendline for the data points. The result should look something like that in Figure 1 of the above webpage.
      2. Since exponential regression using a linear model is really linear regression with a transformation, you need to perform the above steps, but before plotting the results you need to use the inverse transformation.
      3. I will add an example of how to do this in the next release of the Real Statistics software.

      Reply
  5. I need to fit my data with an exponential function y=A*(1-exp(-x/B)). How to linearize my data, apply the linear fit, and find the coefficients A and B?

    Reply
    • Hi Lexy,
      I don’t believe that you can linearize this equation if A and B are regression coefficients.
      If A and B are known constants then it would be possible to linearize this equation.
      Charles

      Reply
    • Kalpana,
      I am not sure about what you mean by “hand solution”. In any case, you can download Excel worksheets about the polynomial and exponential solutions. These can be found at
      Example Workbooks
      In particular, you should download the Regression 1 workbook.
      Charles

      Reply
  6. Hi Charles,
    Thank you for this post. Would you mind elaborating on the process to use if we suspect that a polynomial equation is more appropriate?
    What is a good and easy way to make that decision (without diving into the complexities of Model Selection), and how would we then proceed to actually implement it?
    Thank you.

    Reply
    • Harvey,
      I don’t know of a good way to decide on whether a polynomial equation is more appropriate, except by trying it and see how well it predicts the output based on real data. See the following webpage for more info on this topic:
      Polynomial Regression
      Charles

      Reply
    • Psalmseen,
      y = b/(1+ae^x) is equivalent to 1+ae^x = b/y which is equivalent to b/y – 1 = ae^x. Taking the log of both side yields ln(b/y-1) = x + ln(a).
      Unfortunately, this isn’t in the form y = cx + d (unless b is a known constant), and so you need to use some form of non-linear regression (e.g. using Solver).
      Charles

      Reply
  7. Hi,
    In linear regression, we set up the null hypothesis as slope (β=0) v’s (β≠0). How can we setup the null hypothesis in exponential regression equation.

    Reply
    • Gowher,
      It really depends on which parameter you want to test, but the test that corresponds to the one you listed for linear regression is the same in exponential regression, namely (β=0) v’s (β≠0).
      Charles

      Reply
      • Thanks, if we apply t-test we take degrees of freedom as (n-1) but if we set up the null hypothesis (β=0, i.e slope is equal to zero) v’s (β≠0, i.e, the slope is not equal to zero) in linear regression. Why we have to take degrees of freedom as (n-2)? Please explain.

        Reply
        • Gowher,
          The degrees of freedom in each case is n-p where p = the number of independent variable. In the first case p = 1 and in the second case p = 2.
          Charles

          Reply
          • Thanks
            sir I have used the above exponential regression model explained by you in which “y” represents total fertility rate(dependent variable) and “x” represents birth order(independent variable) and estimates of TFR have been obtained. Now please tell me how can I check the validity of the model?

          • Gowher,
            The exponential regression model presupposes that this model is valid for your situation (based on theory or past experience). You can use the model to gain evidence that that the model is valid by seeing whether the predictions obtained match with data for which you already know the correct values. E.g. suppose you have a sample of size 100. One approach is to use 80 elements from the sample (taken at random) to create the regression model. Now use this regression model to “predict” the values of the dependent variable in the 20 remaining sample elements. Since you know the correct value of the dependent variable for these 20 items, you can compare the model’s predictions with the correct values to see how well the model performs.
            Charles

  8. hello guys ,i want to regress a given data points of t and y ,then find the coefficients and constants for the following models .
    y=a*exp(bt^c)
    y=a*exp((bt)^c)
    can any one help me how to find the a,b and c values by regression for a given datas of experiment of t and y?

    Reply
    • Mitku,
      If we take the log of both sides of the first equation we get ln y = ln a + bt^c. Now let y’ = ln y, a’ = ln a and x = t^c, then the equation takes the form
      y’ = bx + a’. You can then use ordinary regression to find the values for a’ and b. Then a = exp(a’). The only problem with this approach is that c is treated as a constant. If you want to calculate a value for c, then this approach won’t work and you will need to use a non-linear regression approach, e.g. using Solver as I have shown on the website for exponential regression. The situation is similar for the second equation.
      Charles

      Reply
  9. Let’s say we have y = a exp(b*x) + e1 where e1 is the error term. Then we solve ln y = lna + b ln x + e2 where e2 is the error term using the regression. What is the relationship between e1 and e2 so that linearization is accurate?

    Reply
  10. Hello, I am new in the field of statistics, I would like to ask how to calculate the growth rate with time series data.I wanna know the crop yield growth rate and calculate the instability in growth.
    I have this exponential equation:
    Y=ab^t
    Where Y=yield
    a=constant
    b=trend
    t=time(from 2000to 2014)
    I have the data of crop Yield for this period.
    The formula for instability index :CV*(1-R squared)square root

    Thanks, waiting your kind reply

    Reply
      • @Charles, Yes,I have tried many in this way:

        1)calculate ln y
        2)regress ln y x
        3) calculate exp(b)
        When I calculate the Compound Annual Growth rate from the value
        of b obtained(CAGR=(antilog b-1 )*100,i find the answer different when i use the common formula to calculate CAGR in excel.

        Reply
        • Tina,
          If all your data lies on the regression line then the CAGR should agree with the usual value. If not, then you could expect the CAGR not to be the same as that generated by the usual formula, although if the data lies close to the regression line, then the results should be similar.
          Charles

          Reply
  11. Hi,
    I am in a bit puzzled by following question. I need linear regression to model the following form: y = Ae^(-0.3t); as you can see that b is already known in this case. So, how to do this kind of regression in excel. Actually, I want to compare results with my hand calculations. I have done hand calculations using two methods:
    1. without data transformation
    2. with data transformation
    Now, using these two approaches, I get slightly different values for A. The value for A obtained by using model without data transformation gives a smaller sum of squared errors, as compared to using model with data transformation. I am puzzled by this variation as my understanding is that both systems should give same answers. Is my understanding correct? Am I missing something?

    Reply
  12. Q. what is the suitable statistical method to calculate and measure the time once we knew the value of y at t0 and value of y at tn ?
    In the graph below

    y

    t
    t0 tn
    tn-t0=?

    Q. what is the suitable statistical method to calculate and measure the time once we knew the value of y at t0 and value of y at tn ?

    I think we calculate the time using linear interpolation and certain numerical methods.
    In this case, I need to know the statistical method to estimate the time. And how?
    We can rewrite the question as follows:
    How long time the process takes from initial value y0 to end value yn ?

    Reply
    • Alan,
      The referenced webpage tells you how to perform exponential regression based on linear regression. If you look at the webpages on linear regression as well, you will see how to perform linear regression (and therefore exponential regression) by hand. This will not consist of a single formula.
      Charles

      Reply
  13. Hi,
    What do you suppose is the simplest way in which I could show and explain how the method of obtaining an exponential function via a linear model is valid to all exponential functions?
    I have already demonstrated that an exponential function (ab^x) can be found by first obtaining a linear function using using logs and graphing however need to further prove that it can be applied to all exponential functions.
    Would the use of natural log be useful for the (ae^kx)? If so how would I go about showing this with a given set of data? My first and second x and y values are (0,100) and (1,62).

    Reply
  14. I have further question after finding the intercepts.
    Using Y=a.exp(kt)
    In order to determine the value of t for given max/min value of Y (max = 100, min = 0 in my application.) wouldn’t the value of t be Inf. ?
    By subsitution, t = (ln(Y)-ln(a) )/ k

    Also , When the logarithmic distance between the 2 points to determine the constants is ‘more’ or ‘less’ , how would be the prediction of t ?

    Your comments would be useful. Thanks

    Reply
    • Pranav,
      Since t = (ln(Y)-ln(a))/ k, t would be undefined when y = 0, but would have a finite value when y = 100.
      Sorry, but I don’t understand your other question.
      Charles

      Reply
      • Sorry about not posing the question clearly.

        If we had to calculate the value of t at end positions(ex:1 and 100),
        how would the separation between the 2 points used to determine the values of a and k affect the value of t.

        Reply
  15. thanks for the post
    Y=αe^-βt

    can any one solve this example???
    i can fit into Y= α+βt
    but answer … EInY=-0.37182, E(InI)^2=3.45846, EtInI=-1.37554
    NOTE E MEANS SUM

    Reply
  16. Hello,

    Can you please explain to me how you would do the regression data analysis…? I want to know how to get the values in figure 2, Thank you!

    Reply
  17. Hi,
    Can you please elaborate, if we have an exponentially decaying function, when we need to/ we can use linear fiitting for interpolation instaed of using exponentially decaying curve?

    Reply
    • Nazia,
      As described on the referenced webpage, you can always model data that fits an exponential function using a linear model. The non-linear model (as described on the Real Statistics website) will be a little more accurate.
      Charles

      Reply
  18. Hi,
    I saw in some papers that the coefficients are interpreted as semi-elasticity without outlining the initial model. Does that mean that the model is in log-level form ?

    Reply
  19. When we use other functional forms to run the regression analysis, why do we then choose the functional form with the least MS residual to analyse the result?

    Reply
  20. Under what circumstances would it be appropriate to log transform only the independent variable for an exponential regression?

    Would that be considered a log-level or a different type?

    Reply
    • Moshe,

      To download the software, just go to the following webpage and click on the Free Download button (assuming that you are using Excel 2010, 2013 or 2016 for Windows)
      Real Statistics Resource Pack

      To install the software after you have downloaded it, perform the following steps:

      1. Open Excel, but don’t try to open the realstats.xlam file that you downloaded
      2. Press Alt-TI (i.e. hold the Alt key down and simultaneously press T followed by I)
      3. On the Add-Ins dialog box that appears press the Browse button and locate where you stored the realstats.xlam file that you downloaded (this done in a similar manner as when you Open a file)
      4. Once you have done this, make sure that the Realstats option on the Add-Ins dialog box is checked and click the OK button

      Reply
    • You can use log base 10 instead of the natural log, but the answers will differ by a constant factor since LN(x) = a Log(x) for any x where a = 2.302585…
      Charles

      Reply
    • Take the natural log of both sides of the equation and then use properties of logs and exp:

      ln y = ln(a exp^(-x/b))
      ln y = ln a + ln(exp^(-x/b))
      ln y = ln a – x/b
      x/b = ln a – ln y
      x = b ln (a/y)

      Charles

      Reply
  21. In order to run the regression why don’t you have to take the natural log form of both x and y before coming up with the equation? Then once you have it in the natural log form would you just take the equation and set it equal to marginal cost to find the profit maximizing quantity? Thank you.

    Reply
    • Alyssa,

      I have taken the natural log of both sides of the equation. Remember that LN(EXP(x)) is x.

      Regarding “…marginal cost to find the profit maximizing quantity”, you apparently have a particular application of exponential regression in mind, but exponential regression can be used to address a variety of applications, not just those from economics.

      Charles

      Reply
  22. Pls I need a solution to this problem where can I get semi-log regression and double log regression in SPSS or which software can I use to solve it

    Reply
    • Sorry, but I am not that familiar with SPSS. I do my statistical analysis using Excel and my software, the Real Statistics Resource Pack.
      Charles

      Reply
    • Taniya,
      Take any example you have for the chi-square test for independence (of two variables) and simply add another variable. Log-linear models analyze the resulting 3-way contingency tables.
      Charles

      Reply
  23. I would like to know about decline curves and rates.
    y = 2E+21e-0.002x

    I want to know decline rate is -0.02%? My problems are to know rates and -0.002.

    Reply
    • .002 is the exponential decay constant, which results in a decline rate of .2%.
      If this is not the answer to your question, please explain better.
      Charles

      Reply
  24. Exactly how do you arrive at your residual values? Can you please explicitly show us the calculation? As an example, the residual between the observed value x of 7.4 and the observed value y of 5.2.

    Reply
    • If you are modelling y = a*e^(bx), then the linear model is lny = bx + lna. The residual of the linear model is the difference between the observed value of lny and the predicted value of lny. For the model in Example 1 of the referenced webpage, when x = 7.4 then the model predicts y to to be 15.828 (although caution should be used since the value for x is a bit output the observed data range). Since you said that the observed value of y is 5.2, the observed value of ln y is ln 5.2 = 2.001 and the predicted value of ln y is ln 15.828 = 2.762, and so the residual is 2.001 – 2.762 = 0.761.

      Technically the residual for the exponential model is simply 5.2 – 15.828 = -10.628, although the value in the above paragraph may be more relevant for many purposes.

      Charles

      Reply
    • By “solve” do you mean put y=αxe^βx into the form Y = mX + C? This doesn’t seem likely since logy = βx + logα + logx takes the form Y = mX + logX + C.
      Charles

      Reply
  25. Hey, I have a doubt. If we have a data and we need to find the relation between them,we use correl to see if they have any linear relation between them. Likewise is there any counterpart for correl to see the exponential relation between the data. If we have a data,how can we come to a conclusion that they are exponentially related and then use logest or growth to predict the further values.please help me

    Reply
    • Petr,
      As described on the referenced webpage, if x and y have a exponential relationship, i.e. y = abe^x, then ln y = bx + ln a, which is a lineaar relationship. Thus you could use correl between ln y and x to test whether x and y have an exponential relationship.
      Charles

      Reply
  26. For exponential, logarithmic and power trend fits, Excel uses the least square method on the data pairs [x, ln(y)] (in the exponential case).

    From this approach inherit two issues:

    1) The R-squared given in charts is the one of the linear fit to those [x, ln(y)] pairs. NOT the R-squared of your original data! So do not rely on this value in the chart!
    This fact is documented somewhere in Excel … not too easy to find though.

    2) The overall approach will in general NOT deliver an optimal fit!!! I can provide examples, where the Excel trend (no matter if calculated as a chart trendline or by a worksheet function like GROWTH) is worse than an exponential fit calculated e.g. with an Levenberg-Marquardt algorithm.
    This fact is NOT documented in Excel – maybe not even known to Microsoft.
    On the contrary, Excel documentation states that its trends are “best fit”. They are not – always.

    So, if you want to be sure whether your data follow an exponential, logarithmic or power pattern: do not rely on Excel!

    Reply
    • Jorj,
      Sorry that I haven’t responded to your comment earlier, but I have been on vacation for the past few weeks. I appreciate your serious comment and plan to look into it. Based on your comments I may need to provide a new version of the referenced regression algorithms that provide a better fit along the lines that you have suggested.
      Charles

      Reply
      • Hi Charles.

        I just came across this thread and if you have already addressed this issue elsewhere, just ignore my post.

        We can view the exponential model as follows. Y has a log-normal distribution while Ln(Y) has a normal distribution. The relationship between the Normal and Log-normal distribution is well defined. For instance, if the variable Ln(Y) has a Normal distribution with mean = mu and standard deviation = sigma, then the variable Y has Log-normal distribution (with parameters mu and sigma). The mean of Y is given by e^(mu + 0.5*Sigma^2). It is NOT e^(mu). Similarly higher order moments can be defined (see Wikipedia Log-normal).

        The following notation is not exactly right, but I hope it conveys the message. Excel predicted values are off because of the fact that Excel trend line does prediction as e^(bo + b1X) (where b0 and b1 are the result of regressing Ln(Y) on X). But e^(bo + b1X) is not the expected value of Y given X. The correct expression for the expected value of Y given X should be e^(b0 + b1X + 0.5*MSE). So Excel will consistently under-estimate the expected value of Y given X (unless it is a perfect fit in which case MSE will be 0).

        This would be the solution for the Exponential model, but not necessarily for the other models. So Jorj is correct in that the Excel may not be the best approach for non-linear estimation. Most people assume that transformations are easy … they usually are not. And this is a case in point.

        Reply
        • Krish,
          Thanks for your comments. I had been reluctant to spend the time necessary to implement the Levenberg-Marquardt algorithm as suggested by Jorj, but I can see that it is not sufficient to simply accept the approach used by Excel. Shortly I will modify the website to at least comment on the discrepancy and try to come up with a compromise solution.
          Charles

          Reply
        • Krish,
          The latest release of the software, Release 3.8, provides a nonlinear regression solution to the exponential model.
          Charles

          Reply
  27. correction:

    I meant to say

    Is it correct to say that if x increases by delta_x units, y increases by 100*(eˆ(b1*delta_x)-1)%? (for b1 much larger that 0.1)

    Reply
    • Hi,
      Yes this is correct. I believe this is true for any value of b1, not just for b1 much larger than 0.1, using simple algebra.
      Charles

      Reply

Leave a Comment