Search code examples
excelgradient

Sigma Summation function in Excel - Aritmetical Gradients

I want to write a formula in Excel that works as follow:

https://i.sstatic.net/WhFXE.png

Where:

G = Aritmetical Gradient value 
R = Rent value
i = Interest Rate value (expressed in %)
n = Numbers of months

Any help you can give me, I will be really thankful.

Solution

  • SUMPRODUCT will do this:

    =SUMPRODUCT((B2+B1*(B4-SEQUENCE(B4+1,,0)))*(1+B3)^SEQUENCE(B4+1,,0))

    I made the assumption you want to include where k=n if not then remove the +1s in the formula.

    enter image description here

    If one does not have SEQUENCE we can substitute (ROW($ZZ1:INDEX($ZZ:$ZZ,B4+1))-1)

    =SUMPRODUCT((B2+B1*(B4-(ROW($ZZ1:INDEX($ZZ:$ZZ,B4+1))-1)))*(1+B3)^(ROW($ZZ1:INDEX($ZZ:$ZZ,B4+1))-1))

    enter image description here