How Do I calculate Compound Tax Interest over different terms?

NYCRealEstateNerd

New Member
Joined
Dec 22, 2016
Messages
4
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:Restaurant1Restaurant2Restaurant Prototype
Square Footage:2,2003,0002,000
Basement SqFt:1,8001,500
C of O:6865
Ideal # of Seats:6812085
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):7910
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,800must 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

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
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...
 
Upvote 0
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
 
Upvote 0
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

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</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

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 
Upvote 0

Forum statistics

Threads
1,213,553
Messages
6,114,279
Members
448,562
Latest member
Flashbond

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top