Research Article - (2012) Volume 0, Issue 0

A Useful Microsoft Excel Add-in Program for Pharmacokinetic Analysis

Baojian Wu* and Ming Hu
Department of Pharmacological and Pharmaceutical Sciences, College of Pharmacy, University of Houston, 1441 Moursund Street, Houston, TX 77030, USA
*Corresponding Author: Baojian Wu, 1441 Moursund Street, Department of Pharmacological and Pharmaceutical Sciences, College of Pharmacy, University of Houston, Houston, TX77030, USA, Tel: (832)-531-1134 Email:


The aim of this study is to explore the possibility and versatility of Microsoft Excel in handling pharmacokinetic analysis (including model composing, simulation, and parameter estimation). We have developed the first version of an in-house pharmacokinetic tool XlSimEst, coded in Visual Basic for Application (VBA), which can be used for pharmacokinetic modeling and simulation with user-tailored models consisting of a system of differential equations. The special features of the program are: (1) pharmacokinetic models can be open and saved as ordinary text files, thus the model files can be created and edited using any text editor; (2) a quicker interface to perform simulation studies; (3) capable of a standard error estimation for fitted parameters; and (4) a simple interface for generating concentration-time plots.

Keywords: Pharmacokinetic Modeling; Excel; Free program; Differential equation; XlSimEst


As an indispensible tool in drug research, pharmacokinetic modeling provides substantial insights into the underlying mechanisms with respect to drug absorption, distribution, metabolism and excretion (ADME) in humans and other animal species. The derived key parameters such as the elimination rate constant (K), volume of distribution (V) and clearance (CL) are frequently used to guide preclinical and clinical dose regimens. Furthermore, simulations based on an established pharmacokinetic model can be used to identify a rate-determining step in drug clearance (a procedure called “sensitivity analysis”), and to predict therapeutic events associated with changes in this process [1].

Mathematically, simple pharmacokinetic models can be described in an algebraic form (i.e., non-linear regression equations) or be solved by linearization methods such as Laplace transforms. However, solving complex systems using Laplace transforms tends to be tedious and inefficient. In particular, complex pharmacokinetic models involving saturable processes (e.g., metabolism and carrier-mediated transport) cannot be linearized with an analytic form, which thus require a numeric solution. As mentioned by Meineke and Brockmöller [2], coupled differential equations represent a universal approach to describe pharmacokinetic models in a more direct and effective manner.

Microsoft (MS) Excel® spreadsheet has become the standard platform for data collection, graphing and analysis, and majority of original data are kept as MS Excel files in pharmaceutical field. The advantages of Excel include (1) the user-friendly interface and ease of use; (2) it offers many built-in mathematical and graphical routines which can be called in user-defined functions; and (3) it provides tremendous customization through macros and Visual Basic add-ins, for those users with specific needs and programming experience. By far, numerous Excel-based spreadsheet templates and add-ins have been widely programmed for data analyses in pharmaceutical sciences [4-6], including pharmacokinetics [2,3,7-10], suggesting the unmet needs regarding pharmacokinetics analysis with current programs. Among those works, PKsolver [3] and pk-engine [2] represent two exemplary Excel add-in programs for pharmacokinetic analysis. Although PKsolver automates pharmacokinetic analysis with a number of buildin non-linear regression models, it neither accepts user-defined models nor approximates standard errors for fitted parameters. Pk-engine is designed to simulate complex pharmacokinetic models. However, pharmacokinetic modeling (parameter estimation) is not considered in this program.

The goal of our study therefore is to provide an accurate and free program that streamlines the pharmacokinetic model composing, simulation and parameter estimation in MS Excel. In this paper, we present the first version of our add-in program XlSimEst. This program features in (1) the ease of model preparation (i.e., writing of the model), even for non-professionals, (2) a quicker interface to generate concentration-time profiles, and (3) being capable of a standard error approximation for modeled parameters. In addition, a couple of data- based examples were used to qualify the program and the results were compared to those derived from professional software packages (i.e., WinNonlin and ADPAT II).


Pharmacokinetic modeling

Pharmacokinetic models here are specified as a set of differential equations (Figure 1). The Runge-Kutta method (fourth order with variable step size) is implemented to numerically solve the differential equations [11]. In an earlier study [12], this numerical integration technique was used to develop physiologically based pharmacokinetic models yielding results remarkably consistent with those from more sophisticated computer software packages.

