How to pass Range to VBA Basic Function for NPV formula

Question

I am trying to simplify the NPV function for PV0. The existing formula discounts the first number in the array, so to find the true NPV, you have to do "=NPV(rate, B2:B6) + A1" if:

[

Here is my code:

Function True_NPV(rate, PV0, CF_Series As Range)
True_NPV = NPV(rate, CF_Series) + PV0

End Function

When I do "=True_NPV(10%, B1, B2:B6)", I get this error message: "Compile Error: Type Mismatch: Array or user-defined type expected"

I'd appreciate it if someone could guide me on how to complete this, I couldn't find anything similar and struggled with the docs. I have just started learning VBA Basic.

Answer 1

Use WorksheetFunction.Npv.

True_NPV = WorksheetFunction.Npv(rate, CF_Series) + PV0

The NPV function (from VBA.Financial) expects an array of Double as its second parameter, hence your error.