Excel Lease Rental Disparity Model

I need an Excel workbook that lets me compare contracted rent to current market rent for up to 12 separate leases and then values the difference over each lease’s remaining term. The sheet should be set up with nine clearly labelled columns:

1. Tenant Name
2. Suite Area (sq ft)
3. Actual Rent / sq ft
4. Market Rent / sq ft
5. Rental Disparity (3-4)
6. Months Remaining
7. Total Monthly Disparity (5 × 2)
8. Present Value Factor
9. Present Value of Disparity

Key requirements
• One yield rate – entered once at the top of the sheet – will drive every present-value calculation. Users will choose any figure they like (typical range 5-8 %), and the model must discount the monthly cashflow with that annual rate converted to a monthly factor.
• The model must project each lease for its full remaining term even if it extends beyond five years; no hard limit at 60 months.
• For clarity, show both positive and negative disparities because some leases sit above market while others fall below.
• At the bottom, sum total square footage, undiscounted disparity, and the present-value disparity; display the final PV figure rounded to the nearest dollar.

Usability touches
– A clean input area (yellow cells or similar) so a non-technical user can paste tenant data quickly.
– Automatic checks that warn if more than 12 rows are filled.
– All formulas clearly visible (no hidden VBA needed).

Deliverable
An unlocked .xlsx file ready for immediate use, plus a brief note explaining how to change the global yield rate and add new leases later. I’ll consider the project complete when my sample data produces the correct monthly-discounted present values and totals.