Real estate lease calculation

[juul2222]

I’m trying to calculate the NPV of a lease that has annual % rent increases. I want to set up a template that I can change the number of years of the lease, % rent increases, rent amount in cost per square foot, and the number of square feet in the lease without having a schedule of each individual year’s payments. Currently, all I can figure out how to do is create a schedule but that means I have to have different schedules for different length leases, which isn't very concise.

 

[Andrew Fergus]

Hi and welcome to MrExcel

To do an NPV calculation (assuming that is what you want*) then you need the periodic cash flows; there is no getting away from constructing a schedule of payments that go with each lease. To calculate a future value with just a single formula and the input values you described is easy, whereas an NPV requires the schedule of cashflows.

*Given there is no initial investment (or negative cash outflow) all values will be positive - as to whether it is "good" or "bad" will depend on the rate of rent inflation versus your discount factor - this assessment as to whether or not it is good can be done without reference to the actual cash flows, i.e. Disc Factor > 3% is bad, Disc Factor < 3% is good.....hence my statement earlier wondering if NPV is what you want....?

Andrew

 

[juul2222]

Yes, it is the NPV I'm trying to calculate. Currently, I have different schedules for different terms (i.e. one schedule for a 5 year lease, one schedule for a 10 year lease, etc.). During planning exercises, it would be nice to create a more concise table that I could manipulate the lease term and have an NPV formula that would derive at the figure "automatically" without a schedule (something similar to below) whereas you NPV and Total capital required are formulas. I can compound percentage increases to determine the last year's payment using =R8*(1+R9)^(R10-1), but that doesn't compound the total payments made over the duration of the lease.

Rentable SF
Yr 1 Base Rent ($)	$ 12.25
Lease term (years)
Annual rent increase (%)	2%
NPV @ 6.6%
	TI Allowance
	Total Capital required

<tbody>
</tbody><colgroup><col><col><col></colgroup>

 

[Andrew Fergus]

Understood but per my earlier post there is no smart formula that can do this without reference to the periodic cash flows. In other words you need to build the table of cashflows. I recommend you have a hidden sheet with formulas for each period, and provided the period number is less than or equal to the years x 12 then generate the cash flow, otherwise set it to zero. Then do the NPV formula over that column on the hidden sheet.