Rent Amortization Table

Question

Hello everyone,

I am looking for help populating out a rent schedule for several leased properties. I have a table with the length of the lease, the total rent, and rent values & durations in months for each rent step, like the following:

MonthsTotal Rent

Rent Step 1Step 1 DurationRent Step 2Step 2 DurationRent Step 3Step 3 Duration...484059001100031127531155012 36472920

127501213135121352512 19750975010000

I need these steps broken out month-by-month, so that there is a column for rent each month at each of the properties, like so:

MonthsTotal RentMonth 1Month 2Month 3Month 4Month 5Month 6Month 7...4840590011000110001100011275112751127511550 3647292012750127501275012750127501275012750 197509750000000

Both the length of the rent steps and the total number of steps in the contract vary per property.

Any help would be greatly appreciated!

Luke_C · Accepted Answer

Hi @MatthewK

I put together a quick sample on how you could accomplish this.

* First data is transposed to get the step info as rows rather than columns
* Then I use the filter to separate the durations from the rent
* I update the fields to be standard 'Step X' rather than step 1 rent, step 1 duration, etc. This comes into play later on.
* Then I generate a row for each month of the step duration, and use a multi-row formula tool to get the overall month order correctly.
* Then I join that data up with the rent information and cross tab.
* The dynamic rename adds 'Month' to the column headers.

Let me know if this works for you or if you have any questions.