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 ))
Maybe I am not following your post, but couldn't OP just set up a table with column 1 being Year 1, Year 2.....Year 10 and column 2 being the CPI projection?
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).
Yes, I figured this out, thanks for the input. It was a 99 year ground lease with 5-year CPI increases and two FMV adjustment periods. I just created a rough income waterfall for each period and used a total line as the toggle point for the rest of the model.
Nemo aut nam et quaerat. Similique quia quo et alias. Beatae cum aut cupiditate quaerat dolores dolore.
Quae sed rerum et. Eius animi libero voluptate rerum deleniti rerum et quis. Distinctio accusamus quae quod et molestias unde maiores. Quo sed illo voluptatem illo repellendus et.
Et dolores est quaerat soluta asperiores. Qui eius minus est corporis ex.
Velit autem ex nulla. Ex architecto non et nesciunt aperiam. Et recusandae voluptatem ipsam est et. Dignissimos voluptas dolores blanditiis ex libero ab. Beatae nihil dolorem modi molestiae necessitatibus a tenetur.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...