NPV Function

Returns a Double specifying the net present value of an investment based on a series of periodic cash flows (payments and receipts) and a discount rate.

Syntax

NPV(rate, values())

Parameters

rate (Required) - Double specifying discount rate over the length of the period, expressed as a decimal. For example, use 0.10 for 10 percent.
values() (Required) - Array of Double specifying cash flow values. The array must contain at least one negative value (a payment) and one positive value (a receipt). The values must be equally spaced in time and occur at the end of each period.

Return Value

Returns a Double specifying the net present value of the investment.

Remarks

The NPV (Net Present Value) function calculates the present value of a series of future cash flows, discounted at a specified rate. It's a fundamental tool in capital budgeting and investment analysis.

Key Characteristics:

Returns present value of future cash flows
All cash flows must be equally spaced in time
Cash flows occur at the end of each period
First cash flow (period 0) is NOT included - use separate calculation for initial investment
Negative values represent cash outflows (payments)
Positive values represent cash inflows (receipts)
Rate should match the period of cash flows (e.g., monthly rate for monthly cash flows)
Higher discount rates result in lower NPV
NPV > 0 generally indicates a good investment
NPV = 0 means investment breaks even
NPV < 0 indicates investment loses money

Important Note on Initial Investment:

Unlike some implementations, VB6's NPV does NOT include an initial investment (period 0) in the values array. If you have an initial investment, subtract it from the NPV result:

netPV = NPV(rate, cashFlows()) - initialInvestment

Common Use Cases:

Capital budgeting decisions
Investment project evaluation
Equipment purchase analysis
Real estate investment analysis
Business valuation
Compare alternative investments
Determine project profitability
Calculate present value of future revenues

Typical Uses

Investment Evaluation - Determine if an investment is worthwhile
Project Comparison - Compare multiple investment opportunities
Capital Budgeting - Decide which projects to fund
Equipment Purchases - Evaluate equipment vs. leasing decisions
Real Estate - Analyze property investment returns
Business Acquisition - Value businesses based on projected cash flows
Cost-Benefit Analysis - Compare costs and benefits over time
Break-even Analysis - Find discount rate where NPV = 0 (IRR)

Basic Examples

' Example 1: Simple investment with 5-year cash flows
Dim cashFlows(1 To 5) As Double
Dim npvResult As Double
Dim initialInvestment As Double
initialInvestment = 10000
cashFlows(1) = 3000
cashFlows(2) = 3000
cashFlows(3) = 3000
cashFlows(4) = 3000
cashFlows(5) = 3000
' Calculate NPV at 10% discount rate
npvResult = NPV(0.1, cashFlows) - initialInvestment
' Result: approximately $1,372 (good investment)

' Example 2: Evaluate equipment purchase
Dim savings(1 To 3) As Double
Dim equipmentCost As Double
Dim netValue As Double
equipmentCost = 5000
savings(1) = 2000 ' Year 1 savings
savings(2) = 2500 ' Year 2 savings
savings(3) = 3000 ' Year 3 savings
netValue = NPV(0.08, savings) - equipmentCost
If netValue > 0 Then
    MsgBox "Good investment: NPV = $" & Format(netValue, "#,##0.00")
End If

' Example 3: Compare two projects
Dim project1(1 To 4) As Double
Dim project2(1 To 4) As Double
Dim npv1 As Double, npv2 As Double
' Project 1: Higher initial investment, steady returns
project1(1) = 4000: project1(2) = 4000: project1(3) = 4000: project1(4) = 4000
npv1 = NPV(0.1, project1) - 12000
' Project 2: Lower investment, increasing returns
project2(1) = 2000: project2(2) = 3000: project2(3) = 4000: project2(4) = 5000
npv2 = NPV(0.1, project2) - 10000
If npv2 > npv1 Then
    MsgBox "Project 2 has better NPV"
End If

' Example 4: Real estate investment analysis
Dim rentalIncome(1 To 10) As Double
Dim purchasePrice As Double
Dim i As Integer
purchasePrice = 200000
' Annual rental income for 10 years
For i = 1 To 10
    rentalIncome(i) = 24000 ' $2,000/month
Next i
Dim propertyNPV As Double
propertyNPV = NPV(0.08, rentalIncome) - purchasePrice
MsgBox "Property NPV: $" & Format(propertyNPV, "#,##0.00")

