Calculating annual rent with fixed CPI over n years

I am using Excel 2007.
I want the user to enter the annual rent for year one into a cell - e.g. $16,000
I want the user to enter a fixed CPI for each year - e.g. 3.5%
I want the user to enter the number of years to calculate total rent paid - e.g. 3

The formula is:
(16,000*1.035) + ((16,000*1.035)*1.035) + (((16,000*1.035)*1.035)*1.035)
giving me the total rent paid including CPI increases for three years.

My solution is no good when I want the user to change the number of years and have the answer provided automatically.

I believe I could use a for loop using VB but hope someone can help me find a simple formula for my little problem.

Thanks Peter

If I understand, you want to calculate interest charged all 3 years assuming no payments are made, and continuing to charge a flat rent of 16000 / yr.

Then create input cells for:
number of years
rent amount
interest rate

Then in a new cell, use formula FV and apply those values accordingly.

The last line in the formula uses a 0, or 1 depending if you want to start charging interest from the start or end of the first year. If you enter the annual amount as + 16000 the formula will give you a negative result (meaning money owed). If you want it positive, you either need to enter it as a negative in the original cell, or make it negative in the formula. You can split the time intervals as you like (weeks, months, etc,..), but then you need to adjust the interest / payment amounts accordingly.

Boostjunkie,
Thank you for your reply.
No, I don't believe FV is the solution.
I am trying to compare renting an office versus buying an office and need to calculate how much total rent we will pay over the several years - for example 3, 5, 10, 15 or 25 years.
Where we rent now the rent is fixed at $16,000 for one year but increases with our consumer price index every year - say for example 3.5%
So for the next year we will pay $16,000 plus 3.5%, then for the following year it will be an additional 3.5% on next years rent (i.e. $16,000 + 3.5%). That is say the 3.5% is always caculated on the whole of last years rent which includes the 3.5% and so on.
I hope I have made myself more clear.
Thanks Peter

Peter - take a look at the attachment and let me know if this works for you.

Note: you have to run the Macro. Hit Atl &F11 and then F5.

Let me know if this works and we can modified it.

day92 - thanks for replying and yes the result you have provided works.
Now comes my issue of being able to integrate the years as a variable which I suspect needs a 'for loop'.
I look forward to learning from you my next step.
Regards Peter

I think the FV formula provides what you are looking for. Here is your total cost for the intervals you mentioned specifying 0 for the interest increase to take effect the following year.

1 $16,000.00
2 $32,560.00
3 $49,699.60
15 $308,730.89
25 $623,197.71

For example, Year 2 is 16,560 after subtracting year 1 (which is your 3,5% increase). Year 3 costs $17,139.60, which is 16,560 x 1,035.

Formula again,
=FV(A1,B1,-1*C1,,0)
where
A1=0.035 (interest)
B1= 1 (number of years you wish to study)
C1=16000 (base annual rate), -1 in the equation is to just have a positive result in the solution

Using one FV equation by itself will yield the total cost for that amount of years. You can also use it twice to determine the cost of any given year (by subtracting the total cost of the previous year).

Ex:
A1 (Interest): 0.035
B1 (X Years): 12
C1 (X Years -1): 11 (=B1-1)
D1 (base rate) 16000

E1 (Rate at end of X Years (FV 12 years - FV 11 years)) $23,359.52
Equation: =FV(A1,B1,-1*D1,,0)-FV(A1,C1,-1*D1,,0)