Calculating NPV

[Tim]

In column "A" I have the years were the cash flow has occurred. These years
are different for different projects and might start differently and before
the actual year.
In column “B†I have the cash flow values for each individual year.

To calculate the Net Present Value of the project, I want to simply add the
cash values occurred before the actual Year (assuming past $ = present $) and
add then the calculated NPV for the (remaining) future years.

The first question is: Can I do that? (in terms of financial logic)
AND how do I do that?

To add the cash values occurred before I use the formula:
=SUMIF(A7:A18,"<="&DATE(YEAR(NOW()),12,31),B7:B18)

To calculate the NPV for the remaining years I was thinking smth like:
NPV(rate, offset(#, match()))
How do I accomplish this?

 

[ryguy7272]

This is good:
www.studyfinance.com/templates/NPV.xls

Or see these:

HTH,
Ryan---

 

[Tim]

thanks ryguy7272, but there were no examples how to calculate NPV from a
dynamic array.

I think I did it. The formulae is:
=NPV(CC28,OFFSET(BS7,MATCH(CJ11,BS7:BS18,0)-1,13,YEAR(CJ12)-YEAR(CJ11),))
where cc28 - dsct rate
bs7 - reference cell
height of arry - YEAR(CJ12)-YEAR(CJ11)

 

[Tim]

thanks ryguy7272 but the links dindt have any examples on how to calculate
NPV from a dynamic range.

I think I did it:
=NPV(CC28,OFFSET(BS7,MATCH(CJ11,BS7:BS18,0)-1,13,YEAR(CJ12)-YEAR(CJ11),))
if anyone any sugestion, please you are welcome.