I have to model in multiple leases that are all being signed up at different times of the year (monthly) in the next five years (see start date column) with the variables being, # of units per lease, cost per unit, when the lease payments start, term of the lease in years, renewal option, annual rent escalation .
I want to be able to have an lease expense line on a monthly basis such that its a summation of all the leases that are being signed up every year, factoring in rent escalation of 3%,
so if a leas starts in 1/1/2020, with three month rent free, i want the lease expenses to start in 4/1/2020 which is 100 (units) x $1,600 and then goes on for the next five years with an option to renew based on a stated term (not shown in the table, but i will add later). I have a detailed worksheet tab where i have dates laid out horizontally in m/d/yyyy basis such that i can do a lookup based on start date. If there is renewal (let's say for another 3 year), based on the toggle switch (yes/no), i want to be able to renew it for another 36/48/60... months. Is there an easier way to incorporate all these variables in one line for all the leases without creating a waterfall? Any help would be appreciated.
So 1) i want to create a dynamic lease expense for each lease incorporating these variables and if possible, consolidate all the leases beginning at various times in one line without having to do a waterfall.
I want to be able to have an lease expense line on a monthly basis such that its a summation of all the leases that are being signed up every year, factoring in rent escalation of 3%,
so if a leas starts in 1/1/2020, with three month rent free, i want the lease expenses to start in 4/1/2020 which is 100 (units) x $1,600 and then goes on for the next five years with an option to renew based on a stated term (not shown in the table, but i will add later). I have a detailed worksheet tab where i have dates laid out horizontally in m/d/yyyy basis such that i can do a lookup based on start date. If there is renewal (let's say for another 3 year), based on the toggle switch (yes/no), i want to be able to renew it for another 36/48/60... months. Is there an easier way to incorporate all these variables in one line for all the leases without creating a waterfall? Any help would be appreciated.
So 1) i want to create a dynamic lease expense for each lease incorporating these variables and if possible, consolidate all the leases beginning at various times in one line without having to do a waterfall.
| Start Date | Term | Renewal | # of units | Cost per unit/Month | Inflation | Free Rent |
1/1/2020 | 5 Yrs | No | 100 | $1,600 | 3.0% | 3 Mths |
2/1/2021 | 5 Yrs | No | 100 | $1,600 | 3.0% | 3 Mths |
5 Yrs | No | 100 | $1,600 | 3.0% | 3 Mths | |
5 Yrs | No | 100 | $1,600 | 3.0% | 3 Mths | |
5 Yrs | No | 100 | $1,600 | 3.0% | 3 Mths | |
5 Yrs | No | 100 | $1,600 | 3.0% | 3 Mths | |
5 Yrs | No | 100 | $1,600 | 3.0% | 3 Mths | |
5 Yrs | No | 100 | $1,600 | 3.0% | 3 Mths | |
5 Yrs | No | 100 | $1,600 | 3.0% | 3 Mths | |
5 Yrs | No | 100 | $1,600 | 3.0% | 3 Mths | |
5 Yrs | No | 100 | $1,600 | 3.0% | 3 Mths | |
5 Yrs | No | 100 | $1,600 | 3.0% | 3 Mths | |
5 Yrs | No | 100 | $1,600 | 3.0% | 3 Mths | |
5 Yrs | No | 100 | $1,600 | 3.0% | 3 Mths | |
5 Yrs | No | 100 | $1,600 | 3.0% | 3 Mths | |
5 Yrs | No | 100 | $1,600 | 3.0% | 3 Mths | |
5 Yrs | No | 100 | $1,600 | 3.0% | 3 Mths | |
5 Yrs | No | 100 | $1,600 | 3.0% | 3 Mths | |
5 Yrs | No | 100 | $1,600 | 3.0% | 3 Mths |
| | | |
