NPV function

[Kosior_1990]

Hello!

I would like to create correct NPV function in excel (because the one built-in excel do not include cash-flow form period 0).Below I list my objects:

1.Count NPV
2.User selects range (rows are adjacent, each below other in one column)
3.Users inputs interest rate as a number (not percentage, in order to speed up calculation)
4.First row from selection is divided by 0, next one is divided by (1+i)^k (k - number of row in selection); i - it is interest rate

I have written the code, but it doesn't work. Could you help me please?

Function MNPV(interest_rate As Double, flow())

Dim i As Integer

MNPV = 0


For i = 0 To UBound(flow)

flow(i) = flow(i) / ((1 + interest_rate / 100) ^ i)
MNPV = MNPV + flow(i)
Next i

End Function

Thank you very much for all responses!

 

[shg]

Welcome to the board.

because the one built-in excel do not include cash-flow form period 0

Why can't you use the standard NPV function? From Help,

The NPV investment begins one period before the date of the value1 cash flow and ends
with the last cash flow in the list. The NPV calculation is based on future cash flows. If your first
cash flow occurs at the beginning of the first period, the first value must be added to the NPV
result, not included in the values arguments.

 

[Kosior_1990]

I know this, but I use NPV function very often and would like to select a range with casf-flows, insert interest rate and have result. It would be much faster than calculating NPV and then adding cash-flow from period 0.

 

[anandvarma]

try
XNPV FUNCTION

 

[Kosior_1990]

Sorry, but that's not the case. I've just created sub procedure which displays correct result, but I cannot do correct function, because do not know how to use range selected by the user (in particular how can i count cells in the range chosen by an user?).

 

[brettdj]

Rather than create a new function or use Excel's existing functions you could just calculate the discount factors manually. This gives complete control over discount period timing and rates

This may be of use
An overview of using NPV and IRR in Excel for evaluating investment decisions

Cheers

Dave

 

[xenou]

Not sure - this is untested but a quick try at rewriting your function:

Function MNPV(interest_rate As Double, [COLOR="Blue"]flow As Range[/COLOR])

Dim i As Integer
[COLOR="Blue"]Dim a As Variant[/COLOR]

MNPV = 0
[COLOR="Blue"]a = flow.Value[/COLOR]

For i = LBound([COLOR="Blue"]a[/COLOR]) To UBound([COLOR="Blue"]a[/COLOR])

flow(i) = [COLOR="Blue"]a[/COLOR](i) / ((1 + interest_rate / 100) ^ i)
MNPV = MNPV + [COLOR="Blue"]a[/COLOR](i)
Next i

End Function

 

[shg]

The objective of all this is to avoid the inconvenience of using a formula like this

=NPV(rate, A2:A10) + A1

instead of

=SomeCustomFunction(rate, A1:A10)

It just doesn't seem worth the effort, considering the subtleties of NPV.