# How Do I calculate Compound Tax Interest over different terms?

#### NYCRealEstateNerd

##### New Member
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: 2,200 3,000 2,000 Basement SqFt: 1,800 1,500 C of O: 68 65 Ideal # of Seats: 68 120 85 Yeatly Gross Per Seat: \$21,176 \$21,176 \$21,176 Gross Annual Revenue: \$1,440,000 \$2,541,176 \$1,800,000 Length Of Lease (years): 7 9 10 Estimated Full Term Gross Income: \$10,080,000 \$22,870,588 \$18,000,000 Build-Out Costs: -\$100,000 -\$250,000 -\$480,000 Security Deposit: -\$48,750 -\$60,000 -\$60,000 Base Rent: -\$16,250 -\$20,000 -\$20,000 Escalation: 3.00% 3.00% 3.00% Rent Year 1: -\$195,000 -\$240,000 -\$240,000 Rent Year 2: -\$200,850 -\$247,200 -\$247,200 Rent Year 3: -\$206,876 -\$254,616 -\$254,616 Rent Year 4: -\$213,082 -\$262,254 -\$262,254 Rent Year 5: -\$219,474 -\$270,122 -\$270,122 Rent Year 6: -\$226,058 -\$278,226 -\$278,226 Rent Year 7: -\$232,840 -\$286,573 -\$286,573 Rent Year 8: -\$295,170 -\$295,170 Rent Year 9: -\$304,025 -\$304,025 Rent Year 10: -\$313,146 Full Term Rent Total: -\$1,494,180 -\$2,438,185 -\$2,751,331 Key Money: -\$450,000 -\$325,000 -\$120,000 Key Money Interest Paid: 20.00% 20.00% 0.00% Interest Paid On Key Money: -\$90,000 -\$65,000 \$0 Interest Paid On Build Out: RE Monthly Tax base: -\$1,500 -\$1,500 -\$1,000 RE Yearly Taxes: -\$18,000 -\$18,000 -\$12,000 Estimated RE Tax base escalation*: 0.41% 0.41% 0.41% Estimated Full Term RE Taxes: -\$127,800 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>
</tbody>

### Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

#### karate718

##### Board Regular
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

##### Well-known Member

Excel 2010
BCD
1
218,000.0018,000.0012,000.00
30.41%0.41%0.41%
47910
5(\$127,560.43)(\$164,682.37)(\$122,238.38)
6
5b
Cell Formulas
RangeFormula
B5=FV(B3,B4,B2)
C5=FV(C3,C4,C2)
D5=FV(D3,D4,D2)

Does the above work for you?

#### Dave Patton

##### Well-known Member
Possibly more technically correct.

Excel 2010
BCD
218,000.0018,000.0012,000.00
30.41%0.41%0.41%
47910
5\$127,560.43\$164,682.37\$122,238.38
6
5b
Cell Formulas
RangeFormula
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

##### Board Regular
Looks like it to me Dave!

#### NYCRealEstateNerd

##### New Member
Thanks this is good!

Possibly more technically correct.
Excel 2010
BCD
218,000.0018,000.0012,000.00
30.41%0.41%0.41%
47910
5\$127,560.43 \$164,682.37 \$122,238.38
6

</tbody>
5b

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

</tbody>

<tbody>
</tbody>

#### NYCRealEstateNerd

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

#### Dave Patton

##### Well-known Member
Thanks for the feedback. Dave

#### NYCRealEstateNerd

##### New Member
Thank you for the feedback Dave!!

1,190,560
Messages
5,981,693
Members
439,730
Latest member
gjvv

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back