[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 357
  • Last Modified:

Indexation question: How calculate the index growth if we have year 1 and year 4 figures?

Costs in Year 1 = $100
Costs in Year 4 = $130
What is the yearly growth rate.
So that if I was to multiply Year by "X", and then that result by "X", until year 4 it would give me an answer of $30 dollars.  

Thanks,
Jp
0
easycapital
Asked:
easycapital
4 Solutions
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

you cannot determine a reliable figure for growth rate from just two data points. You can calculate the average growth rate for each year between year 1 and year 4, something like

=(B4-B1)/COUNTA(A1:A4)

but that is based on a very slim date source.

Can you step back a bit and explain what you want to achieve?
0
 
jppintoCommented:
This should be the formula:

=((End Value/Start Value)^(1/(Periods - 1)) -1

I've attached a simple example file.

jppinto

GrowthCalculation.xlsx
0
 
StephenJRCommented:
About 9.1%. It's the cube root of 30%.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
jppintoCommented:
My formula is based on a "Constant Annual Growth Rate"...
0
 
Arno KosterCommented:
if the yearly growth rate is linear, such as :

year 1 : [start]
year 2 : [start] * (1+[growth])
year 3 : [start] * (1+[growth])^2
year 4 : [start] * (1+[growth])^3 = [end]

you can use the solver to solve this.

cell A1 : start value
cell A2 : growth value

cell B1 : formula =A1 (this is the value for year 1)
cell B2 : formula =A1*(1+A2) (this is the value for year 2)
cell B3 : formula =A1*(1+A2)^2 (this is the value for year 3)
cell B4 : formula =A1*(1+A2)^3 (this is the value for year 4)

then use the solver as in
http://www.youtube.com/watch?v=YAugMpW-aJw

menu data - analysis - solver
target cell = B4
by changing cell A2

starting at 100 & ending at 130 : growth rate = 9,13935128252419 % = 0,0913935128252419





0
 
Rory ArchibaldCommented:
Assuming 1 and 4 in A1 and A2 and 100 and 130 in B1:B2 then
=INDEX(LOGEST(B1:B2,A1:A2),1)-1
0
 
StormSeedCommented:
Actually, this sounds more like a high school homework question than something that would be needed in real life.

Especially since, even here in Africa, everyone learns how to do that in high school.
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Given easycapital's question history, I very much doubt that this is homework.
0
 
easycapitalAuthor Commented:
Very interesting approaches.
Thank you teylyn for the support.
Storm Seed, you came really close to getting all the points :)
On a serious note, this was to calculate manufacturing indexation.
Thank you.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now