VBA Function returning #VALUE! when trying to refer to Optional Parameter-CodePudding

I am trying to calculate a NPV of several cashflows with continuous compounding. However when trying to refer to the optional parameter (which signals that the NPV will be continuous compounding), i get #VALUE! instead of a valid NPV

My VBA code is as follows:

Function myNPV(r As Double, CF As Range, Optional rate_type = 0)

Sum = 0
t = 0
For Each Cell In CF
    Sum = Sum   Cell.Value / (1   r) ^ t
    t = t   1
Next Cell

If rate_type = 1 Then
    Sum = Sum   Cell.Value / ((Exp(1)) ^ (r * t))
    t = t   1
End If

myNPV = Sum

End Function

The first of the formulas calculates a non-continuous-compounded NPV, which is why I've introduced the optional parameter to choose between the two parameters.

When testing out my formula as follows: "=mynpv(C10, C3:C8, 1)" (C10 = discount rate, C3-C8 = Cash flows and 1 refering to the rate type), I simply get #Value!

CodePudding user response：

I believe you want to include both inside the loop and then use an IF to decide which to do:

Function myNPV(r As Double, CF As Range, Optional rate_type = 0)

Sum = 0
t = 0
For Each Cell In CF
    If rate_type = 0 Then
        Sum = Sum   Cell.Value / (1   r) ^ t
    Else
        Sum = Sum   Cell.Value / ((Exp(1)) ^ (r * t))
    End If
    t = t   1
Next Cell

myNPV = Sum

End Function