Multiply result by itself a number of times

[The Elk]

Hi,
Having problems with getting the right formula. I'm doing a variance analysis at work, and the result of my year 1 variance is £300. In year 2, this variance will occur again but also increase with the power of inflation @ 3.5%, hence be £310.50. The same will happen in year 3, but obviously it becomes £310.50 * (1+3.5%) = £321.37. This gives my total 3 years variance of £931.87 (£300 + £310.50 + £321.37). My variances last for 10 years.

So what I need is a formula that will multiply the result on itself by 1+ the inflation, over a set number of years, with the 2 factors being:
- the original £300 (say in cell A2)
- the inflation 3.5% (say in cell B2)

I've done it on a spreadsheet by creating 10 columns, having Yr 2 being Yr * (1+3.5%) and dragging it over the 10 columns(years) which is the right answer, but I need a formula that will return that same total but in just 1 cell (spreadsheet & data constraints!!!!)

Thanks in advance

 

[Dannyh1]

Hi Try,

A1 = 0
A2 = 1
A3 = 2

etc.. to column L

B1 = 300
B2 = =$A2*(1+3.5%)^B1+A2

copy across to column L

 

[Peter_SSs]

Welcome to the MrExcel board!

Do you just need this?
=A2*(1+B2)^10

If not, what result do you expect for the sample you have given?

 

[Peter_SSs]

Sorry, I mis-read you question - you want to sum all the values. Try this.

Excel Workbook
	A	B	C	D
1	Y1 Var	Inflation	Years	Result
2	300	3.50%	10	3519.42
The Elk

 

[The Elk]

Fantastic, thank you Peter. I wish I could understand the logic/narrative of why you do (minus original value) and also why it's (1-(1+inflation) .

But thank you, it fits perfectly.

 

[Peter_SSs]

What you are trying to sum is a geometric series. That is a seies where each term in the series is obtained by multiplying the previous term by a constant number. Your constant number is 1.035 (1 + inflation).

300 + (300*1.035) + (300*1.035*1.035) + ...

The formula for the sum of a geometric series is
S = a*(1-rⁿ)/(1-r)
where
a = first term (A2 = 300)
r = constant multiplier (1 + inflation = 1+B2)
n = number of terms (C2 = 10)
(Google geometric series for more info)

So
S = a*(1-rⁿ)/(1-r)
= A2*(1-(1+B2)^C2)/(1-(1+B2))
= A2*(1-(1+B2)^C2)/(1-1-B2)
= A2*(1-(1+B2)^C2)/(-B2)
then I just moved the - from the last term to the front and added the ROUND function.