Write up a Excel Spreadsheet based on a Real Estate Example

Write down an accounting real estate spreadsheet.

Mortgage and Waterfall Homework:
2000 Magnolia Drive is a 10 story office building with 100,000 square feet of leased space. The building
is rented to two tenants, Dewey, Cheatem and Howe, a high profile law firm rents 75,000 square feet
and pays a gross rent of $25 per square foot on a new ten year lease. The rent goes up $1.00 per foot
annually. The expense stop is $10.00. The second tenants is Clueless Investigations, a detective firm.
They rent the remaining space under a lease with five years remaining at a fixed rent of $18 net and
they pay all of their expenses. Assume they will renew for an additional five years at $25 net with TA/LC
of $25/foot. Expenses this year are $10.50 and are expected to rise 3% per year. Assume Cap Ex
reserves are $.25 per square foot and management fees equal 3% of gross receipts. The building was
purchased at a 6% cap rate on first year stable NOI.
The owners were able to borrow 60% using the CMBS market at a 4.5% rate, 25 year amortization
schedule. The building is owned by a limited partnership. The GP invested 20% and the LP invested 80%.
The LPs are entitled to a 6% preferred return on invested capital. Any deficit is a preferred equity claim
and is cumulative carrying a 6% interest rate. The GP interest is 6% on invested capital. Any deficit is
cumulative carrying a 6% interest rate. The GP earns 30% of all operating cash flow above 6% level. Any
and all promoted returns are subject to a clawback should LP operating cash flow fall below 6% in any
year.
The property is sold at the end of year ten at an exit cap of 6.5% based on year 10 stable NOI. Proceeds
from sale are applied first to return invested capital to the LPs and then to the GP. Excess proceeds are
then applied first to any deficit LP balance at the 6% level, then to any GP deficit at the 6% level. The LPs
are then raised to an 8% lookback IRR. The GP is then raised to an 8% IRR. Any remaining amounts are
then paid out 60% to the LPs and 40% to the GP.
Create a spreadsheet that presents this example