Letting excel calculate with ^n

[Koejns]

I want to calculate the Net present value over a variable time of years.
Now I've got the formula =SUM(FV/(1+interest)^n where n is the amount of years.
However I want the SUM of all years apart.
I can set a expected number of years for the project but this is variable. So for instance I set the expected project years at 20 and I want Excel to automaticly calculate the SUM of (FV/(1+interest)^1+(FV/(1+interest)^2+(FV/(1+interest)^3....etc.+(FV/(1+interest)^20

How can I program this?

 

[kpark91]

I'm afraid I do not know how to fix the problem.

 

[MickG]

If essentialy what your trying to do is add Fv/(1+int)^1+FV/(1+int)^2 +FV/(1+int)^3 +FV*/(1+int)^4 ----FV/(1+int)^n. Then this formula will do that:-
(FV*(1+int)*([1/(1+int)] -[1/(1+int)^n+1)])/int= Sum
Mick

 

[Scott Huish]

How did this not work for you?

=SUMPRODUCT(FV/(1+interest)^ROW(INDIRECT("1:" & n)))

 

[Seti]

This SUMPRODUCT formula produces the same answer as the PV function as long as FV is a single value rather than a vector of values.

 

[Koejns]

HOTPEPPER thanx! It worked I didn't really look into your answer as I was busy trying the other answers but it workes great! Thanx! Problem solved