I have a question about the NPV function in Excel?

When using the NPV function in Excel, I get a slightly different answer than if I discount each year's cash flows separately.

Example: invest $500 and use a 9% discount rate. What is the NPV over 5 years if the investment is in year 0 and the net undiscounted cash flow each year is $150 from years 1-5.

Using the NPV function, excel returns +$77. However, discounting each cash flow separately (year 1 = 150* (1-.09)^1; year 2 = 150*(1-.09)^2), etc.) returns +$70.

Any help you can provide would be really appreciated!

There are two issues to discuss... First, the Excel NPV function assumes the first cash flow happens in Period 1, not Period 0. Therefore, to use Excel to calculate your example, you must exclude the Period 0 cash flow from the NPV function, i.e. "=-500+NPV(0.09,150,150,150,150,150)". This will give the correct answer of $83.45.

The second issue is when calculating by hand, your discounting formula should be [year 1 = 150/(1+0.09)^1; year 2 = 150/(1+0.09)^2; etc.] Summing the results of this calculation method will also yield $83.45.

^^^great answer^^^
Give that person 10 points.