Calculo del VAN o NPV

VAN = VAN son las siglas de Valor Actual Neto
NPV = Net Present Value

En la siguiente URL de Microsoft

https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/npv-function?f1url=https%3A%2F%2Fmsdn.microsoft.com%2Fquery%2Fdev11.query%3FappId%3DDev11IDEF1%26l%3Den-US%26k%3Dk%28vblr6.chm1009284%29%3Bk%28TargetFrameworkMoniker-Office.Version%3Dv16%29%26rd%3Dtrue" rel="nofollow - https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/npv-function?f1url=https%3A%2F%2Fmsdn.microsoft.com%2Fquery%2Fdev11.query%3FappId%3DDev11IDEF1%26l%3Den-US%26k%3Dk(vblr6.chm1009284)%3Bk(TargetFrameworkMoniker-Office.Version%3Dv16)%26rd%3Dtrue

Encontré este código que, tras probarlo, que di cuenta que No funciona correctamente.
Realicé unas ligeras modificaciones para que funcione; No está optimizado, solo adaptado para su correcto funcionamiento.

Se trata de una métrica financiera que determina el valor presente de una inversión, descontando los flujos de caja futuros y restando la inversión inicial.
Un VAN positivo indica rentabilidad; uno negativo, posibles pérdidas.


Código Original

Public Function funNPV_Mal()
' Original code Microsoft
No funciona bien

    Dim Fmt, Guess, RetRate, NetPVal, Msg
    Static Values(5) As Double    ' Set up array.
    Fmt = "###,##0.00"    ' Define money format.
    Guess = 0.1   ' Guess starts at 10 percent.
    RetRate = 0.0625   ' Set fixed internal rate.
    Values(0) = -70000    ' Business start-up costs.
    ' Positive cash flows reflecting income for four successive years.
    Values(1) = 22000: Values(2) = 25000
    Values(3) = 28000: Values(4) = 31000
    NetPVal = NPV(RetRate, Values())    ' Calculate net present value.
    Msg = "The net present value of these cash flows is "
    Msg = Msg & Format(NetPVal, Fmt) & "."
    Debug.Print Msg
    MsgBox Msg    ' Display net present value.
End Function



Código Modificado que Funciona correctamente

Public Function funNPV_Bien()
Si funciona bien
Dim Fmt
dim Guess
dim RetRate
dim NetPVal
dim Msg

    Fmt = "###,##0.00"
    ' Guess = 0.1 ' <<  No usado
    RetRate = 0.0625
    ' Values(0) = -70000 ' << No usado
    Const cInvestment = -70000
    Static Values(4) As Double ' << cambio 5 por un 4
    Values(0) = 22000: Values(1) = 25000
    Values(2) = 28000: Values(3) = 31000

    NetPVal = NPV(RetRate, Values()) + cInvestment
    Msg = "The net present value of these cash flows is "
    Msg = Msg & Format(NetPVal, Fmt) & "."
    Debug.Print Msg
    MsgBox Msg
End Function


Mas información
Wikipedia: https://en.wikipedia.org/wiki/Net_present_value" rel="nofollow - https://en.wikipedia.org/wiki/Net_present_value