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 ...
excel
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Oct 09, 2020

    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!

HansVogelaar's avatar
HansVogelaar
MVP
Oct 08, 2020

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
Oct 08, 2020

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

Resources