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.
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.
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...