Rent Amortization Table

[MatthewK]

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:

Months	Total Rent	Rent Step 1	Step 1 Duration	Rent Step 2	Step 2 Duration	Rent Step 3	Step 3 Duration	...
48	405900	11000	3	11275	3	11550	12
36	472920	12750	12	13135	12	13525	12
1	9750	9750	1	0	0	0	0

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:

Months	Total Rent	Month 1	Month 2	Month 3	Month 4	Month 5	Month 6	Month 7	...
48	405900	11000	11000	11000	11275	11275	11275	11550
36	472920	12750	12750	12750	12750	12750	12750	12750
1	9750	9750	0	0	0	0	0	0

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 solution]

Hi @MatthewK 

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

1. First data is transposed to get the step info as rows rather than columns
2. Then I use the filter to separate the durations from the rent
3. I update the fields to be standard 'Step X' rather than step 1 rent, step 1 duration, etc. This comes into play later on.
4. 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.
5. Then I join that data up with the rent information and cross tab.
6. The dynamic rename adds 'Month' to the column headers. 

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

[MatthewK]

Thank you Luke, this worked like a charm! Exactly the functionality I was looking for.