Common Patterns

' Pattern 1: Basic NPV calculation with initial investment
Function CalculateInvestmentNPV(initialCost As Double, _
                               cashFlows() As Double, _
                               discountRate As Double) As Double
    CalculateInvestmentNPV = NPV(discountRate, cashFlows) - initialCost
End Function

' Pattern 2: Investment decision helper
Function ShouldInvest(initialInvestment As Double, _
                     cashFlows() As Double, _
                     requiredReturn As Double) As Boolean
    Dim npvResult As Double
    npvResult = NPV(requiredReturn, cashFlows) - initialInvestment
    ShouldInvest = (npvResult > 0)
End Function

' Pattern 3: Sensitivity analysis
Sub AnalyzeNPVSensitivity(initialCost As Double, cashFlows() As Double)
    Dim rate As Double
    Dim npvResult As Double
    Debug.Print "NPV Sensitivity Analysis"
    Debug.Print String(40, "-")
    For rate = 0.05 To 0.20 Step 0.01
        npvResult = NPV(rate, cashFlows) - initialCost
        Debug.Print Format(rate * 100, "0.0") & "%: $" & Format(npvResult, "#,##0.00")
    Next rate
End Sub

' Pattern 4: Profitability Index
Function CalculateProfitabilityIndex(initialInvestment As Double, _
                                     cashFlows() As Double, _
                                     discountRate As Double) As Double
    Dim presentValue As Double
    presentValue = NPV(discountRate, cashFlows)
    If initialInvestment = 0 Then
        CalculateProfitabilityIndex = 0
    Else
        CalculateProfitabilityIndex = presentValue / initialInvestment
    End If
End Function

' Pattern 5: Compare multiple projects
Function SelectBestProject(projects As Collection, discountRate As Double) As String
    Dim project As Variant
    Dim bestNPV As Double
    Dim bestProject As String
    Dim currentNPV As Double
    bestNPV = -999999
    For Each project In projects
        currentNPV = NPV(discountRate, project.CashFlows) - project.InitialCost
        If currentNPV > bestNPV Then
            bestNPV = currentNPV
            bestProject = project.Name
        End If
    Next project
    SelectBestProject = bestProject
End Function

' Pattern 6: Break-even discount rate (approximation of IRR)
Function FindBreakEvenRate(initialInvestment As Double, _
                          cashFlows() As Double) As Double
    Dim rate As Double
    Dim npvResult As Double
    Dim increment As Double
    increment = 0.001
    For rate = 0 To 1 Step increment
        npvResult = NPV(rate, cashFlows) - initialInvestment
        If npvResult <= 0 Then
            FindBreakEvenRate = rate
            Exit Function
        End If
    Next rate
    FindBreakEvenRate = -1 ' Not found
End Function

' Pattern 7: NPV with growing cash flows
Function NPVWithGrowth(initialInvestment As Double, _
                       firstYearCashFlow As Double, _
                       growthRate As Double, _
                       years As Integer, _
                       discountRate As Double) As Double
    Dim cashFlows() As Double
    Dim i As Integer
    ReDim cashFlows(1 To years)
    For i = 1 To years
        cashFlows(i) = firstYearCashFlow * ((1 + growthRate) ^ (i - 1))
    Next i
    NPVWithGrowth = NPV(discountRate, cashFlows) - initialInvestment
End Function

' Pattern 8: Payback period with NPV
Function CalculateDiscountedPayback(initialInvestment As Double, _
                                    cashFlows() As Double, _
                                    discountRate As Double) As Double
    Dim i As Integer
    Dim cumulativeNPV As Double
    Dim periodCashFlows() As Double
    For i = LBound(cashFlows) To UBound(cashFlows)
        ReDim periodCashFlows(LBound(cashFlows) To i)
        Dim j As Integer
        For j = LBound(cashFlows) To i
            periodCashFlows(j) = cashFlows(j)
        Next j
        cumulativeNPV = NPV(discountRate, periodCashFlows) - initialInvestment
        If cumulativeNPV >= 0 Then
            CalculateDiscountedPayback = i
            Exit Function
        End If
    Next i
    CalculateDiscountedPayback = -1 ' Never pays back
End Function

