Forum Discussion

HvgoCA's avatar
HvgoCA
Copper Contributor
Oct 08, 2020
Solved

Series on excel

I have a value, 100 for example and a rate (5%) with a period on 3 years and use 90% of the result. I need to recreate this formula
(100/3*((1+5%)³-1)+100/3*((1+5%)²-1)+100/3*((1+5%)-1)*90%
So if i change the period, it automatically return the value
like this if n = 2
100/2*((1+5%)²-1)+100/2*((1+5%)-1)*90%

12 Replies

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor

    HvgoCA As a variant, and if you are on a recent version of Excel, please find a formula in the attached workbook, using the SEQUENCE function. No VBA required.

     

    Edit: updated file!

    • HvgoCA's avatar
      HvgoCA
      Copper Contributor
      Oh Great!!, this works too, i'll use it too, thanks!.
  • Rajesh_Sinha's avatar
    Rajesh_Sinha
    Iron Contributor
    It's very simple put the value of n in any cell and use the cell address/references in the formula for example,,,

    Suppose in cell A1 you kept value of n,, then this would be the formula.

    =100/$A$1*((1+5%)²-1)+100/$A$1*((1+5%)-1)*90%
    • HvgoCA's avatar
      HvgoCA
      Copper Contributor
      Rajesh_Sinha its not that simple, the idea is that when you change the value of "n" on a cel it changes the lengh of the entire ecuation: for example:
      r = 100
      n = 4
      i = 3%
      then you get:
      x = ((100/4(((1+0.03)^4)-1)+(100/4(((1+0.03)^3)-1)+(100/4(((1+0.03)^2)-1)+(100/4(((1+0.03)^1)-1))*0.90
      and goes on larger or smaller while changing n.
      • Rajesh_Sinha's avatar
        Rajesh_Sinha
        Iron Contributor

        HvgoCA 

         

        Check this, I've put value of n, r & i in cells F15, F17 & in F18.

         

        • Your formula in in cell I15:

         

         

        =((100/4*(((1+0.03)^4)-1)+(100/4*(((1+0.03)^3)-1)+(100/4*(((1+0.03)^2)-1)+(100/4*(((1+0.03)^1)-1))*0.9))))

         

         

        • My formula is in cell I18:

         

         

        =(($F$17/$F$15*(((1+$F$18)^4)-1)+($F$17/$F$15*(((1+$F$18)^3)-1)+($F$17/$F$15*(((1+$F$18)^2)-1)+($F$17/$F$15*(((1+$F$18)^1)-1))*0.9))))

         

         

         

        And both produces same results.

         

        Remember whenever you are using cell references in the formula, Excel makes the formula  dynamic, and change value in cell depends on situation, if and when required or not.

         

  • HvgoCA 

    Here is a custom VBA function:

    Function Outcome(Amount As Double, Rate As Double, Periods As Long) As Double
        Dim i As Long
        Dim x As Double
        Dim r As Double
        x = 1 + Rate
        For i = 1 To Periods
            r = r + x ^ i - 1
        Next i
        Outcome = r * Amount / Periods * 0.9
    End Function

    Use like this:

    =Outcome(100,5%,3)

    or enter the input values in cells, for example A1, A2 and A3:

    =Outcome(A1,A2,A3)

    • HvgoCA's avatar
      HvgoCA
      Copper Contributor

      Works as expected!!!! Many thanks!!!, very quick response, i reaaally appreciate your help HansVogelaar 

Resources