XLeratorDLL vs WorksheetFunction: XNPV
Aug
21
Written by:
Charles Flock
8/21/2016 3:30 PM
In this article we compare the XLeratorDLL/financial XNPV function to WorksheetFunction XNPV where we encounter some aspects of the WorksheetFunction that might give you pause.
The calculation of net present value is one of the most basic concepts in finance allowing you to compare the value of a cash today against cash a year (for example) from now. This concept, that cash flows can be equated across a time dimension, is the backbone of all time-value of money calculations. Let’s compare the WorksheetFunction XNPV method to the XLeratorDLL/financial XNPV function.
I have the following statements at the top of my code.
Option Explicit On
Option Strict On
Imports System.Runtime.InteropServices
Imports System.Text
Imports Excel = Microsoft.Office.Interop.Excel
Imports wct = XLeratorDLL_financial.XLeratorDLL_financial
Then I create a very simple form which consists of a single button and a text box where the results are displayed.
I then create a Class for XLDLL functions:
Public Class test_XLDLL
End Class
and another Class for Excel functions:
Public Class test_XL
End Class
Let's look at how the XNPV function works in XLDLL.
Public Shared Sub testXNPV()
Dim rate As Double
Dim cf As Double()
Dim cfdate As Date()
Dim result As Double
rate = 0.06
cf = New Double() {-100000, -50000, -2500, 12500, 37500, 75000, 90000}
cfdate = New Date() {#11/30/2011#, #3/15/2012#, #7/18/2012#, #11/30/2012#, #1/23/2013#, #4/30/2013#, #2/6/2014#}
result = wct.XNPV(rate, cf, cfdate)
Form1.TextBox1.AppendText("XLDLL XNPV: " & result.ToString("F4") & vbCrLf)
End Sub
You would expect the code for the Excel function to be pretty much the same so this is how I originally coded it.
Public Shared Sub testXNPV()
Dim XL As New Excel.Application
Dim rate As Double
Dim cf As Double()
Dim cfdate As Date()
Dim result As Double
rate = 0.06
cf = New Double() {-100000, -50000, -2500, 12500, 37500, 75000, 90000}
cfdate = New Date() {#11/30/2011#, #3/15/2012#, #7/18/2012#, #11/30/2012#, #1/23/2013#, #4/30/2013#, #2/6/2014#}
result = XL.WorksheetFunction.Xnpv(rate, cf, cfdate)
Form1.TextBox1.AppendText("Excel XNPV: " & result.ToString("F4") & vbCrLf)
XL.Quit()
End Sub
But that code won't run because the XNPV WorksheetFunction method only supports 2 input parameters, not the three that are required. The Microsoft documentation actually acknowledges this:
The Xnpv method does not provide a parameter that corresponds to the rate argument required by the corresponding XNPV function (=XNPV(rate, values, dates)). To work around this limitation in VBA code, instead of using the Xpnv method, call the XNPV function by using the Evaluate(Object) method.
Let's do that.
Public Shared Sub testXNPV()
Dim XL As New Excel.Application
Dim rate As Double
Dim cf As Double()
Dim cfdate As Date()
Dim result As Double
Dim strXNPV As New StringBuilder
rate = 0.06
cf = New Double() {-100000, -50000, -2500, 12500, 37500, 75000, 90000}
cfdate = New Date() {#11/30/2011#, #3/15/2012#, #7/18/2012#, #11/30/2012#, #1/23/2013#, #4/30/2013#, #2/6/2014#}
'result = XL.WorksheetFunction.Xnpv(rate, cf, cfdate)
strXNPV.Clear()
strXNPV.Append("XNPV(")
strXNPV.Append(rate.ToString & ",{")
For i As Integer = 0 To cf.GetUpperBound(0)
strXNPV.Append(cf(i).ToString & If(i < cf.GetUpperBound(0), ",", "}"))
Next
strXNPV.Append(",{")
For i As Integer = 0 To cfdate.GetUpperBound(0)
strXNPV.Append(cfdate(i).ToOADate.ToString & If(i < cf.GetUpperBound(0), ",", "}"))
Next
strXNPV.Append(")")
result = Convert.ToDouble(XL.Evaluate(strXNPV.ToString))
Form1.TextBox1.AppendText("Excel XNPV: " & result.ToString("F4") & vbCrLf)
XL.Quit()
End Sub
I add the following code to Button1_Click to produce the results of the comparison.
Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Run.Click
TextBox1.Clear()
test_XL.testXNPV()
test_XLDLL.testXNPV()
End Sub
When I run the code I get the following result.
As you can see the results were identical, though it was much easier to code using XLDLL. As you will see in the following examples, using the WorksheetFunction method can actually get quite a bit more complicated.
In this example, we are going to randomly create 100 cash flows and calculate the net present value. Here's the code for XLDLL.
Public Shared Sub testXNPV()
Dim rate As Double
Dim cf As Double()
Dim cfdate As Date()
Dim result As Double
Dim r1 As New System.Random
Dim r2 As New System.Random
rate = 0.06
ReDim cf(99)
ReDim cfdate(99)
For i As Integer = 0 To 99
cf(i) = Math.Round(-50000 + r1.NextDouble * 100000, 2)
cfdate(i) = Today.AddDays(r2.Next(1, 3650))
Next
result = wct.XNPV(rate, cf, cfdate)
Form1.TextBox1.AppendText("XLDLL XNPV: " & result.ToString("F4") & vbCrLf)
End Sub
And here's the code for the WorksheetFunction.
Public Shared Sub testXNPV()
Dim XL As New Excel.Application
Dim rate As Double
Dim cf As Double()
Dim cfdate As Date()
Dim result As Double
Dim strXNPV As New StringBuilder
Dim r1 As New System.Random
Dim r2 As New System.Random
rate = 0.06
ReDim cf(99)
ReDim cfdate(99)
For i As Integer = 0 To 99
cf(i) = Math.Round(-50000 + r1.NextDouble * 100000, 2)
cfdate(i) = Today.AddDays(r2.Next(1, 3650))
Next
strXNPV.Clear()
strXNPV.Append("XNPV(")
strXNPV.Append(rate.ToString & ",{")
For i As Integer = 0 To cf.GetUpperBound(0)
strXNPV.Append(cf(i).ToString & If(i < cf.GetUpperBound(0), ",", "}"))
Next
strXNPV.Append(",{")
For i As Integer = 0 To cfdate.GetUpperBound(0)
strXNPV.Append(cfdate(i).ToOADate.ToString & If(i < cf.GetUpperBound(0), ",", "}"))
Next
strXNPV.Append(")")
result = Convert.ToDouble(XL.Evaluate(strXNPV.ToString))
Form1.TextBox1.AppendText("Excel XNPV: " & result.ToString("F4") & vbCrLf)
XL.Quit()
End Sub
Chances are, however, when you run test_XL.testXNPV() you will get an answer like -2146826273.0000.
What's up with the Excel calculation? After a little bit of digging it turns out that that -2146826273 is actually equivalent to #VALUE! in the COM world.
This result arises for 2 reasons. First, the Excel function actually requires that the first cash flow contain the minimum date value for the set. The remaining cash flows can be in any order, but the first one must be earliest one. While you could write a routine to traverse the dataset and swap out the cash flows it is in fact much simpler to simply sort them.
Second, it turns out that the Evaluate function will not accept a string longer than 256 characters (mind-boggling, I know) which limits our string to about 15 cash flows. To overcome this limitation, we are going to have to actually move data into the worksheet object and then evaluate the function based on the values in the worksheet object. This is way more complicated than just using the XLDLL function.
Finally, our code now needs to handle COM errors and to sort the cash flows. The following code does this, and now an answer is returned.
Public Shared Sub testXNPV()
Dim XL As New Excel.Application
Dim rate As Double
Dim cf As Double()
Dim cfdate As Date()
Dim result As Double
Dim strXNPV As New StringBuilder
Dim r1 As New System.Random
Dim r2 As New System.Random
Dim wkb As Excel.Workbook = XL.Workbooks.Add
Dim wks As New Excel.Worksheet
Dim rng As Excel.Range
rate = 0.06
ReDim cf(99)
ReDim cfdate(99)
For i As Integer = 0 To 99
cf(i) = Math.Round(-50000 + r1.NextDouble * 100000, 2)
cfdate(i) = Today.AddDays(r2.Next(1, 3650))
Next
Array.Sort(cfdate, cf)
wks = DirectCast(wkb.Sheets(1), Excel.Worksheet)
For i As Integer = 0 To 99
wks.Cells(i + 1, 1) = cf(i)
wks.Cells(i + 1, 2) = cfdate(i)
Next
wks.Cells(101, 2) = "=XNPV(" & rate.ToString & "," & "A1:A" & (cf.GetUpperBound(0) + 1).ToString & "," & "B1:B" & (cf.GetUpperBound(0) + 1).ToString & ")"
rng = DirectCast(wks.Cells(101, 2), Excel.Range)
result = DirectCast(rng.Value, Double)
Select Case result
Case -2146826281 '#DIV/0!
result = Double.NaN
Case -2146826246 '#N/A!
result = Double.NaN
Case -2146826259 '#NAME!
result = Double.NaN
Case -2146826288 '#NULL
result = Double.NaN
Case -2146826252 '#NUM!
result = Double.NaN
Case -2146826265 '#REF
result = Double.NaN
Case -2146826273 '#VALUE!
result = Double.NaN
End Select
Form1.TextBox1.AppendText("Excel XNPV: " & result.ToString("F4") & vbCrLf)
XL.ActiveWorkbook.Close(SaveChanges:=False)
XL.Quit()
End Sub
This produces the following result. Remember, your results will be different because the dates and the cash flows were generated randomly and the 2 results will be different for the same reason.
You will notice that when you run the test_XL.testXNPV() that a worksheet is open and you can see it populated with the cash flows.
So not only is the code way more complicated, the computation is much more resource-intensive because we are actually just automating an Excel process, getting the result, and closing the process down. Of course, that process can be interrupted by almost any event on the machine in which it is running.
XLDLL was simple, straightforward, and completely under the control of the .NET framework; a huge advantage as far as I am concerned.
Let's compare performance. In this test we will randomly create 1 thousand test cases which consist of from 50 to 500 cash flows. To exclude the cash flow creation process from the timings we create the following Class.
Public Class cXNPV
Public rate As Double
Public cf As Double()
Public cfdate As Date()
End Class
We can use the following code to create the timings for XLDLL.
Public Shared Sub testXNPV()
Dim result As Double()
Dim r1 As New System.Random
Dim r2 As New System.Random
Dim X As New Dictionary(Of Integer, cXNPV)
Dim C As cXNPV
Dim n As Integer
'Create a thousand test cases
For i As Integer = 0 To 999
n = r2.Next(50, 500)
C = New cXNPV
With C
ReDim .cf(n)
ReDim .cfdate(n)
For j As Integer = 0 To n
.cf(j) = Math.Round(-50000 + r1.NextDouble * 100000, 2)
.cfdate(j) = Today.AddDays(r2.Next(1, 3650))
Next
.rate = Math.Round(0.00001 + 0.2 * r1.NextDouble, 5)
End With
X.Add(i, C)
Next
'Loop through the test cases and calculate the XNPV values
Dim time_start As Date
Dim time_end As Date
ReDim result(999)
time_start = Now
For i As Integer = 0 To 999
With X(i)
result(i) = wct.XNPV(.rate, .cf, .cfdate)
End With
Next
time_end = Now
Form1.TextBox1.AppendText("XLDLL XNPV elapsed time: " & (time_end - time_start).TotalMilliseconds.ToString("F4") & " milliseconds" & vbCrLf)
End Sub
Here is the code to test the Excel version.
Public Shared Sub testXNPV()
Dim XL As New Excel.Application
Dim result As Double()
Dim r1 As New System.Random
Dim r2 As New System.Random
Dim wkb As Excel.Workbook = XL.Workbooks.Add
Dim wks As New Excel.Worksheet
Dim rng As Excel.Range
Dim rng_data As Excel.Range
Dim X As New Dictionary(Of Integer, cXNPV)
Dim C As cXNPV
Dim n As Integer
'Create a thousand test cases
For i As Integer = 0 To 999
n = r2.Next(50, 500)
C = New cXNPV
With C
ReDim .cf(n)
ReDim .cfdate(n)
For j As Integer = 0 To n
.cf(j) = Math.Round(-50000 + r1.NextDouble * 100000, 2)
.cfdate(j) = Today.AddDays(r2.Next(1, 3650))
Next
.rate = Math.Round(0.00001 + 0.2 * r1.NextDouble, 5)
End With
X.Add(i, C)
Next
'Loop through the test cases and calculate the XNPV values
Dim time_start As Date
Dim time_end As Date
ReDim result(999)
wks = DirectCast(wkb.Sheets(1), Excel.Worksheet)
time_start = Now
For i As Integer = 0 To 999
With X(i)
Array.Sort(.cfdate, .cf)
For j As Integer = 0 To .cf.GetUpperBound(0)
wks.Cells(j + 1, 1) = .cf(j)
wks.Cells(j + 1, 2) = .cfdate(j)
Next
wks.Cells(.cf.GetUpperBound(0) + 2, 2) = "=XNPV(" & .rate.ToString & "," & "A1:A" & (.cf.GetUpperBound(0) + 1).ToString & "," & "B1:B" & (.cf.GetUpperBound(0) + 1).ToString & ")"
rng = DirectCast(wks.Cells(.cf.GetUpperBound(0) + 2, 2), Excel.Range)
rng_data = wks.Range("A1:B" & .cf.GetUpperBound(0) + 1)
End With
result(i) = DirectCast(rng.Value, Double)
Select Case result(i)
Case -2146826281 '#DIV/0!
result(i) = Double.NaN
Case -2146826246 '#N/A!
result(i) = Double.NaN
Case -2146826259 '#NAME!
result(i) = Double.NaN
Case -2146826288 '#NULL
result(i) = Double.NaN
Case -2146826252 '#NUM!
result(i) = Double.NaN
Case -2146826265 '#REF
result(i) = Double.NaN
Case -2146826273 '#VALUE!
result(i) = Double.NaN
End Select
rng.Clear()
rng_data.Clear()
Next
time_end = Now
Form1.TextBox1.AppendText("Excel XNPV elapsed time: " & (time_end - time_start).TotalMilliseconds.ToString("F4") & " milliseconds" & vbCrLf)
wkb.Close(SaveChanges:=False)
XL.Quit()
End Sub
This produces the following results.
As you can see, XLDLL was more than 9100 times faster than using Excel, and the code was much simpler.
If you are interested in having a 21st century algorithmic capability in your .NET code instead of automating processes from the 1990's, you should try the 15-day trial of XLeratorDLL/financial.
It takes about a minute to download and about a minute to install and comes with loads of VB and C# examples that you can use as templates to start coding.
See also