' Pattern 9: NPV with terminal value
Function NPVWithTerminalValue(initialInvestment As Double, _
                              cashFlows() As Double, _
                              terminalValue As Double, _
                              discountRate As Double) As Double
    Dim i As Integer
    Dim modifiedCashFlows() As Double
    ReDim modifiedCashFlows(LBound(cashFlows) To UBound(cashFlows))
    For i = LBound(cashFlows) To UBound(cashFlows) - 1
        modifiedCashFlows(i) = cashFlows(i)
    Next i
    ' Add terminal value to final period
    modifiedCashFlows(UBound(cashFlows)) = cashFlows(UBound(cashFlows)) + terminalValue
    NPVWithTerminalValue = NPV(discountRate, modifiedCashFlows) - initialInvestment
End Function

' Pattern 10: Risk-adjusted NPV
Function RiskAdjustedNPV(initialInvestment As Double, _
                        expectedCashFlows() As Double, _
                        riskFreeRate As Double, _
                        riskPremium As Double) As Double
    Dim adjustedRate As Double
    adjustedRate = riskFreeRate + riskPremium
    RiskAdjustedNPV = NPV(adjustedRate, expectedCashFlows) - initialInvestment
End Function

Advanced Usage

Example 1: Investment Analyzer Class

' Class: InvestmentAnalyzer
' Comprehensive investment analysis with NPV and related metrics
Option Explicit
Private m_initialInvestment As Double
Private m_cashFlows() As Double
Private m_discountRate As Double
Public Sub Initialize(initialInvestment As Double, _
                     cashFlows() As Double, _
                     discountRate As Double)
    m_initialInvestment = initialInvestment
    m_cashFlows = cashFlows
    m_discountRate = discountRate
End Sub
Public Function GetNPV() As Double
    GetNPV = NPV(m_discountRate, m_cashFlows) - m_initialInvestment
End Function
Public Function GetProfitabilityIndex() As Double
    Dim pvCashFlows As Double
    pvCashFlows = NPV(m_discountRate, m_cashFlows)
    GetProfitabilityIndex = pvCashFlows / m_initialInvestment
End Function
Public Function IsAcceptable() As Boolean
    IsAcceptable = (GetNPV() > 0)
End Function
Public Function GetApproximateIRR() As Double
    Dim rate As Double
    Dim npvResult As Double
    Dim lastRate As Double
    Dim lastNPV As Double
    ' Binary search for IRR
    Dim lowRate As Double, highRate As Double
    lowRate = 0
    highRate = 1
    Do While (highRate - lowRate) > 0.0001
        rate = (lowRate + highRate) / 2
        npvResult = NPV(rate, m_cashFlows) - m_initialInvestment
        If npvResult > 0 Then
            lowRate = rate
        Else
            highRate = rate
        End If
    Loop
    GetApproximateIRR = rate
End Function
Public Function GenerateReport() As String
    Dim report As String
    Dim npvValue As Double
    Dim pi As Double
    Dim irr As Double
    npvValue = GetNPV()
    pi = GetProfitabilityIndex()
    irr = GetApproximateIRR()
    report = "Investment Analysis Report" & vbCrLf
    report = report & String(50, "=") & vbCrLf & vbCrLf
    report = report & "Initial Investment: $" & Format(m_initialInvestment, "#,##0.00") & vbCrLf
    report = report & "Discount Rate: " & Format(m_discountRate * 100, "0.00") & "%" & vbCrLf
    report = report & "Number of Periods: " & UBound(m_cashFlows) & vbCrLf & vbCrLf
    report = report & "Net Present Value: $" & Format(npvValue, "#,##0.00") & vbCrLf
    report = report & "Profitability Index: " & Format(pi, "0.00") & vbCrLf
    report = report & "Approx. IRR: " & Format(irr * 100, "0.00") & "%" & vbCrLf & vbCrLf
    If IsAcceptable() Then
        report = report & "Recommendation: ACCEPT - Positive NPV"
    Else
        report = report & "Recommendation: REJECT - Negative NPV"
    End If
    GenerateReport = report
