Properties
Property 1: The maximum of the log-likelihood statistic for Poisson Regression occurs when the following k equations hold
Proof: Click here
Observation: Thus, to find the values of the bi coefficients we need to solve the above equations. We can do this iteratively using Newton’s method (see Definition 2 of Newton’s Method and Property 2 of Newton’s Method). As noted in Poisson Regression using Solver, the covariance matrix S for the coefficient estimates B is
where X is the n × k design matrix (whose rows are the Xi), μ is the n × 1 column vector of μi (scalar) values, and Z is the n × k matrix [zij] where zij = μi · xij.
Property 2: Let B = [bj] be the k × 1 column vector of Poisson regression coefficients, let Y = [yi] be the n × 1 column vector of observed outcomes of the dependent variable, let X be the n × k design matrix (see Definition 3 of Least Squares Method for Multiple Regression), let P = [pi] be the n × 1 column vector of predicted values of the dependent variable, and V = [vij] be the n × n diagonal matrix where vij = zij on the main diagonal and zeros elsewhere. Then if B0 is an initial guess of B and for all m we define the following iteration
then for m sufficiently large, B ≈ Bm, and so Bm is a reasonable estimate of the coefficient vector.
Proof: Click here
Worksheet Functions
Real Statistics Functions: The Real Statistics Resource Pack provides the following worksheet array functions. These pertain to a Poisson regression model based on X data in Rx (with k columns), Y data in Ry, and frequency data in Rt. If Rt is omitted it defaults to a column of ones.
PoissonCoeff(Rx, Ry, lab, phi, Rt, head, alpha, iter, guess): a k+1 × 6 array containing the estimated regression coefficients, standard errors, Wald statistic, p-value, and the lower and upper ends of the 1 – alpha confidence interval; if head = TRUE (default FALSE) then the first row of Rx, Ry, and Rt are column headers; if phi = TRUE (default FALSE) then the phi correction is applied to the standard errors.
When lab = TRUE, then an extra column is appended to the output containing labels. alpha is the significance level (default .05). iter is the number of iterations used in calculating the coefficients using Newton’s method (default 20). guess is the initial guess of these coefficients (if missing then all the coefficients are initially set to one).
PoissonCov(Rx, Ry, Rt, iter, guess): a k+1 × k+1 array containing the coefficient covariance matrix, where iter and guess are as above.
Examples
For Example 1 of Poisson Regression using Solver, you can use the array function
=PoissonCoeff(G3:I18,J3:J18,TRUE,,K3:K18,TRUE)
to obtain the results shown in range P22:V26 of Figure 6 of Poisson Regression using Solver. To obtain the output shown in P28:V32 of Figure 7 of Poisson Regression using Solver, you can use the array function
=PoissonCoeff(G3:I18,J3:J18,TRUE,TRUE,K3:K18,TRUE)
You can use the array formula
=PoissonCov(G3:I18,J3:J18,K3:K18)
to obtain the covariance matrix shown in Figure 5 of Poisson Regression using Solver.
Examples Workbook
Click here to download the Excel workbook with the examples described on this webpage.
References
Hintze, J. L. (2007) Poisson regression. NCSS
https://www.ncss.com/wp-content/themes/ncss/pdf/Procedures/NCSS/Poisson_Regression.pdf
Nussbaum, E. M., Elsadat, S., Khago, A. H. (2007) Best practices in evaluating count data, Chapter 21: Poisson regression.
http://www.academia.edu/438746
Penn State (2017) Poisson regression. STAT 504: Analysis of discrete data.
https://online.stat.psu.edu/stat504/lesson/9