Bass Model Functions in Excel Visual Basic

To use these functions, download the spreadsheet described at Which Bass Model Equations Should I Use? The Excel file also contains additional documentation, examples of function use and comparison of the three sets of Bass Model functions.

You may also copy the functions in the listing below to a Visual Basic module, which is where Excel stores macros. If you are not familiar with Excel VBA, you may want to scan a Web-based tutorial. Of course, these functions may also be used in other environments that host Visual Basic.

To use the functions, the security settings in Excel must be set so that macros will run. See Excel Help files for how to do this.

The functions are referenced as a part of a formula in a worksheet cell. The Excel formula bar is show here using the function Bass_cF with cell references to function parameters p, q and t.

Copy everything below this bar to a Visual Basic module. The formulae images will not paste into Visual Basic.


'---------------------------------------------------------
Option Explicit
'---------------------------------------------------------
' See
http://www.bassbasement.org/BassModel/WhichBassModelEquation.aspx
'
' for information about these functions.
'---------------------------------------------------------
'---------------------------------------------------------
' Bass functions must be used in pairs such that the
' cumulative function and the rate function are consistent.
' There are three such pairs presented here:
' 1. Bass_cF and Bass_SM_f
' 2. Bass_cF_asSumf and Bass_f
' 3. DE_cF_asSum_DE_f_tm1 and Bass_DE_f
' The first pair is preferred for parameter estimation and
' forecasting. See Srinivasan Mason 1986.
'---------------------------------------------------------
' 1. Bass_cF and Bass_SM_f
'---------------------------------------------------------
Function Bass_cF(p As Double, q As Double _
, t As Double) As Variant
' Bass CDF companion to PDF Bass_f.
' Bass cumulative distribution function.
' Big F is denoted here as cF because VB is not case sensitive.
' The continuous and discrete formulae are the same.
' This is the preferred discrete form for big F.
Dim a As Double, b As Double, x As Double, y As Double
If t < 0 Then
Bass_cF = 0
Else
a = q / p
b = p + q
x = Exp(-b * t)
y = 1 + a * x
Bass_cF = (1 - x) / y
End If
End Function
Function Bass_SM_f(p As Double, q As Double _
, t As Double) As Variant
' Bass PDF companion to CDF Bass_cF.
' Srinivasan Mason 1986 f of Bass probability density function.
' This is the preferred form.
' f is the difference F(t)-F(t-1),
' Use with the companion CDF Bass_cF.
If t = 1 Then
Bass_SM_f = Bass_cF(p, q, t)
Else
Bass_SM_f = Bass_cF(p, q, t) - Bass_cF(p, q, t - 1)
End If
End Function
'---------------------------------------------------------
' 2. Bass_cF_fsum and Bass_f
'---------------------------------------------------------
Function Bass_cF_fsum(p As Double, q As Double _
, t As Double) As Variant
' Bass CDF companion to PDF Bass_f.
' Not recommended for use in a discrete model
' except for exploration of Bass Model nuisances,
' This F form is not preferred. Use Bass_cF and Bass_SM_f.
' Here F is the sum of f(t) from t=0 to t.
Dim i As Double, cumF As Double
cumF = 0
For i = t - Fix(t) To t
cumF = cumF + Bass_f(p, q, i)
Next i
Bass_cF_fsum = cumF
End Function
Function Bass_f(p As Double, q As Double _
, t As Double) As Variant
' This is the continuous equation for f.
' Except for exploration of Bass Model nuisances,
' this form is not the preferred discrete form. See Bass_SM_f.
' Use with the companion cumulative function Bass_cF_asSumf.
Dim a As Double, b As Double, x As Double, y As Double
If t < 0 Then
Bass_f = 0
Else
a = q / p
b = p + q
x = Exp(-b * t)
y = 1 + a * x
Bass_f = ((b ^ 2) * x) / (p * (y ^ 2))
End If
End Function
'---------------------------------------------------------
' 3. Bass_DE_cF and Bass_DE_f
'---------------------------------------------------------
Function Bass_DE_cF(DE_cF_tm1 As Double, DE_f As Double) As Variant
' Bass CDF companion to PDF Bass_DE_f.
' This form of the Bass Model is supplied for
' exploration purposes only. It is not recommended
' for estimating parameters or forecasting.
' DE_cF_tm1 is Bass_DE_cF t - 1; that is,
' the value of this function at the interval before this.
' Parameter DE_f is the value of function DE_f
' at this time interval.
Bass_DE_cF = DE_cF_tm1 + DE_f
End Function
Function Bass_DE_f(p As Double, q As Double _
, cFtm1 As Double) As Variant
' Bass PDF companion to CDF Bass_DE_cF.
' Bass Model differential equation f.
' This form of is supplied for exploration purposes.
' It is not recommended for estimating parameters
' for forecasting. See Bass_SM_f.
' cFtm1 is CDF at t - 1; that is, the value
' of function DE_cF_asSum_DE_f_tm1 at the interval before this.
Bass_DE_f = p + (q - p) * cFtm1 - q * (cFtm1 ^ 2)
End Function
'---------------------------------------------------------
' Bass Model DE Alternate Form
'---------------------------------------------------------
Function Bass_DE_a(p As Double, q As Double, M As Double _
, cAtm1 As Double) As Variant
' equivalent to M * Bass_DE_f
' cAtm1 is cumulative adoptions at t - 1,
' which should be calculated in Excel formulae.
Bass_DE_a = p * M + (q - p) * cAtm1 - (q / M) * (cAtm1 ^ 2)
End Function
'---------------------------------------------------------
' Peak
'---------------------------------------------------------
Function Bass_peak_t(p As Double, q As Double) As Double
' Peak when Bass_f is used. Add 1 if using Bass_SM_f.
Dim a As Double, b As Double
a = q / p
b = p + q
Bass_peak_t = Log(a) / b ' natural log
End Function