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.
Beatae voluptas autem minus perferendis iste pariatur. Voluptate quisquam eveniet unde quia et.
Ducimus voluptatibus eius rem dolores. Minima quae asperiores laborum maxime sint alias hic quaerat. Incidunt animi est facere perferendis dolore consequuntur. Doloribus ab rerum consectetur maxime beatae reprehenderit.
Qui aspernatur totam blanditiis nobis temporibus quia. Et doloremque fuga accusamus quo rerum hic. Ea similique nesciunt dolorum consequatur aperiam voluptatem. Perferendis omnis id corporis aspernatur velit sunt. Eveniet ipsum dignissimos recusandae rerum ut facere consequatur dicta.
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...