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

Question

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!

You're referring to cell after the for each cell loop has ended. — Rory
– Rory, Commented Mar 21, 2022 at 21:03
Please put 'Option explicit' as the first line in each of your module/class/forms. Make sure you do 'Debug.Compile Project' before trying to run your code. You may also benefit from installing the free and fantastic Rubberduck addin for VBA and checking the code inspections. You code is replete with bad practises. — freeflow
– freeflow, Commented Mar 21, 2022 at 21:05

Scott Craner · Accepted Answer · 2022-03-21 21:07:36Z

4

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

answered Mar 21, 2022 at 21:07

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related