Calculating incremental increases on values over time

[Jon von der Heyden]

Hi All

I need to enlist some help today. I'm trying to avoid building an over-complicated higildey-pigildey helper table.

I have Current Costs, each itemised, over a 5 year span. Most of the current costs do not span the entire 5 year period.

I have for each cost item a Percentage Increase Factor (indicates how costs will grow/shrink). But the increase is not applied only once, nor is it increased in a linear manner over the time period. Thus I have an Increase Frequency Factor.

These components are used to calculated a schedule of Future Costs. I need to exclude current costs from the future schedule, yet I need to apply the increase factor according to the increase frequency factor.

Example:

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Current									Future
2	Cost Item	% Increase	Increase Frequency	2013	2014	2015	2016	2017		2013	2014	2015	2016	2017
3	X	10%	2	10,000						0	11,000	11,000	12,100	12,100
4	Y	5%	1	50,000	60,000					0	0	63,000	66,150	69,458
5	Z	15%	3	15,000	15,000	17,500				0	0	0	20,125	20,125
Sheet1

Referring to the example:

• Cost Item X - 2013 is zero as this cost appears in the "current costs" schedule. The increase is immediately applied in the 1st future period (i.e. 2014). This calculates 10000*1.1 = 11000. However as cost increases occur every 2 years, the cost in 2015 will be the same. The increase is applied again in 2016 (the 3rd year), i.e. 11000*1.1 = 12100. And again 2017 will remain the same as it is within the 2 year frequency since the last increase was applied.
  
• Cost Item Y - follows the same methodology. Note please that the last cost in the "current costs" schedule is used for the base of the future costs, plus the initial increase. As the increase frequency factor is 1 (annually); the increase factor (5%) is applied to each subsequent period.
  
• Cost Item Z - Same methodology again. The last current cost is used for the base, plus the initial increase. However as the increase frequency factor falls outside of the schedule time period (i.e. it would refer to 2018 which is not included); no further increases are applied.

I'm really battling to pull this together. Any bright ideas?

 

[ChrisM]

Any constraints? I'm assuming you don't want VBA, and you say no helper table, but what about a little hidden row or column if needed? (not that I have a solution yet!)

 

[RonB1111]

Enter this formula in K3 and copy down/across:

=IF(COUNT(D3:$H3)>1,0,IF(COUNT(D3:$H3),D3*(1+$B3),J3+IF(MOD(COLUMNS($J3:K3),$C3),0,J3*$B3)))

 

[ChrisM]

Nice one!

 

[RonB1111]

Nice one!

Thanks!

 

[Jon von der Heyden]

Hi Ron

Thanks very much for your suggestion. Unfortunately there is one small problem. Try changing cost item X's Increase Frequency to 3. You will notice that the initial increase to 11000 doesn't carry over across 3 periods, only 1, and then the next subsequent 3 periods adds an addition 10% on top of the 11000.

But I am very grateful indeed. Your suggestion got me scratching my head and in a round-about way helped me conjure up a working solution.

In J3:
=IF(D3,0,LOOKUP(10^307,$D3:$H3,$D3:$H3)*(1+$B3)^ROUNDUP(COUNTIF($D3:D3,"")/$C3,0))