What function does .NET NPV() use? Doesn't match manual calculations

Question

I am using the NPV() function in VB.NET to get NPV for a set of cash flows.

However, the result of NPV() is not consistent with my results performing the calculation manually (nor the Investopedia NPV calc... which matches my manual results)

My correct manual results and the NPV() results are close, within 5%.. but not the same...

Manually, using the NPV formula: NPV = C0 + C1/(1+r)^1 + C2/(1+r)^2 + C3/(1+r)^3 + .... + Cn/(1+r)^n

The manual result is stored in RunningTotal With rate r = 0.04 and period n = 10

Here is my relevant code:

EDIT: Do I have OBOB somewhere?

    YearCashOutFlow = CDbl(TxtAnnualCashOut.Text)
    YearCashInFlow = CDbl(TxtTotalCostSave.Text)

    YearCount = 1

    PAmount = -1 * (CDbl(TxtPartsCost.Text) + CDbl(TxtInstallCost.Text))
    RunningTotal = PAmount
    YearNPValue = PAmount
    AnnualRateIncrease = CDbl(TxtUtilRateInc.Text)

    While AnnualRateIncrease > 1
        AnnualRateIncrease = AnnualRateIncrease / 100
    End While
    AnnualRateIncrease = 1 + AnnualRateIncrease

    ' ZERO YEAR ENTRIES
    ListBoxNPV.Items.Add(Format(PAmount, "currency"))
    ListBoxCostSave.Items.Add("$0.00")
    ListBoxIRR.Items.Add("-100")
    ListBoxNPVCum.Items.Add(Format(PAmount, "currency"))
    CashFlows(0) = PAmount
    ''''

    Do While YearCount <= CInt(TxtLifeOfProject.Text)
        ReDim Preserve CashFlows(YearCount)

        CashFlows(YearCount) = Math.Round(YearCashInFlow - YearCashOutFlow, 2)
        If CashFlows(YearCount) > 0 Then OnePos = True


        YearNPValue = CashFlows(YearCount) / (1 + DiscountRate) ^ YearCount
        RunningTotal = RunningTotal + YearNPValue

        ListBoxNPVCum.Items.Add(Format(Math.Round(RunningTotal, 2), "currency"))
        ListBoxCostSave.Items.Add(Format(YearCashInFlow, "currency"))

        If OnePos Then
            ListBoxIRR.Items.Add((IRR(CashFlows, 0.1)).ToString)
            ListBoxNPV.Items.Add(Format(NPV(DiscountRate, CashFlows), "currency"))
        Else
            ListBoxIRR.Items.Add("-100")
            ListBoxNPV.Items.Add(Format(RunningTotal, "currency"))
        End If

        YearCount = YearCount + 1
        YearCashInFlow = AnnualRateIncrease * YearCashInFlow
    Loop

EDIT: Using the following values: Discount Rate = 4% Life of Project = 10 years Cash Flow 0 = -78110.00 Cash Flow 1 = 28963.23 Cash Flow 2 = 30701.06 Cash Flow 3 = 32543.12 Cash Flow 4 = 34495.71 Cash Flow 5 = 36565.45 Cash Flow 6 = 38759.38 Cash Flow 7 = 41084.94 Cash Flow 8 = 43550.03 Cash Flow 9 = 46163.04 Cash Flow 10 = 48932.82

Using the calculator at http://www.investopedia.com/calculator/NetPresentValue.aspx And following the manual "textbook" formula I arrive at the same result:

Net Present Value: $225,761.70

I cannot seem to get NPV() to replicate this result... it spits out $217,078.59

I iterate it manually using the example same value... so they must be using a different function than I am...

The MSDN page example clearly states that the initial expense should be included in the cash flows list.

Answer 1

Normally you wouldn't include the first cashflow in the Visual Basic NPV() function (or at least we don't in the leasing world). You would discount all but the first cash flow, then add that first cash flow amount onto your Net Present Value. Here's an example of what I've done before in a calculation engine (minus error checking to simplify the example):

Dim leaseRentalsDiscounted As Double = 0.0

Dim rebatedCashFlows As IEnumerable(Of LeasePayment) = GetLeaseRentalsPaymentStream()

Dim firstFlow As LeasePayment = rebatedCashFlows(0)

Dim doubleStream As Double() = PaymentToDoubleArray(rebatedCashFlows.Skip(1))

If doubleStream.Length > 0 Then
    Dim rate As Decimal = New Decimal(Me.Lease.DiscountRate / 100.0 / 12.0)
    leaseRentalsDiscounted = NPV(rate, doubleStream)
End If

leaseRentalsDiscounted += firstFlow.Amount

Return leaseRentalsDiscounted

That could account for your 5% -- I know I've run into an issue like this before. To me, in the manual NPV formula you posted, C0 doesn't need to be in the stream that is discounted, so that's why I don't include it in the NPV() function.

Answer 2

The MSDN page notes that if your cash outflow begins at the beginning of the first period (instead of the end) the first value must be added to the NPV value and not included in the cash flows array.

Your manual calculation shows that your cash outflow (C0) occurs at time zero (present value), which indicates you should follow the MSDN page's suggestion.

Answer 3

Cory Larson is right, in part... but the MSDN documentation seems in error to me.

The problem is that the NPV() function is discounting the very first (n=0) element of the array when it should not; it is beginning at n=1

Even though the MSDN documentation specifics that the first element of the array should be the initial expense this is not the case with their function.

In the NPV() function, the first element of the array (as Cory Larson implied) should be the first real cash flow. Then, after the function returns a result, the result should have the initial expense subtracted.

This is because the NPV() function begins with n=1 using the NPV formula: NPV = C0 + C1/(1+r)^1 + C2/(1+r)^2 + C3/(1+r)^3 + .... + Cn/(1+r)^n

In the manual formula, Cn/(1+r)^n, for n=0 you use your initial expense... then the denominator is 1 (because n=0)

In my opinion, the MSDN example at http://msdn.microsoft.com/en-us/library/microsoft.visualbasic.financial.npv.aspx should be amended to the following:

Exclude the initial -70000 value from the array, shift all element down one in index, and decrease the array size by 1. Then add the initial expense (-70000) to the variable NetPVal to arrive at the actual result.

Somebody should like MS know about their OBOB :D (But it's actually a feature, right?)

EDIT: And the section which says " The array must contain at least one negative value (a payment) and one positive value (a receipt)." In not accurate. As Cory Larson pointed out: a negative value is not required in the array (and, in fact, should be left out!)