Sum of a series increasing by a variable percentage

[wallacemp]

Hi all,

This one has stumped me and I can't find the answer online.

I need to sum a series that increases by a percentage each period. However, the percentage it increases depends on the number of periods. I.e., if it needs to increase from 80K to 100K over 3 years, the annual percentage increase would be 7.72%; but if it needs to increase from 80K to 100K over 4 years, the annual percentage increase would be 5.74%.

What I need is a formula that can take this variability into account while summing the total of series for the given period. E.g., I know that the answer for the 3 year example given above is 259,009 (80,000 for period 1 + 86,177 for period 2 + 92,832 for period 3), but I don't know how to get it into a single formula (which I need, given the variability explained above).

To be clear, I will always know the beginning and end points (80K and 100K in the example above), the number of periods, and the percentage increase. What I need is the sum of the amounts for each period, as explained above.

I'm sure there's a math equation I learned in high school that I'm forgetting here. Can anyone help?

 

[DRSteele]

The algorithms needed are simply annuity formulas. I doubt they were taught in high school (American ones, at least), so don't feel that you're forgetful.

Here are some formulas that yield the answer. The ones with Red font are single-cell formulas that rely on the three inputs only.

Book1
	A	B	C	D	E
1	periods	3
2	begin	80,000.00
3	end	100,000.00
4	CAGR (compound annualised growth rate)	7.7217%		7.7217%
5
6	Future Value (Excel functions)	259,009.16	259,009.16	259,009.16	259,009.16
7	Future Value (financial maths)	259,009.16	259,009.16
8
9
10
11	Period
12	1	80,000.00
13	2	86,177.39
14	3	92,831.78
15	end	259,009.16
Sheet35

Cell Formulas
Range	Formula
B4	=(B3/B2)^(1/B1)-1
B6	=FV(B4,B1,-B2)
B7	=1/B4*(1-1/(1+B4)^B1)*B3
B12	=B2
B13	=B2*(1+B4)
B14	=B13*(1+B4)
B15	=SUM(B12:B14)
D4	=RATE(B1,0,-B2,B3)
D6	=FV(D4,B1,-B2)
C6	=FV((B3/B2)^(1/B1)-1,B1,-B2)
C7	=1/((B3/B2)^(1/B1)-1)*(1-1/(1+((B3/B2)^(1/B1)-1))^B1)*B3
E6	=FV(RATE(B1,0,-B2,B3),B1,-B2)

 

[Marcelo Branco]

Maybe...


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Begin​

[/td][td]

End​

[/td][td]

Periods​

[/td][td]

Increase​

[/td][td]

Result​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

80000​

[/td][td]

100000​

[/td][td]

3​

[/td][td]

1,0772​

[/td][td]

259009,16​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

80000​

[/td][td]

100000​

[/td][td]

4​

[/td][td]

1,0574​

[/td][td]

348606,58​

[/td][/tr]
[/table]

Formula in D2 copied dow
=(B2/A2)^(1/C2)

Formula in E2 copied down: sum of terms of a geometric progression: S=a1*(q^n -1)/(q-1)
=A2*(D2^C2-1)/(D2-1)

M.

 

[Eric W]

A few more offerings:

	A	B	C	D	E	F
Start	End	# periods	Percent	sum of first n periods
Single cell formula

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]80000[/TD]
[TD="align: right"]100000[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]7.72%[/TD]
[TD="align: right"]259009.2[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]7.72%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]259009.2[/TD]
[TD="align: right"][/TD]

</tbody>

Sheet5​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]=EXP(LN(B2/A2)/C2)-1[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD]=A2*((1+D2)^C2-1)/D2[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D3[/TH]
[TD="align: left"]=RRI(C2,A2,B2)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E5[/TH]
[TD="align: left"]=A2*((1+RRI(C2,A2,B2))^C2-1)/RRI(C2,A2,B2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Much the same as Marcelo's formulas that sum a geometric series, but note that Excel has many financial functions. The RRI function in D3 calculates the interest rate without having to remember the formulas (although you do have to remember which function to use!).

 

[wallacemp]

Thanks everyone! DRSteele's equation in E6 worked perfectly, though I'm sure the others would work equally well. Really appreciate the help.

 

[DRSteele]

You're welcome. Glad to have helped....