The Net Present Value (NPV) function isn’t directly available as a native VBA function, but you can utilize the Application.WorksheetFunction.NPV interface in VBA to access the Excel NPV function and execute it within your VBA code. Below you can find a simple example of using the NPV function in VBA. This VBA code is meant for use within an Excel macro:

Sub CalculateNPV()
    ' Define the variables
    Dim rate As Double
    Dim cashFlows As Variant
    Dim npv As Double
    
    ' Set the discount rate
    rate = 0.08 'Let's say 8%
    
    ' Define the future cash flows (negative for investments, positive for returns)
    ' For example, an initial investment of $100,000 and then cash inflows
    cashFlows = Array(-100000, 20000, 25000, 30000, 35000, 40000)
    
    ' Use the NPV function
    npv = Application.WorksheetFunction.NPV(rate, cashFlows)
    
    ' Add the initial investment to the result because NPV function does not include it
    npv = npv + cashFlows(0)
    
    ' Print the NPV value to the Immediate window
    Debug.Print "NPV is: $" & Format(npv, "0.00")
    
    ' You might also want to display it in a cell
    ThisWorkbook.Sheets("Sheet1").Range("A1").Value = npv
End Sub

When you run this subroutine (CalculateNPV), it calculates the NPV for an initial investment of $100,000 followed by cash inflows, with a discount rate of 8%, and it output the result to the Immediate window as well as into cell A1 on Sheet1. Note: 1. The initial investment is usually done in period 0, so it’s not included in the function’s calculation by default. The NPV from the function is the present value of cash flows from period 1 onward, to which you will need to add the initial investment manually (as I’ve done above with npv = npv + cashFlows(0)). 2. The cashFlows array should be structured such that indexes correspond to each period’s cash flow starting from period 1 (hence, cashFlows(0) is actually the initial outlay at period 0). 3. The NPV function within the Application.WorksheetFunction object takes the discount rate and a list of cash flows as parameters. The cash flows can be passed as an array or range reference from an Excel sheet. This is a simple example for illustrative purposes. VBA enables you to automate financial analysis like NPV calculations across a large number of data sets, integrate with other data sources, and more complex scenario analysis within Excel.