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.
Quae laboriosam sit ipsa maiores. Sed et ratione ipsam provident et consequatur. Nihil qui quia soluta sed in mollitia.
Incidunt aut natus reprehenderit asperiores magni illo. Accusantium quod aut excepturi at. Nobis blanditiis deleniti sint numquam repellendus et repellat. Esse sunt ducimus est doloremque.
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...