The add-in program “XlSimEst” appears as four buttons (command bars) in the region [Custom Toolbars] under Add-Ins tab in MS Excel after a quick installation, as illustrated in Figure 2. A flowchart of pharmacokinetic modeling using XlSimEst is shown in Figure 3. By clicking [XlSimEst] button in the command bar, users will be prompted to specify a desired application (either simulation or parameter estimation), and to input the model (Figure 4). Pharmacokinetic models can be written using this interface consisting of three folder tabs, namely, “Initialization”, “State variables” and “Parameters”. “Initialization” is used to enter the values for time interval and final time. “State variables” are variables that are defined with derivatives (e.g., drug/metabolite concentration or amount) (Figure 4B). “Parameters” define the model structure and are the numbers that will be varied per simulation to observe its effects on the drug disposition processes (e.g., the first-order rate constants) (Figure 4C). “State variables” are enclosed by square brackets to distinguish them from “Parameters”. After completion of the model specification, it is encouraged to save the model (as .txt file) for future use by clicking “SAVE”. Alternatively, one can use text editor (e.g., Notepad) to compose the desired model, and load it by clicking “OPEN” (Figure 5).

An Excel spreadsheet named “Results” (by default) with the required inputs and formulas (including those for Runge-Kutta numerical integration) in the appropriate cells will be yield after clicking on the “OK” button (Figure 6). The scrollbars in the spreadsheet can be used to change the parameter values for the model parameters and the initial values (defining initial condition) for the state variables. The formula links to the scrollbars can be recovered by clicking on the [parScroll] command (Figure 2), in case that they are disabled. The maximum and minimum values attached to the scroll bars can also be changed manually in the spreadsheet. Fitting of the model to actual data can be visually inspected by plotting both simulated and observed values, which can be accessed by [Graph] button (Figure 7A) with “included data points” option checked. By examining how well the model approximates the data with changes in parameter values, the program provides an efficient way to obtain more reasonable initial parameter values for next parameter estimation.

Parameter optimization is performed using Excel SOLVER, which is based on the robust and reliable generalized reduced gradient method (GRG) [13,14]. The optimization algorithm requires the preset initial parameter values to start an iterative process. The iterative calculation stops and provides the solutions, when the target (i.e., weighted sum squares of residuals) converges at a predefined value. A detailed illustration of this SOLVER implementation in Excel is reported previously [6,15]. For the ease of use, optimization for selected parameters (either parameters or initial conditions for state variables) is performed via an interactive Excel UserForm by clicking on [XlEst(Solver)] command bar, where both parameter inputs and modeling settings are specified (Figure 7B). Users are allowed to use different weighting schemes (1, 1/y and 1/y2) and adjust the SOLVER features such as minimization method, precision, convergence and iterations (Figure 7B). The parameter estimation is started instantaneously by clicking the “Run” button. The massive calculations then proceed in the current sheet “Result”, where the estimated parameters, their respective standard errors, and model diagnostics will be presented (Figure 8). In order to facilitate a quick experience and learning of the program, sample data are included, which can be invoked simply by clicking the “Sample” button (Figure 4A). Additionally, example model files used in this paper can be found in supplemental files (User Manual).

One dataset (i.e., the abovementioned sample data) from a published book [16] was used to evaluate XlSimEst. This same set of concentration–time data were fitted with one-, two-, and threecompartment IV bolus models, respectively (Figure 1). Table 1 summarizes the modeling results using XlSimEst and two commercial softwares (WinNonlin and ADAPT II). All parameters and their standard errors obtained with XlSimEst are similar to those calculated with WinNonlin and ADAPT II. Moreover, the diagnostic parameters are nearly identical, which suggests that the results of XlSimEst are acceptable.

  One-compartment model Two-compartment model Three-compartment model