End Function
Public Function RunSensitivityAnalysis() As String
    Dim result As String
    Dim rate As Double
    Dim npvValue As Double
    result = "NPV Sensitivity to Discount Rate" & vbCrLf
    result = result & String(40, "-") & vbCrLf
    For rate = 0.05 To 0.25 Step 0.05
        npvValue = NPV(rate, m_cashFlows) - m_initialInvestment
        result = result & Format(rate * 100, "0.0") & "%: $" & _
                 Format(npvValue, "#,##0.00") & vbCrLf
    Next rate
    RunSensitivityAnalysis = result
End Function

Example 2: Project Portfolio Manager

' Class: ProjectPortfolioManager
' Manages and ranks multiple investment projects
Option Explicit
Private Type Project
    name As String
    initialInvestment As Double
    cashFlows() As Double
    npv As Double
    pi As Double
    rank As Integer
End Type
Private m_projects As Collection
Private m_discountRate As Double
Public Sub Initialize(discountRate As Double)
    Set m_projects = New Collection
    m_discountRate = discountRate
End Sub
Public Sub AddProject(name As String, _
                     initialInvestment As Double, _
                     cashFlows() As Double)
    Dim proj As Project
    proj.name = name
    proj.initialInvestment = initialInvestment
    proj.cashFlows = cashFlows
    proj.npv = NPV(m_discountRate, cashFlows) - initialInvestment
    Dim pvCashFlows As Double
    pvCashFlows = NPV(m_discountRate, cashFlows)
    proj.pi = pvCashFlows / initialInvestment
    m_projects.Add proj, name
End Sub
Public Sub RankByNPV()
    ' Simple ranking based on NPV
    Dim projects() As Project
    Dim i As Integer, j As Integer
    ReDim projects(1 To m_projects.Count)
    For i = 1 To m_projects.Count
        projects(i) = m_projects(i)
    Next i
    ' Sort by NPV (descending)
    For i = 1 To UBound(projects) - 1
        For j = i + 1 To UBound(projects)
            If projects(j).npv > projects(i).npv Then
                Dim temp As Project
                temp = projects(i)
                projects(i) = projects(j)
                projects(j) = temp
            End If
        Next j
    Next i
    ' Assign ranks
    For i = 1 To UBound(projects)
        projects(i).rank = i
    Next i
End Sub
Public Function GetPortfolioNPV() As Double
    Dim proj As Project
    Dim totalNPV As Double
    Dim i As Integer
    totalNPV = 0
    For i = 1 To m_projects.Count
        proj = m_projects(i)
        totalNPV = totalNPV + proj.npv
    Next i
    GetPortfolioNPV = totalNPV
End Function
Public Function SelectProjectsWithBudget(budget As Double) As Collection
    Dim selectedProjects As New Collection
    Dim remainingBudget As Double
    Dim proj As Project
    Dim i As Integer
    RankByNPV
    remainingBudget = budget
    For i = 1 To m_projects.Count
        proj = m_projects(i)
        If proj.npv > 0 And proj.initialInvestment <= remainingBudget Then
            selectedProjects.Add proj
            remainingBudget = remainingBudget - proj.initialInvestment
        End If
    Next i
    Set SelectProjectsWithBudget = selectedProjects
End Function
Public Function GenerateRankingReport() As String
    Dim report As String
    Dim proj As Project
    Dim i As Integer
    RankByNPV
    report = "Project Portfolio Ranking" & vbCrLf
    report = report & String(80, "=") & vbCrLf
    report = report & "Rank  Project Name          Investment       NPV          PI" & vbCrLf
    report = report & String(80, "-") & vbCrLf
    For i = 1 To m_projects.Count
        proj = m_projects(i)
        report = report & Format(proj.rank, "0") & "     "
        report = report & Left(proj.name & String(20, " "), 20) & "  "
        report = report & Format(proj.initialInvestment, "$#,##0") & "  "
        report = report & Format(proj.npv, "$#,##0") & "  "
        report = report & Format(proj.pi, "0.00") & vbCrLf
    Next i
    report = report & vbCrLf & "Total Portfolio NPV: $" & _
             Format(GetPortfolioNPV(), "#,##0.00")
    GenerateRankingReport = report
End Function

Example 3: Real Estate Investment Analyzer

