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 can't seem to think of a way to calculate this with an excel formula as I'm not really familiar with array formulas.

So, here is a VBA code:

Public Function calcNetPresent(FV as Double, interest As Double, years As Double) As Double
    For i = 1 To years
        calcNetPresent = calcNetPresent + (FV / (1 + interest) ^ 20)
    Next i
End Function

Put this code in your worksheet module and use it like a formula on your spreadsheet

=calcNetPresent (FV, interest, years)

 

[Koejns]

Thanks for this answer. The question however, remains because the client I write this excel sheet for has specificly asked me not to write VBAs or macros

 

[Seti]

If you FV is fixed over all durations and your interest rate does not change, then you can use the following formula to derive you present value:

=-PV(rate,years,FV)

This assumes that the FV occurs at the end of the year. If it is at the beginning of the year, change the formula to

=-PV(rate,years,FV,,1)

 

[kpark91]

=SUM(FV / (1 + interest) ^ ROW(1:year))

To use an array formula, always enter with Ctrl+Shift+Enter

 

[kpark91]

If you FV is fixed over all durations and your interest rate does not change, then you can use the following formula to derive you present value:

=-PV(rate,years,FV)

This assumes that the FV occurs at the end of the year. If it is at the beginning of the year, change the formula to

=-PV(rate,years,FV,,1)

Hi, Seti. What does "-" infront of the formula do?

 

[Seti]

Well, the PV formula returns a negative value if the FV value is positive. I assume that the OP does not want that, so I put a minus sign at the beginning. It is optional, depending on the use of the result.

 

[Scott Huish]

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

 

[Koejns]

So I tried it and kpark91's formula returns the correct value as the FV formula returns something strange so I'm going to try it with the formula I stated myself and got upgraded by kpark91.

The only thing that still bothers me: If I set the ROW(1:Years) with the years as a reference to a cell the ROW formula doesn't calculate any value, how can I get rid of this problem?

 

[kpark91]

Nvm. Let me work with it for a bit and I'll get back to you.