formula - calculate the yearly cost each year of the 10 year contract

Hello,

I need help with a formula that can calculate the re-purchase or re-lease of equipment on a contract at the escalated rate during the time of repurchase.

For example, I have a 10 year contract (120 months) and the equipment I am leasing is a 15 month lease and each time the lease is up and I re-lease the equipment I am adding an escalation factor.

The original monthly lease cost is $400.00 and the escalation is compounded annually at 3% each contract year but the escalation rate is only applied at time of repurchase.

Each cell is calculating the lease cost on an annual basis (12 months), so 3 months into contract year 2 (month 16) I will need to re-lease the equipment. So 3 months of contract year 2 will have the original lease cost $400.00) and 9 months will have the escalated lease cost ($400.00 x 1.03). Then the following year (contract year 3) will have 6 months with the previous years escalation cost ($400.00 x 1.03) and then it is time to repurchase the equipment at the new escalation rate ($400.00 x 1.06) and this will go on for the full ten years of the contract.

The lease cost and term of lease (months) are just examples and change according to equipment being leased.

Can someone please help me with a formula that I can calculate the yearly cost each year of the 10 year contract?

Please let me know if there is any additional information necessary.

I´m sure there is a direct way of getting a formula but this will also give you what you want.

You can create a list on a seperate sheet with the amount multiplied by the increase. This list would have the increase on a 15 month basis.

On the main sheet your formulas are sums of that list on 12 month intervals.

Good thing is you can change the escalation % on each period and it will ajust automatically.

Only issue is if you´re lease period is different than 15. In this case you have to do a seperate list and ajust the formulas

See attached