' Module: RealEstateAnalyzer
' Analyzes real estate investments using NPV
Option Explicit
Public Function AnalyzeRentalProperty(purchasePrice As Double, _
                                     annualRent As Double, _
                                     annualExpenses As Double, _
                                     holdingYears As Integer, _
                                     appreciationRate As Double, _
                                     discountRate As Double) As String
    Dim cashFlows() As Double
    Dim i As Integer
    Dim salePrice As Double
    Dim npvValue As Double
    Dim result As String
    ReDim cashFlows(1 To holdingYears)
    ' Calculate annual net cash flows
    For i = 1 To holdingYears - 1
        cashFlows(i) = annualRent - annualExpenses
    Next i
    ' Final year includes property sale
    salePrice = purchasePrice * ((1 + appreciationRate) ^ holdingYears)
    cashFlows(holdingYears) = (annualRent - annualExpenses) + salePrice
    npvValue = NPV(discountRate, cashFlows) - purchasePrice
    result = "Real Estate Investment Analysis" & vbCrLf
    result = result & String(50, "=") & vbCrLf
    result = result & "Purchase Price: $" & Format(purchasePrice, "#,##0") & vbCrLf
    result = result & "Annual Rent: $" & Format(annualRent, "#,##0") & vbCrLf
    result = result & "Annual Expenses: $" & Format(annualExpenses, "#,##0") & vbCrLf
    result = result & "Holding Period: " & holdingYears & " years" & vbCrLf
    result = result & "Appreciation Rate: " & Format(appreciationRate * 100, "0.0") & "%" & vbCrLf
    result = result & "Discount Rate: " & Format(discountRate * 100, "0.0") & "%" & vbCrLf
    result = result & "Estimated Sale Price: $" & Format(salePrice, "#,##0") & vbCrLf & vbCrLf
    result = result & "Net Present Value: $" & Format(npvValue, "#,##0.00") & vbCrLf
    If npvValue > 0 Then
        result = result & "Recommendation: Good investment opportunity"
    Else
        result = result & "Recommendation: Consider other opportunities"
    End If
    AnalyzeRentalProperty = result
End Function
Public Function CompareBuyVsLease(equipmentCost As Double, _
                                 leaseCosts() As Double, _
                                 buyingCashFlows() As Double, _
                                 discountRate As Double) As String
    Dim buyNPV As Double
    Dim leaseNPV As Double
    Dim result As String
    buyNPV = NPV(discountRate, buyingCashFlows) - equipmentCost
    leaseNPV = NPV(discountRate, leaseCosts)
    result = "Buy vs. Lease Analysis" & vbCrLf
    result = result & String(40, "-") & vbCrLf
    result = result & "Buying NPV: $" & Format(buyNPV, "#,##0.00") & vbCrLf
    result = result & "Leasing NPV: $" & Format(leaseNPV, "#,##0.00") & vbCrLf
    result = result & vbCrLf
    If buyNPV > leaseNPV Then
        result = result & "Recommendation: BUY (NPV advantage: $" & _
                 Format(buyNPV - leaseNPV, "#,##0.00") & ")"
    Else
        result = result & "Recommendation: LEASE (NPV advantage: $" & _
                 Format(leaseNPV - buyNPV, "#,##0.00") & ")"
    End If
    CompareBuyVsLease = result
End Function

Example 4: Business Valuation Tool

' Class: BusinessValuationTool
' Values businesses using discounted cash flow (DCF) method
Option Explicit
Private m_projectedCashFlows() As Double
Private m_terminalValue As Double
Private m_discountRate As Double
Public Sub ProjectCashFlows(baseCashFlow As Double, _
                           growthRate As Double, _
                           years As Integer)
    Dim i As Integer
    ReDim m_projectedCashFlows(1 To years)
    For i = 1 To years
        m_projectedCashFlows(i) = baseCashFlow * ((1 + growthRate) ^ i)
    Next i
End Sub
Public Sub CalculateTerminalValue(finalYearCashFlow As Double, _
                                 perpetualGrowthRate As Double, _
                                 discountRate As Double)
    ' Terminal value using Gordon Growth Model
    m_terminalValue = (finalYearCashFlow * (1 + perpetualGrowthRate)) / _
                      (discountRate - perpetualGrowthRate)
