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]

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

This one is perfect. Could you explain why you put 'v.count' as part of the For-Next loop instead of the 'For i = 1 to x1' and why it applies to the other two variables I have in there?

The other one gives me a number, but it is an incorrect one. I'll check it out when I get home today. I just popped in to do a quick and dirty test of the ones you gave.

 

[RSXchin]

I used v.count because it is the same number as the other 2. I only used 1 variable (i) because that's the best way to go through different rows. what was wrong with the other one? it gave me the same answer. well... provided all numbers are filled in.

 

[Ozzman]

I used v.count because it is the same number as the other 2. I only used 1 variable (i) because that's the best way to go through different rows. what was wrong with the other one? it gave me the same answer. well... provided all numbers are filled in.

Upon another inspection, the second one does work. Is the reason you have the (i, #) in there because it searches for the respective item in the equation?

 

[RSXchin]

It's a multidimentional array. It looks at "i" for the row, and the # as the relative column in the range. I probably should have added in a column check...try something like

If rvt.columns.count <> 3 then
     Net = 0
     Exit Function
End If

Put that at the second line.

 

[Ozzman]

It's a multidimentional array. It looks at "i" for the row, and the # as the relative column in the range. I probably should have added in a column check...try something like

If rvt.columns.count <> 3 then
     Net = 0
     Exit Function
End If

Put that at the second line.

I get a #VALUE! error when I do that. Where do I put it specifically? EDIT: Nevermind, I got it! Thanks for all your help!

 

[RSXchin]

what did you change to get it to work?

 

[Ozzman]

what did you change to get it to work?

I put the line here:

 If r.Count <> v.Count Or r.Count <> t.Count Then         Net = "ERROR"     If rvt.Columns.Count <> 3 Then      Net = 0      Exit Function

 

[brettdj]

I'm curious as to why you are applying different rates - what is your intent behind this?

Cheers

Dave

 

[Ozzman]

I'm curious as to why you are applying different rates - what is your intent behind this?

Cheers

Dave

1) VBA practice 2) When you invest, you don't necessarily earn the same rate every period. If I buy a bond and reinvest the coupons I get from said bond, the reinvestment rates I receive won't necessarily be the same as the rate I got when I bought the bond (for example: 6% coupon but I can only reinvest the coupons at 4%). There are also several strategies where, because of certain legislation, different interest rates have to be applied at different times when calculating pension benefits. You may earn 8% one year, but only 6% the next. What is the true NPV under this scenario?

 

[brettdj]

FWIW I think you are mixing valuing the bond cashflows (which should be discounted at a certain rate) with the potential re-investment. I don't think you should apply future reinvestment scenarios to your bond - it should be evaluated on its own mertis

Regards

Dave