Total Cost with dynamic no of years and standard escalation %

[gorrepati]

Dear Friends,

Can you help me with a formula that calculates the total cost for a dynamic number of years where the cost escalates at a periodic interval.

Eg:

Cost in Year 1 - $ 100
Escalation after every 3 years = 12%
Total cost in no of years = make the number of years dynamic

I should be able to get the total cost by changing the number of years eg: 3 years, 5 years, 7 years, 10 years etc.

Appreciate your help in this matter.

Thanks...Chaitanya

[Bob Phillips]

Need more info.

Is that 100 every year, then 112 in year 3, 4 and 5, 125.44 in year 6 etc.? Or is it 100 + 12 more on 3rd (r4th?) year and so on?

[gorrepati]

Bob,

Thanks for your response

Its 100 in Year 1,2 and 3, 112 in Year 4,5 and 6 and 125.4 in Year 7,8 and 9 and 140.5 in Year 10. It increases by 12% after every 3 year period

Year 1 2 3 4 5 6 7 8 9 10
Cost 100 100 100 112 112 112 125.4 125.4 125.4 140.5

[Bob Phillips]

Assuming the year number (1,2,3,...) is in A1, B1, C1, ..., and A2 holds 100, enter this in B2

=IF(MOD(B1,3)=1,A2*112%,A2)

and copy across.

[gorrepati]

Thanks Bob for your response. This is helpful.

However for this i need to represent the data in multiple cells (from A1 to J1) and my objective is use just one cell to yield the result of the total cost.

For Eg: if A1 has the total number of years, i would like to get the total cost in A2 by giving a single formula in that cell.

Could you please help with this

Thanks a bunch again for your response.

Regards...

[Bob Phillips]

How about this

=-FV(12%,INT((A1-1)/3),0,100)

[gorrepati]

Thanks Bob for the formula.

This gives me the cost in the year selected. However am looking for the cumulative cost for period 1 till the period selected.

Is there a way i can get the sum of total cost for all years till the year selected.

Thanks so much for your help once again.

Regards...Chaitanya

[Bob Phillips]

Give me an example of what you will input and into which cells, and what output you would like to get, and into which cells.