Help with iterative formula to avoid circular reference

**Soco15** · 02-14-2017, 01:45 AM

Hello,

I am trying to create a spreadsheet that will calculate a commercial real estate property's value based on income and expenses, for a range of cap rates.

Please see attached spreadsheet.

The formula is like this:

(Rental income) - (miscellaneous expenses) - (property tax) = net operating income

(Net operating income) divided by (cap rate) equals property value

However, the taxes are based on the property value. They are calculated as one percent of the property value.

You can see we have a circular reference.

I made an Excel sheet where I get around the circular reference by manually entering the value for property tax until it is one percent of the property value.

Is there a way to get Excel to calculate this manually? Would it have something to do with iterative calculations?

Thank you in advance for your help.

**MrShorty** · 02-14-2017, 01:58 AM

I think an iterative algorithm could solve this, and converge most of the time. However, more reliable still would be to apply some algebra to the problem and eliminate the circular reference completely:

your starting equations (variables abbreviated):
RI-ME-PT=NOI
PV=NOI/CR
PT=0.01*PV
Solve equation 2 for NOI, then substitute into equation1. Also substitute PT into equation 1
RI-ME-0.01*PV=PV*CR
Move all PV terms to the same side of the equation and continue to solve for PV (if you need a refresher on solving multi-step equations, I recommend this one: http://www.purplemath.com/modules/solvelin3.htm ) You should end up with a straightforward function to calculate PV from the income, expenses, the tax rate, and the cap rate.