In case anyone is interested, I've figured out a couple of ways of doing this. If I have a table that looks like this, where Y is a year, c is a series of cashflows, r is a discount rate for the year, p is a project number, I can calculate N, the NPV of future cashflows in a calculated column with the formulas:
=SUMX(Table1,if(Table1[Y]>=EARLIER(Table1[Y])&&Table1[p]=earlier(Table1[p]),Table1[C]/(1+earlier(table1[r]))^(Table1[Y]-EARLIER(Table1[Y]))))
or
=sumx(filter(Table1,Table1[Y]>=earlier(Table1[Y])&&Table1[p]=earlier(Table1[p])),Table1[C]/(1+earlier(table1[r]))^(Table1[Y]-EARLIER(Table1[Y])))
Y C r p N
2005 10 0.065 1 33
2006 7 0.09 1 24
2007 14 0.04 1 19
2008 5 0.02 1 5
2005 98 0.065 2 473
2006 76 0.09 2 386
2007 54 0.04 2 351
2008 309 0.02 2 309
I would still be interested if anyone can think of a better (more elegant or efficient) way of doing this. I'm building a complex model and performance will be an issue.
Cheers,
Mike.