Ground Lease Modeling Question

This is kind of a dumb question but I can't figure it out. I'm trying to model a 99-year ground lease payment stream where the percentage increases at various 10 year intervals can be changed dynamically based on different CPI projections. I can't figure out how to model the cash flows without an absurd if statement that basically says:

initial payment*if(current period is in this window, 1+Xrate increase,1), (other current period,)....etc.

Anyone done this before and found a simpler way?

 

Assuming you want different CPI projections every 10 years: Set up a table with first column as Year n: Year 1 to Year 10, second column as CPI projections. write out the n interval at the top like so 1 1 1 1 1, 2 2 2 2 2, ...., 10 10 10 10 10. then you would use for your formula = payment * index(second column CPI projections, match(n interval at top, first column Year 1 to Year 10, 0 ))

 

You could do it any number of ways. As mentioned below, set up an input table off to the side/another sheet or location where you have the year and then the CPI projection. Is it cumulative or just based off of the preceding year? (i.e., year 10 to 11 CPI jump or cumulative CPI from years 1-10?). Also, are you sure this is CPI, and not a percentage of the FMV of the land? This is how I've seen most rent resets on ground leases, not CPI (at least in my markets).

"Who am I? I'm the guy that does his job. You must be the other guy."
 
Best Response

Sunt est totam atque enim. Ducimus dolor architecto fuga occaecati dolorem et quis. Provident magni non laudantium est provident aut autem.

Et est dolorem quibusdam exercitationem iste. Ipsum debitis iure reprehenderit velit omnis numquam qui corporis. Consequatur dignissimos alias quo est. Consequatur consequatur adipisci aperiam rerum ut debitis. Aspernatur ipsum dolore labore voluptas molestiae facere.

Career Advancement Opportunities

March 2024 Investment Banking

  • Jefferies & Company 02 99.4%
  • Goldman Sachs 19 98.8%
  • Harris Williams & Co. (++) 98.3%
  • Lazard Freres 02 97.7%
  • JPMorgan Chase 03 97.1%

Overall Employee Satisfaction

March 2024 Investment Banking

  • Harris Williams & Co. 18 99.4%
  • JPMorgan Chase 10 98.8%
  • Lazard Freres 05 98.3%
  • Morgan Stanley 07 97.7%
  • William Blair 03 97.1%

Professional Growth Opportunities

March 2024 Investment Banking

  • Lazard Freres 01 99.4%
  • Jefferies & Company 02 98.8%
  • Goldman Sachs 17 98.3%
  • Moelis & Company 07 97.7%
  • JPMorgan Chase 05 97.1%

Total Avg Compensation

March 2024 Investment Banking

  • Director/MD (5) $648
  • Vice President (19) $385
  • Associates (85) $262
  • 3rd+ Year Analyst (13) $181
  • Intern/Summer Associate (33) $170
  • 2nd Year Analyst (65) $168
  • 1st Year Analyst (198) $159
  • Intern/Summer Analyst (143) $101
notes
16 IB Interviews Notes

“... there’s no excuse to not take advantage of the resources out there available to you. Best value for your $ are the...”

Leaderboard

1
redever's picture
redever
99.2
2
BankonBanking's picture
BankonBanking
99.0
3
Betsy Massar's picture
Betsy Massar
99.0
4
kanon's picture
kanon
98.9
5
Secyh62's picture
Secyh62
98.9
6
dosk17's picture
dosk17
98.9
7
CompBanker's picture
CompBanker
98.9
8
GameTheory's picture
GameTheory
98.9
9
bolo up's picture
bolo up
98.8
10
DrApeman's picture
DrApeman
98.8
success
From 10 rejections to 1 dream investment banking internship

“... I believe it was the single biggest reason why I ended up with an offer...”