End Sub
Public Function GetEnterpriseValue(discountRate As Double) As Double
    Dim i As Integer
    Dim cashFlowsWithTerminal() As Double
    m_discountRate = discountRate
    ' Create array including terminal value
    ReDim cashFlowsWithTerminal(1 To UBound(m_projectedCashFlows))
    For i = 1 To UBound(m_projectedCashFlows) - 1
        cashFlowsWithTerminal(i) = m_projectedCashFlows(i)
    Next i
    ' Add terminal value to final year
    cashFlowsWithTerminal(UBound(m_projectedCashFlows)) = _
        m_projectedCashFlows(UBound(m_projectedCashFlows)) + m_terminalValue
    GetEnterpriseValue = NPV(discountRate, cashFlowsWithTerminal)
End Function
Public Function GetEquityValue(enterpriseValue As Double, _
                              debt As Double, _
                              cash As Double) As Double
    GetEquityValue = enterpriseValue - debt + cash
End Function
Public Function GenerateValuationReport(companyName As String, _
                                       debt As Double, _
                                       cash As Double) As String
    Dim report As String
    Dim ev As Double
    Dim equity As Double
    ev = GetEnterpriseValue(m_discountRate)
    equity = GetEquityValue(ev, debt, cash)
    report = "Business Valuation: " & companyName & vbCrLf
    report = report & String(60, "=") & vbCrLf & vbCrLf
    report = report & "Discounted Cash Flow Analysis" & vbCrLf
    report = report & "Discount Rate (WACC): " & Format(m_discountRate * 100, "0.00") & "%" & vbCrLf
    report = report & "Projection Period: " & UBound(m_projectedCashFlows) & " years" & vbCrLf
    report = report & vbCrLf
    report = report & "Enterprise Value: $" & Format(ev, "#,##0,000") & vbCrLf
    report = report & "Less: Debt: $" & Format(debt, "#,##0,000") & vbCrLf
    report = report & "Plus: Cash: $" & Format(cash, "#,##0,000") & vbCrLf
    report = report & String(60, "-") & vbCrLf
    report = report & "Equity Value: $" & Format(equity, "#,##0,000")
    GenerateValuationReport = report
End Function

Error Handling

On Error Resume Next
Dim npvResult As Double
npvResult = NPV(rate, cashFlows())
If Err.Number <> 0 Then
    MsgBox "Error calculating NPV: " & Err.Description & vbCrLf & _
           "Ensure cash flows array is properly dimensioned and rate is valid."
End If
On Error GoTo 0

Performance Considerations

NPV calculation is relatively fast
Performance scales linearly with number of cash flows
For large arrays (1000+ periods), consider optimization
Caching results when using same inputs repeatedly
More efficient than manually discounting each period

Best Practices

Remember initial investment - Subtract it from NPV result (not in array)
Use appropriate discount rate - Match to risk and period
Validate cash flow array - Ensure proper dimensioning
Check for mixed signs - Need both positive and negative flows
Consider risk - Use higher discount rate for riskier investments
Document assumptions - State discount rate rationale
Sensitivity analysis - Test multiple discount rates
Compare to alternatives - Use NPV to rank projects
Account for inflation - Use real rates or inflate cash flows
Validate results - Ensure NPV makes sense given inputs

Function	Purpose	Key Difference
NPV	Net present value	Assumes end-of-period cash flows
PV	Present value	Single/annuity payments only
IRR	Internal rate of return	Finds rate where NPV = 0
MIRR	Modified IRR	Uses separate financing/reinvestment rates
FV	Future value	Calculates future amount, not present

Platform Notes

Available in VBA (Excel, Access, Word, etc.)
Available in VB6
Not available in VBScript
Equivalent to Excel's NPV function
Part of VBA financial functions library
Requires array parameter (not ParamArray)

Limitations

Assumes equally spaced time periods
Cash flows occur at end of each period
Does NOT include initial investment in values array
Cannot handle irregular time periods (use XNPV in Excel for that)
Assumes constant discount rate
Does not account for taxes directly
Requires at least one positive and one negative value

IRR - Finds internal rate of return (discount rate where NPV = 0)
MIRR - Modified internal rate of return
PV - Present value of annuity or lump sum
FV - Future value of investment
PMT - Payment amount for loan/investment
Rate - Interest rate per period

VB6 Parser Notes

NPV is parsed as a regular function call (CallExpression). This module exists primarily for documentation purposes to provide comprehensive reference material for VB6 developers working with financial analysis, investment evaluation, capital budgeting, and discounted cash flow calculations.