Working backwards to year 1 rent with a fixed total

[Mazbuka]

Hello brainy people,

In negotiations to rent a new building, I know our budget is say $3,000,000 over 5 years and also that the Landlord will be looking for some annual increments.

I'm really struggling to put in a formula to calculate year 1 rent which allows me to experiment with various increment percentages etc.

In the table, column B (Increment) is changeable & I want a formula to calculate rent each year...it's giving me a headache.

First time poster so apologies in advance for any breaches in layout etiquette!

[TABLE="width: 279"]
<tbody>[TR]
[TD][TABLE="width: 500"]
<tbody>[TR]
[TD]YEAR[/TD]
[TD]INCREMENT[/TD]
[TD]ANNUAL RENT[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1.02[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1.02[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]1.03[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]REQUIRED TOTAL RENT[/TD]
[TD][/TD]
[TD]3,000,000[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[James006]

Hello,

As long as you have actually built your sheet with formulas and inputs ... going backwards can be done with Goal Seek ( Data > What If Analysis )

Hope this will help

 

[Macropod]

There's more to consider in this kind of forecasting than mere $ amounts. While you might now have $3,000,000 budgeted, unless your landlord is likely to increase the rent at more than the inflation rate, the annual requirement in terms of today's $ will be the same or less than it is for the first year. I believe it's normal for a 5-year lease to include either a fixed-price rental for the whole period (in which case the annual rent is likely to be decreasing in real terms), or for annual adjustments in line with inflation (in which case the annual rent remains constant in real terms).