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:

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 21^{st} 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.

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.

Archive

Monthly

Go

| |||||||||

Sun | Mon | Tue | Wed | Thu | Fri | Sat | |||
---|---|---|---|---|---|---|---|---|---|

28 | 29 | 30 | 31 | 1 | 2 | 3 | |||

4 | 5 | 6 | 7 | 8 | 9 | 10 | |||

11 | 12 | 13 | 14 | 15 | 16 | 17 | |||

18 | 19 | 20 | 21 | 22 | 23 | 24 | |||

25 | 26 | 27 | 28 | 29 | 30 | 1 | |||

2 | 3 | 4 | 5 | 6 | 7 | 8 |

Go