NPV function with variable rates

[Ozzman]

Hello all! I am attempting to create a function similar to Excel's NPV() function, but would like it to use more than one interest rate. I have attempted this in VBA using several sources and have coded the following:

Function Net(r, v, t) x1 = r.Count x2 = v.Count x3 = t.Count If x1 <> x2 <> x3 Then Net = CVErr(xlErrNum) End If Net = 0 For i = 1 To x1 For j = 1 To x2 For k = 1 To x3 Net = Net + v(j) / (1 + r(i)) ^ t(k) Next k Next j Next i Net = WorksheetFunction.Sum(r, v, t) End Function

I know I am missing a line or two of code that would make this work. Right now, this function is off by about 700 dollars with the example that I use. I am using a range of rates, values and times (in that order)

 

[RSXchin]

for one thing, you should Declare what kind of variable r, v, and t are.

 ByVal r as Double, ByVal V as Double, ByVal T as Double

this IS a pretty big function though.

 

[RSXchin]

Try this

Function Net(ByVal r As Double, ByVal v As Double, ByVal t As Double)
    x1 = r.Count
    x2 = v.Count
    x3 = t.Count
    If x1 <> x2 <> x3 Then
        Net = CVErr(xlErrNum)
        Exit Function
    End If
    Net = 0
    For i = 1 To x1
        For j = 1 To x2
            For k = 1 To x3
                Net = Net + v(j) / (1 + r(i)) ^ t(k)
            Next k
        Next j
    Next i
    Net = WorksheetFunction.Sum(r, v, t)
End Function

 

[Ozzman]

Try this

Function Net(ByVal r As Double, ByVal v As Double, ByVal t As Double)
    x1 = r.Count
    x2 = v.Count
    x3 = t.Count
    If x1  x2  x3 Then
        Net = CVErr(xlErrNum)
        Exit Function
    End If
    Net = 0
    For i = 1 To x1
        For j = 1 To x2
            For k = 1 To x3
                Net = Net + v(j) / (1 + r(i)) ^ t(k)
            Next k
        Next j
    Next i
    Net = WorksheetFunction.Sum(r, v, t)
End Function

I get an 'Invalid Qualifier' error in the first line

 

[Ozzman]

I get an 'Invalid Qualifier' error in the first line

I changed the 'As Double' to 'As Range' and it works again, but the answer is still off. If I insert the 'Exit Function' statement after the "Net = CVErr(xlErrNum)", then I get a #NUM! argument

 

[RSXchin]

hmm for some reason, the "<>" didn't copy over. make sure you add those back in.

 

[Ozzman]

hmm for some reason, the "<>" didn't copy over. make sure you add those back in.

I added those and that error still occurs when I use all of the code you suggested

 

[RSXchin]

I have NO idea what this is used for. I'm not an accountant. What kind of output are you expecting? is it a number? is it currency?

change the line

Net CVErr(xlErrNum)

to

Net = "Ranges not the same size"

or something like that.

 

[Ozzman]

I have NO idea what this is used for. I'm not an accountant. What kind of output are you expecting? is it a number? is it currency?

change the line

Net CVErr(xlErrNum)

to

Net = "Ranges not the same size"

or something like that.

I should be getting a number. It should be the sum of all the present values of the amounts given. It should be: v1/(1+r1)^t1 for each individual value and those need to be summed for the answer. Research 'Present Value' on Wiki if you want to follow along. For example: If I want to value of 500 dollars at time 1 NOW at a rate of 8%, I would go: 500/(1+.08)^1 and that would be the present (as in, today's) value of 500 dollars at time 1 (as in, calculated at time 1)

 

[RSXchin]

think i found a problem in your code then. you're using arrays v(i) and such. Arrays start with ZERO, not 1. so you're missing out on the first number of all of your arrays. change all of your

For i = 1 to x1

into

For i = 0 to x1 -1 step 1

That should give you different answers.