How Do I calculate Compound Tax Interest over different terms?

[NYCRealEstateNerd]

Hi Folks! I am working on a spreadsheet that calculates estimated real estate taxes for restaurants (among other things) over a period of years and comparing them to one another. Its a fairly simple compound interest calculation but the added variable is that I want to be able to calculate it automatically but each restaurant that I am evaluating has different terms (7, 9, 10 years etc).

So I was trying to use this formula to calculate it: ((P*(1+i)^n) - P)

but that formula just calculates the additional amount owed at the end of the term. I need to calculate the cumulative cost of real estate taxes by multiplying the "RE Yearly Taxes", by the "Estimated RE Tax base escalation", by the "Length Of Lease (Years)" automatically. Where I input the length of the lease"

Does that question make sense?

Address:	Restaurant1	Restaurant2	Restaurant Prototype
Square Footage:
Basement SqFt:
C of O:
Ideal # of Seats:
Yeatly Gross Per Seat:
Gross Annual Revenue:
Length Of Lease (years):
Estimated Full Term Gross Income:
Build-Out Costs:
Security Deposit:
Base Rent:
Escalation:
Rent Year 1:
Rent Year 2:
Rent Year 3:
Rent Year 4:
Rent Year 5:
Rent Year 6:
Rent Year 7:
Rent Year 8:
Rent Year 9:
Rent Year 10:
Full Term Rent Total:
Key Money:
Key Money Interest Paid:
Interest Paid On Key Money:
Interest Paid On Build Out:
RE Monthly Tax base:
RE Yearly Taxes:
Estimated RE Tax base escalation*:
Estimated Full Term RE Taxes:	must create formula for this, mrexcel.com

<colgroup><col style="width: 235px"><col width="153"><col width="182"><col width="183"><col width="197"></colgroup><tbody>
[TD="align: right"]2,200[/TD]
[TD="align: right"]3,000[/TD]
[TD="align: right"]2,000[/TD]

[TD="align: right"]1,800[/TD]

[TD="align: right"]1,500[/TD]

[TD="align: right"]68[/TD]
[TD="align: right"]65[/TD]

[TD="align: right"]68[/TD]
[TD="align: right"]120[/TD]
[TD="align: right"]85[/TD]

[TD="align: right"]$21,176[/TD]
[TD="align: right"]$21,176[/TD]
[TD="align: right"]$21,176[/TD]

[TD="align: right"]$1,440,000[/TD]
[TD="align: right"]$2,541,176[/TD]
[TD="align: right"]$1,800,000[/TD]

[TD="align: right"]7[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]10[/TD]

[TD="align: right"]$10,080,000[/TD]
[TD="align: right"]$22,870,588[/TD]
[TD="align: right"]$18,000,000[/TD]

[TD="align: right"]-$100,000[/TD]
[TD="align: right"]-$250,000[/TD]
[TD="align: right"]-$480,000[/TD]

[TD="align: right"]-$48,750[/TD]
[TD="align: right"]-$60,000[/TD]
[TD="align: right"]-$60,000[/TD]

[TD="align: right"]-$16,250[/TD]
[TD="align: right"]-$20,000[/TD]
[TD="align: right"]-$20,000[/TD]

[TD="align: right"]3.00%[/TD]
[TD="align: right"]3.00%[/TD]
[TD="align: right"]3.00%[/TD]

[TD="align: right"]-$195,000[/TD]
[TD="align: right"]-$240,000[/TD]
[TD="align: right"]-$240,000[/TD]

[TD="align: right"]-$200,850[/TD]
[TD="align: right"]-$247,200[/TD]
[TD="align: right"]-$247,200[/TD]

[TD="align: right"]-$206,876[/TD]
[TD="align: right"]-$254,616[/TD]
[TD="align: right"]-$254,616[/TD]

[TD="align: right"]-$213,082[/TD]
[TD="align: right"]-$262,254[/TD]
[TD="align: right"]-$262,254[/TD]

[TD="align: right"]-$219,474[/TD]
[TD="align: right"]-$270,122[/TD]
[TD="align: right"]-$270,122[/TD]