Parameters XlSimEst*# WinNonlin ADAPT II* XlSimEst*# WinNonlin ADAPT II* XlSimEst*# WinNonlin ADAPT II*
K10 0.0079 ± 0.0011 0.0079 ± 0.0011 0.0079 ± 0.0011 0.0071 ± 0.0004 0.0071 ± 0.0004 0.0071 ± 0.0004 0.0067 ± 0.0010 0.0067 ± 0.0009 0.0067 ± 0.0009
K12       0.0218 ± 0.0021 0.0219 ± 0.0021 0.0219 ± 0.0021 0.0194 ± 0.0061 0.0195 ± 0.0063 0.0196 ± 0.0062
K21       0.0222 ± 0.0028 0.0222 ± 0.0028 0.0222 ± 0.0028 0.0632 ± 0.0436 0.0632 ± 0.0457 0.0635 ± 0.0457
K13             0.0121 ± 0.0073 0.0121 ± 0.0073 0.0123 ± 0.0073
K31             0.0117 ± 0.0070 0.0117 ± 0.0070 0.0118 ± 0.0070
C0 1.4028 ± 0.0827 1.4027 ± 0.0827 1.4027 ± 0.0827 1.8408 ± 0.0412 1.8402 ± 0.0412 1.8402 ± 0.0412 1.9271 ± 0.0754 1.9307 ± 0.0778 1.9311 ± 0.0769
R2 0.9135 0.9135 0.9135 0.9974 0.9974 0.9974 0.9985 0.9985 0.9985
SS 0.2598 0.2598 0.2598 0.0065 0.0065 0.0065 0.0037 0.0037 0.0037
SE 0.1471 0.1471 0.1471 0.0255 0.0255 0.0255 0.0214 0.0214 0.0214
AIC -14.87 -14.87 -14.87 -62.53 -62.53 -62.53 -66.47 -66.47 -66.47
SC -13.59 -13.59 -13.59 -59.98 -59.98 -59.98 -62.64 -62.64 -62.64

*Initial values were arbitrary assigned: K10 = K12 = K21 = K13 = K31 = 0.01; C0=1. Weighting scheme (W=1) was used in all cases. # step size of 5 (mins) was used for numerical integration.

Table 1: Detailed comparison of XlSimEst results with those of WinNonlin 3.3 and ADAPT II.

Model discrimination

For model selection, XlSimEst provides statistics for goodness-offit assessment such as determination of coefficient (R2), weighted sum squares of residuals (SS), standard error of weighted residuals (SE), Akaike’s information criterion (AIC) and Schwarz criterion (SC). Among those statistics, AIC and SC include a penalty term for the number of modeled parameters [17], and are computed the same way as described earlier [3,6].

Standard error of the estimated parameters

Although being capable of estimating the parameters, SOLVER does not approximate the standard errors of the estimated parameters. A special macro therefore is encoded in XlSimEst to provide the standard errors with respective to the parameters resulted from SOLVER.

The standard error of the parameter a1 is given by [18]


where Pii -1 is the ith diagonal element of the inverse of the Pij matrix, SE is standard error of weighted residuals.


N is the number of observations. δFn/δai is the partial derivative of the function with respective to ai evaluated at xn (x, independent variables).


The XlSimEst package is available in Supplementary Materials. The package contains the XlSimEst program file and a complete User Manual. Interested users can download and install it for free. The package was developed and tested in Excel 2010 under Microsoft Windows XP service pack 2. The program is also successfully run in Excel 2007. Other Excel (e.g., Excel 2003) or Windows versions are not tested, thus their compatibility with XlSimEst is not guaranteed. We are looking forward to receiving any suggestions from our program users.


Since parameter optimization in XlSimEst essentially is an MS Excel SOLVER implementation, all limitations of SOLVER is also applied to this add-in program. For example, (1) the greater the number of parameters in the model, the longer the program will take; (2) the number of observations in total cannot exceed the limit of 200. It is also noted that the program is sensitive to given initial parameter value. If the initial parameters are inappropriate, SOLVER either proceeds in the wrong direction and a solution cannot found, or converges at a local minimum and provides a biased solution.


This study presents XlSimEst, a free MS Excel add-in program, which aims to streamline pharmacokinetic model composing, simulation and parameter estimation. As scientists are more educated in application of MS Excel, the add-in program admittedly is much user-friendly. Although the program is satisfactory regarding the estimated parameters and their respective standard errors for a limited number of example runs, further testing is required for additional models and datasets.


It is gratefully acknowledged that configuration of spreadsheet for result output and Excel UserForms in this work had been adapted from the project of RK4 by Dr. Co, which is an outstanding exemplary of implementing Runge-Kutta method in MS Excel. The author also thanks reviewers for valuable suggestions and comments.


Citation: Wu B, Hu M (2011) A Useful Microsoft Excel Add-in Program for Pharmacokinetic Analysis. Pharm Anal Acta S11:002.

Copyright: © 2011 Wu B, et al. This is an open-access article distributed under the terms of the Creative Commons Attribution License, which permits unrestricted use, distribution, and reproduction in any medium, provided the original author and source are credited.