Development of an NetPV engine
In this module we develop a variety of corrections to the Excel NPV function, with three different routines. Each routine includes the cash flow component at time zero:-
- The VBA NPV function, with cash flows from a range object which is assigned to an array
- The WS NPV function with cash flows in a range object
- NPV using first principles with a For...Next loop to read the range object values
The procedures are constructed as possible alternate routines for the analysis engine behind an NPV Estimator UserForm interface described in section 1.2.
1. Preliminary
It is well known that the Excel NPV function returns the present value of FUTURE cash flows, and ignores the cash flow component at time zero. In financial modeling, the net present value (NPV) of a project or investment is given by $$\begin{equation} NPV=C_0 + \sum_{t=1}^{n} \frac{C_t}{(1+k)^t} \end{equation}$$ where \(C_0\) is the initial cash flow at time zero. The future cash flows at time \(t\) are denoted \(C_t\) over \(n\) periods. \(k\) is the periodic discount rate.
1.1 The NPV example
Example: ABC Farming Co is considering an investment of $80,000 in a new carrot washing machine, that is expected to generate the following incremental cash inflows: $10,000 (year 1), $20,000 (year 2), $15,000 (year 3), $50,000 (year 4), and $20,000 (year 5). Assume that all cash inflows occur at the end of the year. The discount rate is 5.0% pa. [Source: NPV example.]
Required: Calculate the net present value of the project using equation 1.
Mathematically, the answer to the cash flow example is: $$ NPV=-80,000 + \frac{10,000}{(1+0.05)^{1}} + \frac{20,000}{(1+0.05)^{2}}+ \frac{15,000}{(1+0.05)^{3}}+ \frac{50,000}{(1+0.05)^{4}}+ \frac{20,000}{(1+0.05)^{5}} = \$17,428 $$
The Excel NPV function ignores the \(C_0\) component, so equation 1 can be rewritten as \(NPV_{xl}\) -
$$\begin{equation} NPV_{\text{xl}}= \sum_{t=1}^{n} \frac{C_t}{(1+k)^t} \end{equation}$$
The NetPV engine: A financial modeling team is developing a demonstration program to return the actual net present value of cash flows with incorporation of the initial cash flow of the project.
The financial model involves both a user interface (section 1.2), and a procedure (analytical engine, described in section 2) to calculate the return value. This module addresses the second part.
1.2 The NetPV interface
The UserForm in figure 1 has two Parameters: Discount rate (unlabeled with value 5%), and Cash Flow range (range name CF).
The sample cash flows worksheet (figure 2) has an m x 1 vector of cash flows (m = 6) with labels one column left. When the cash flow vector is set to the Selection, the name CF appears in the Name Box. Code 6 (described in section 3.1) can be used to establish the CF vector and labels.
2. Calculating NetPV
Three methods are demonstrated. 1. The VBA NPV function; 2. The Excel NPV function, and 3. NPV from first principles.
2.0 Module Declaration
Code 1: Module level Declaration
Option Explicit Option Private Module ' Start edit == Const Rte As Double = 0.05 Const Rng As String = "CF" ' the "Cash Flow range" TextBox / RefEdit control returns a string Const CFvals As String = "-80000,10000,20000,15000,50000,20000" ' End edit ====
About Code 1
- Line 2: Option Private Module - all procedures in the module are private and will not appear on the Excel Macro list, or Function list
- Line 4 to 8: Edit segment - the UserForm is not required in this development phase, so values from the UserForm interface are grouped in the declarations segment to provide ease with future editing
- Line 7: Const CFvals - represents assignment of the sample cash flow values from figure 1. It is not possible to declare an array constant, so the cash flow vector is written as a string constant, then assigned to an array in a later procedure (code 5 line 84), using the VBA Split function
2.1 The VBA NPV function
SYNTAX: NPV function (VBA)
VBA.NPV(Rate As Double, ValueArray() as Double) As Double The function calculates the net present value (NPV) of a project from a series of future cash flows (ValueArray) and a discount rate (Rate)
The VBA NPV function requires that the cash flow range object must be assigned to a VBA array of type double. Types such as Variant or even Single will return a Type Mismatch error.
Code 2: Sub procedure NetPVv - NetPV using the vBA NPV function
Sub NetPVv(Disc As Double, CFlow As Range) Dim CFarray() As Double, CF0 As Double, Tmp As Double Dim m As Integer, i As Integer m = CFlow.Rows.Count ReDim CFarray(1 To m - 1) CF0 = CFlow(1, 1).Value For i = 1 To m - 1 CFarray(i) = CFlow(i + 1, 1).Value Next i Tmp = VBA.NPV(Disc, CFarray) + CF0 DBPrint (Tmp) End Sub Sub RunNetPVv() Call NetPVv(0.05, Range(Rng)) End Sub
About Code 2
- Line 10: Procedure declaration - two compulsory arguments including CFlow as type Range. Note: when an object is included in the procedure declaration the Set statement is not required.
- Line 11: Dynamic array - the cash flow array, named CFarray is a dynamic array as indicated by the empty (). The array is given a dimension in line 15
- Line 16: CF0 - assign the first cell of
Cflow([RowIndex],[ColumnIndex])
CFlow(1, 1).Value returns -80000 to the CF0 variable - Line 19: CFarrray(i) - assign the remaining cells of
Cflow([RowIndex],[ColumnIndex])
rows 2 to 6 to the CFarray index 1 to 5. The values of CFarray Type Double(1 To 5) are listed in Locals Window in figure 3
- Line 22: NetPV - call the VBA NPV function and pass arguments Disc, and CFarray (with elements 1 to 5) plus CF0
- Line 23: DBPrint (described in code 6) - print the NetPV value to the Immediate Window with time stamp (figure 6)
- Lines 27 to 29: RunNetPV - this procedure is used to pass the arguments to the NetPVv procedure
2.2 The Excel NPV function
SYNTAX: NPV function (WS)
NPV(rate,value1,[value2],...) Application.Npv(Arg1 As Double, Arg2, [Arg3], [Arg4],..., [Arg30]) As Double The function calculates the net present value (NPV) of a project from a series of future cash flows (Arg2,...,Arg30) and a discount rate (Arg1)
The WS NPV function cash flows can be individual elements or a Range object
Code 3: Sub procedure NetPVx - NetPV using the Excel NPV function
Sub NetPVx(Disc As Double, CFlow As Range) Dim CF0 As Double Dim Tmp As Double Dim WSF As WorksheetFunction: Set WSF = WorksheetFunction Dim m As Integer m = CFlow.Rows.Count CF0 = CFlow(1, 1).Value Tmp = WSF.NPV(Disc, CFlow.Range(Cells(2, 1), Cells(m, 1))) + CF0 DBPrint (Tmp) End Sub Sub RunNetPVx() Call NetPVx(0.05, Range(Rng)) End Sub
About Code 3
- Line 43: WSF - declare WSF as a WorksheetFunction object, then use the Set statement to assign the object to WSF. WSF is used in line 49
The cash flow values are located in the CFlow range object (declared in line 40)
To view the values, expand the CFlow > Value2 expression in the Locals Window (figure 4) - Value2 is an array of Variants with Type Variant/Variant(1 to 6, 1 to 1)
Each row (dimension 1) of Values2 contains another Variant array for the cells in the Range row
CF0 can also be addressed by CFlow.Value2(1, 1). This is a Variant with sub type Double. Variant/Double in the Type column
2.3 NPV from the algebraic formula
In the algebraic form, equation 1 is re-expressed as -
$$\begin{equation} NPV_{\text{loop}}= \sum_{t=0}^{n} \frac{C_t}{(1+k)^t} \end{equation}$$
to sum all of the discounted cash flow, as the main statement in a VBA For...Next loop. See code 4. This is also describes as NPV from first principles.
Code 4: Sub procedure NetPVfn - NetPV with a for...next loop
Sub NetPVfn(Disc As Double, CFlow As Range) Dim Tmp#, i% For i = 1 To CFlow.Rows.Count Tmp = Tmp + CFlow(i, 1) * (1 + Disc) ^ -(i - 1) Next i DBPrint (Tmp) End Sub Sub RunNetPVfn() Call NetPVfn(0.05, Range(Rng)) End Sub
About Code 4
- Line 61: Dim - statement using type declaration characters (TDC's), # for Double, and % for Integer
- Line 64: NetPV - accumulate the values in Tmp with reference to all of the elements of
Cflow([RowIndex],[ColumnIndex])
rows 1 to 6 in CFlow(1)...(6) - see figure 4
3. Utility procedures
Two utility programs are described. SetCF is used to set up the cash flow vector in a worksheet, and DBPrint as a short routine to call the Debug.Print statement.
3.1 Setting the Cash Flow vector
Code 5: Sub procedure SetCF - set Cash Flow vector
Sub SetCF() Dim C As Variant Dim i As Integer, rv As Integer C = Split(CFvals, ",") On Error Resume Next If ActiveSheet.Name(Rng) = Rng Then rv = MsgBox(Prompt:="The " & Rng & " name already exists" & vbNewLine & _ "Press OK to Overwrite or Relocate at ActiveCell", _ Buttons:=vbOKCancel + vbInformation, _ Title:="Set " & Rng & " sample data series") If rv = vbCancel Then Exit Sub End If End If With ActiveCell .Resize(UBound(C) + 1, 1).Offset(0, 1).Name = Rng For i = 0 To 5 .Offset(i, 0) = Rng & i .Offset(i, 1) = C(i) .Offset(i, 1).NumberFormat = "#,##0" Next i End With End Sub
About Code 5
- Line 84: use the VBA Split function to the convert the CFvals string, declared at the module level, to elements of the C variant array, figure 5
3.2 Printing the results
Code 6: Sub procedure DBPrint - Debug.Print routine
Sub DBPrint(Msg As String) Debug.Print "Time: " & Time & " ===" & vbNewLine & _ "NPV: " & Format(Msg, "Currency") & vbNewLine End Sub
About Code 6
- Lines 111 and 112: Debug.Print concatenates (using the & operator) a series of text strings with the VBA Time function (returns the current time in Windows "Long time" format) and the NPV number as a currency value (Windows "Currency" format). Output is shown in figure 6
References
Walkenbach J, (2015), Microsoft Excel 2016 Bible: The Comprehensive Tutorial Resource, Wiley. (gp. Google Play page numbers)
- Related material: Excel NPV example - ABC Farming Co
- Development platform: Excel 2016 (64 bit) Office 365 ProPlus on Windows 10
- Revised: Saturday 25th of February 2023 - 10:12 AM, [Australian Eastern Time (AET)]