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

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

karate718

Board Regular
Joined
Feb 25, 2005
Messages
93
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
Joined
Feb 15, 2002
Messages
5,029
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">18,000.00</td><td style="text-align: right;;">18,000.00</td><td style="text-align: right;;">12,000.00</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">0.41%</td><td style="text-align: right;;">0.41%</td><td style="text-align: right;;">0.41%</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">7</td><td style="text-align: right;;">9</td><td style="text-align: right;;">10</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">($127,560.43)</td><td style="text-align: right;;">($164,682.37)</td><td style="text-align: right;;">($122,238.38)</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:1.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">5b</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B5</th><td style="text-align:left">=FV(<font color="Blue">B3,B4,B2</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C5</th><td style="text-align:left">=FV(<font color="Blue">C3,C4,C2</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">D5</th><td style="text-align:left">=FV(<font color="Blue">D3,D4,D2</font>)</td></tr></tbody></table></td></tr></table><br />

Does the above work for you?
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
5,029
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Possibly more technically correct.
<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">18,000.00</td><td style="text-align: right;;">18,000.00</td><td style="text-align: right;;">12,000.00</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">0.41%</td><td style="text-align: right;;">0.41%</td><td style="text-align: right;;">0.41%</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">7</td><td style="text-align: right;;">9</td><td style="text-align: right;;">10</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">$127,560.43 </td><td style="text-align: right;;">$164,682.37 </td><td style="text-align: right;;">$122,238.38 </td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:1.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">5b</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B5</th><td style="text-align:left">=-FV(<font color="Blue">B3,B4-1,B2,,1</font>)+B2</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C5</th><td style="text-align:left">=-FV(<font color="Blue">C3,C4-1,C2,,1</font>)+C2</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">D5</th><td style="text-align:left">=-FV(<font color="Blue">D3,D4-1,D2,,1</font>)+D2</td></tr></tbody></table></td></tr></table><br />
 

karate718

Board Regular
Joined
Feb 25, 2005
Messages
93

ADVERTISEMENT

Looks like it to me Dave!
 

NYCRealEstateNerd

New Member
Joined
Dec 22, 2016
Messages
4
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>
 

NYCRealEstateNerd

New Member
Joined
Dec 22, 2016
Messages
4

ADVERTISEMENT

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

Forum statistics

Threads
1,148,334
Messages
5,746,158
Members
423,995
Latest member
excelbloggs

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
Top