Forum Discussion
Series on excel
(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%
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!
12 Replies
- PeterBartholomew1Silver Contributor
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)
- Riny_van_EekelenPlatinum 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!
- HvgoCACopper ContributorOh Great!!, this works too, i'll use it too, thanks!.
- Rajesh_SinhaIron ContributorIt'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%- HvgoCACopper ContributorRajesh_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_SinhaIron Contributor
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.
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)
- HvgoCACopper Contributor
Works as expected!!!! Many thanks!!!, very quick response, i reaaally appreciate your help HansVogelaar