Login     Register

        Contact Us     Search

XLeratorDLL vs WorksheetFunction: XNPV

Aug 21

Written by: Charles Flock
8/21/2016 3:30 PM  RssIcon

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 free 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.
All the documentation is available on-line at westclintech.com. If you have any questions or if there is something that you would like to see added, just send an e-mail to support@westclintech.com.
See also

Tags:
Categories:
Copyright 2008-2017 Westclintech LLC         Privacy Policy        Terms of Service