[TD="align: right"]-$226,058[/TD]
[TD="align: right"]-$278,226[/TD]
[TD="align: right"]-$278,226[/TD]

[TD="align: right"]-$232,840[/TD]
[TD="align: right"]-$286,573[/TD]
[TD="align: right"]-$286,573[/TD]

[TD="align: right"]-$295,170[/TD]
[TD="align: right"]-$295,170[/TD]

[TD="align: right"]-$304,025[/TD]
[TD="align: right"]-$304,025[/TD]

[TD="align: right"]-$313,146[/TD]

[TD="align: right"]-$1,494,180[/TD]
[TD="align: right"]-$2,438,185[/TD]
[TD="align: right"]-$2,751,331[/TD]

[TD="align: right"]-$450,000[/TD]
[TD="align: right"]-$325,000[/TD]
[TD="align: right"]-$120,000[/TD]

[TD="align: right"]20.00%[/TD]
[TD="align: right"]20.00%[/TD]
[TD="align: right"]0.00%[/TD]

[TD="align: right"]-$90,000[/TD]
[TD="align: right"]-$65,000[/TD]
[TD="align: right"]$0[/TD]

[TD="align: right"]-$1,500[/TD]
[TD="align: right"]-$1,500[/TD]
[TD="align: right"]-$1,000[/TD]

[TD="align: right"]-$18,000[/TD]
[TD="align: right"]-$18,000[/TD]
[TD="align: right"]-$12,000[/TD]

[TD="align: right"]0.41%[/TD]
[TD="align: right"]0.41%[/TD]
[TD="align: right"]0.41%[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]-$127,800[/TD]

</tbody>

 

[karate718]

I do not know the answer, but I think the question is summed up as follows:

Base Annual Amount: 18,000
Annual Rate of increase: .41%
Years: variable (7, 9 & 10 in this example)

The idea is to put a formula in a single cell that calculates this rather than manually entering

18,000 *(1+ .41%) + 18,000 *(1+ .41%)^2 + 18,000 *(1+ .41%)^3 + 18,000 *(1+ .41%)^4...

 

[Dave Patton]

Excel 2010
	B	C	D
1
2	18,000.00	18,000.00	12,000.00
3	0.41%	0.41%	0.41%
4	7	9	10
5	($127,560.43)	($164,682.37)	($122,238.38)
6
5b

Cell Formulas
Range	Formula
B5	=FV(B3,B4,B2)
C5	=FV(C3,C4,C2)
D5	=FV(D3,D4,D2)

Does the above work for you?

 

[Dave Patton]

Possibly more technically correct.

Excel 2010
	B	C	D
2	18,000.00	18,000.00	12,000.00
3	0.41%	0.41%	0.41%
4	7	9	10
5	$127,560.43	$164,682.37	$122,238.38
6
5b

Cell Formulas
Range	Formula
B5	=-FV(B3,B4-1,B2,,1)+B2
C5	=-FV(C3,C4-1,C2,,1)+C2
D5	=-FV(D3,D4-1,D2,,1)+D2

 

[karate718]

Looks like it to me Dave!

 

[NYCRealEstateNerd]

Thanks this is good!

Possibly more technically correct.
Excel 2010

	B	C	D

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]
[TD="align: right"]18,000.00[/TD]
[TD="align: right"]18,000.00[/TD]
[TD="align: right"]12,000.00[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]0.41%[/TD]
[TD="align: right"]0.41%[/TD]
[TD="align: right"]0.41%[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]$127,560.43 [/TD]
[TD="align: right"]$164,682.37 [/TD]
[TD="align: right"]$122,238.38 [/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

5b​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B5[/TH]
[TD="align: left"]=-FV(B3,B4-1,B2,,1)+B2[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C5[/TH]
[TD="align: left"]=-FV(C3,C4-1,C2,,1)+C2[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D5[/TH]
[TD="align: left"]=-FV(D3,D4-1,D2,,1)+D2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[NYCRealEstateNerd]

Thank you all for the suggestions, I have it now! the FV formula is key..

 

[Dave Patton]

Thanks for the feedback. Dave

 

[NYCRealEstateNerd]

Thank you for the feedback Dave!!