NPV

[Guest]

Anyone know how I can do an NPV calculation with more than 29 values? Many
of the adjacent values are identical and there will be no more than 29
different numerical values, however, using ranges doesn't seem to help.
Also, is there an instruction book which goes into solid detail on the
financial or other functions? Excel help is too elementary for many aspects.
Thanks.

 

[Ron Rosenfeld]

Anyone know how I can do an NPV calculation with more than 29 values? Many
of the adjacent values are identical and there will be no more than 29
different numerical values, however, using ranges doesn't seem to help.
Also, is there an instruction book which goes into solid detail on the
financial or other functions? Excel help is too elementary for many aspects.
Thanks.

=NPV(rate,B1:B100)

is perfectly valid syntax.

The 29 argument limit is true for all Excel functions; but the above is
considered to have only one argument by the parser, even though it includes 100
values. (Well one "value" argument plus the rate argument, so maybe two
arguments).




--ron

 

[Guest]

thanks, Ron. I agree and have tried, but if I do the calculation =NPV(rate,
A1:A24, B!:B24), I get a numerical value which is nonsensically smaller than
what it should be. certainly not what I would get on my HP 12-C financial
calculator. any ideas?

 

[Ron Rosenfeld]

thanks, Ron. I agree and have tried, but if I do the calculation =NPV(rate,
A1:A24, B!:B24), I get a numerical value which is nonsensically smaller than
what it should be. certainly not what I would get on my HP 12-C financial
calculator. any ideas?

My first guess would be that there is something funny about your data. Perhaps
some of the values that appear to be numeric are really text.

The reliable way to check that is to execute =ISTEXT(cell_ref) on each cell.

Or you could enter an **ARRAY** formula =OR(ISTEXT(A1:B24)).

To enter an **ARRAY** formula, hold down <ctrl><shift> while hitting <enter>.
If you did it correctly, Excel will place braces {...} around the formula. If
the above formula gives a result of TRUE, then some of your entries are TEXT
and will not be interpreted as numeric values by the formula.

Another possibility is that, because of cell formatting, you are not seeing the
true contents of the cell, and that is skewing the results.

Also, if "rate" is text (do the ISTEXT(rate)), that would also give you a small
value.

If that is not the problem, if you can, post the values you are using and let's
see what we get on a different machine.
--ron

 

[David Biddulph]

thanks, Ron. I agree and have tried, but if I do the calculation
=NPV(rate,
A1:A24, B!:B24), I get a numerical value which is nonsensically smaller
than
what it should be. certainly not what I would get on my HP 12-C financial
calculator. any ideas?

Have you really got =NPV(rate,A1:A24, B!:B24)?

If so, try =NPV(rate,A1:A24, B!:B24), with B1 instead of B!

 

[Guest]

good idea, Ron. i will try this. you have been very helpful. thanks.

 

[Guest]

David - it was B1. I left my shift key down on the 1 by mistake. good
catch. thanks.

 

[Guest]

Ron Rosenfeld - thanks for your input. You are correct and your response was
very helpful to me. Thanks alot. Hal