NPV Calc appears incorrect with a large # of negative cash flows

[Guest]

Using NPV on 48 values of which 47 are negative (i.e., a cumulative negative
balance that is increasing to 0). When I LOWERED the interest rate from 10%
to 1% the NPV got worse. In both cases, the NPV was a negative but at a
lower interest rate it was much more negative. It is almost like it is
calculating in the absolute. Don’t understand why. Not even sure I can use
NPV on a cumulative balance (trying to do a cost recover model over 4 years)

 

[Guest]

MikeW,
I do not believe this is an excel problem, but more of PV vs FV cash flows.
At a higher interest rate, cash flows farther in the future are more greatly
affected than those in the near term. With a lower hurdle rate/interest rate,
all cash flows are greater in future years than with a higher rate based on
absolute terms.

For example in year 1 at 10%, (1000) is reduced to (909.09). In year 5 it is
(620.92).

At a 1% rate, (1000) is reduced to (990.10). In year 5 it is (951.46). This
makes the cumulative negative cash flows more negative at a lower rate.

You can test the excel formula using your own formula {cash flow / (1 +
interest rate)^(year n)}