To calculate the Net Effective Rent (NER) in Excel, you will typically follow a formula that takes into account the total cost of the lease over its term, including any concessions like free months of rent, and then divides that by the total term of the lease to get the average monthly cost.
Here's a step-by-step guide on how to set up a Net Effective Rent calculation in Excel:
- Input the total monthly rent amount: This is the amount the tenant will pay each month for the lease term, excluding any rent-free periods.
- Input the lease term: This is the total length of the lease, typically in months.
- Input any rent concessions: For example, if there are months of free rent or a lump sum given back to the tenant, input this value.
- Calculate the total cost of the lease: This is the monthly rent amount multiplied by the lease term.
- Subtract any concessions from the total cost: This will give you the net cost of the lease.
- Divide the net cost by the total lease term: This gives you the Net Effective Rent, which is the average monthly cost over the term of the lease considering the concessions.
Here is a simple example of the calculation in Excel format:
The formula in Excel for Total Lease Cost without concessions would be:=B1 * B2
The formula for Total Concessions would be:=B1 * B3 + B4
The formula for Net Lease Cost with concessions would be:= (B1 * B2) - (B1 * B3 + B4)
Finally, the formula for Net Effective Rent would be:= ((B1 * B2) - (B1 * B3 + B4)) / B2
You would input each of these formulas into their respective cells to get the calculations you need.
Remember to replace B1, B2, B3, and B4 with the actual cell references in your Excel spreadsheet that correspond to the Monthly Rent, Lease Term, Free Months, and Lump Sum Credit, respectively.
Learn more about how we extract Concessions from text and compute Net Effective Rent for you in HelloData.ai on our article
