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]

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

That was just a textual example. Of course the Bond PV is going to have it's own interest rate. What I'm using this for is not your concern. I can't go in to details on why.

 

[Ozzman]

That was just a textual example. Of course the Bond PV is going to have it's own interest rate. What I'm using this for is not your concern. I can't go in to details on why.

I'm not trying to be mean, but I just can't divulge any details on why I need something like this.

 

[brettdj]

Thats fine. If you are clear on the application of methodology then its sweet

Btw, I think you could do this with a function. While I dont play with advanced formulae much these days this effort
=SUMPRODUCT(--(A1:A5),--(1/(1+B1:B5)^(ROW(INDIRECT("1:5")))))
will discount end period cashflows in A1:A5 by different discount rates in B1:B5

Cheers

Dave

 

[Ozzman]

Thats fine. If you are clear on the application of methodology then its sweet

Btw, I think you could do this with a function. While I dont play with advanced formulae much these days this effort
=SUMPRODUCT(--(A1:A5),--(1/(1+B1:B5)^(ROW(INDIRECT("1:5")))))
will discount end period cashflows in A1:A5 by different discount rates in B1:B5

Cheers

Dave

What do the hyphens represent in this formula? I've never dealt with INDIRECT() before. That's a neat function

 

[brettdj]

The hyphens are double negative, they coerce boolean to numerical (ie TRUE to 1, FALSE to 0). The ROW(INDIRECT("1:5") is a way of quickly putting an array of numbers into a formula, I first saw Bob Ulmas work this trick on array formulas

You might find this article I wrote of interest, it sits outside the EE paywall so is freely accessible

An overview of using NPV and IRR in Excel for evaluating investment decisions http://www.experts-exchange.com/A_6535.html

Cheers

Dave