 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

# Re: Series on excel

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)

# Re: Series on excel

Works as expected!!!! Many thanks!!!, very quick response, i reaaally appreciate your help @Hans Vogelaar

# Re: Series on excel

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%
best response confirmed by HvgoCA (New Contributor)
Solution

# Re: Series on excel

@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!

# Re: Series on excel

@Rajesh-S 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.

# Re: Series on excel

Oh Great!!, this works too, i'll use it too, thanks!.

# Re: Series on excel

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.

# Re: Series on excel

@Rajesh-S Allow me to comment: The point is that the number of times the part "(1+%)^n" needs to be included, varies with the number of periods. Therefore you cannot build a static formula that includes a fixed number of these calculations. If there are only two periods, it would be "(1+%)^2 + (1+%)^1". Six periods would be "(1+%)^6 + (1+%)^5 ........ + (1+%)^1".

You'll need a UDF or DA formula as demonstrated above. Try this: change the number of periods to 6 but DO NOT edit your formula! You'll see it won't work.

# Re: Series on excel

You could either follow @Riny_van_Eekelen solution of

``````= (base*factor/n) * SUM((1+perc)^k - 1)
where k is
=SEQUENCE(n)``````

or you could bite the bullet and sum the geometric series algebraically

``= (base*factor/n) * (((1+perc)^n - 1)*(1+perc)/perc - n)``

# Re: Series on excel

Check my formula I've never suggested to use Cell references for PERIODs,,, are sitll ^4, ^3, ^2 & ^1,,, I have shown that how the value of r , n & i can be used through Cell references.

Since the OP (original post owner) has raised the issue,,, "change the period, it automatically return the value, like this if, n = 2", and I responded that value of n can be stored in cell even,, no where the OP said the period.

In example above OP has used

r = 100
n = 4
i = 3%

No description of PERIOD !!

• And if the PERIODs are few, then we may also put then in cells,, like ^\$A\$1 for ^4 and others.

I'm agree on using UDF, since is better and faster.

# Re: Series on excel

@Rajesh-S Let's agree that we interpret the original post differently.

# Re: Series on excel

My implementation of the algebraic solutions contains @Riny_van_Eekelen 's as a fully functional solution, as well as listing the terms that he summed.  I see no problem with his solution.