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)

 

[Ozzman]

But there isn't anything at that point in the code that would trip an error.

Well, it trips. I opened up a brand new module and pasted it in and I got an error. I have everything you had in the code you gave (minus the 'Exit Function') and changed the 'As Double' to 'As Range' and I get a value. I insert the 'Exit Function' and I get a #NUM! in the cell

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.

I'll try that

 

[RSXchin]

I changed my post. re-read it.

 

[Ozzman]

I changed my post. re-read it.

Still gives me an incorrect answer

 

[RSXchin]

any chance your math is off? I'm throwing in the towel on this one. Sorry

 

[Ozzman]

any chance your math is off? I'm throwing in the towel on this one. Sorry

My math is not off. Sorry, man! I appreciate your help with this though! This is closer than I've come in the last week.

 

[RSXchin]

I might get OCD about this and try it tonight.... would you please post a sample of your work, and the desired outcome? Use the Excel Jeanie that is listed in the sticky.

 

[Ozzman]

I might get OCD about this and try it tonight.... would you please post a sample of your work, and the desired outcome? Use the Excel Jeanie that is listed in the sticky.

That seems too complicated and I don't have enough time to do that. I will post a screenshot instead:

Okay, the custom function is in the 5th column (net(E6:E15,etc). The regular ol' present value of these amounts is given in the column to the left of the column with the Net() function. The difference between these amounts is in the very last column. Feel free to copy these values and see how far off it is. First three columns are rates, values and time respectively. </br></br>

 

[RSXchin]

question 1. What part of the sheet am I looking at? (what range)
and 2. What is the desired outcome? (as you've done by hand)

 

[RSXchin]

here you go

Function Net(ByVal r As Range, ByVal v As Range, ByVal t As Range)
    If r.Count <> v.Count Or r.Count <> t.Count Then
        Net = "ERROR"
    Else
        For i = 1 To v.Count
            Net = Net + (v(i) / ((1 + r(i)) ^ t(i)))
        Next i
    End If
End Function

 

[RSXchin]

This one only requires you to input ONE range

i.e.

=Net(A1:C10)

Function Net(ByVal rvt As Range)
    For i = 1 To rvt.Rows.Count
        Net = Net + (rvt(i, 2) / ((1 + rvt(i, 1)) ^ rvt(i, 3)))
    Next i
End Function