Create a monthly Amortization Schedule from Contract amount, start, end and booked date.

[BrianGBennett]

Thank you in advance for the help.

I have data in which each row is a contract line. It has the contract start date, end date, created date and contract value.  My company realizes contract revenue on a monthly amortized basis. As in, a 1 year contract booked on (or before) 01JAN2020 for $12,000 with contract line start date of 01JAN2020 and end date of 31DEC2020 will yeild $1,000 in revenue for each of the 12 months in 2020. 

However, if the contract is booked after the start month, the back revenue is realized in the month it is booked.  for the example above, if I booked the same contract on 01FEB2020 than the February revenue would be $2,000 and the remaining months would be $1,000.

I'd like to create a work flow which converts my report of contract lines into a monthly amortization report.

Input-

Contract Number	Contract Line number	Start Date	End Date	Created Date	Amount
123345	10	1/1/2020	12/32/2020	1/1/2020	12000
12346	10	1/1/2020	12/32/2020	2/1/2020	12000
12347	10	1/1/2020	12/32/2020	12/1/2019	12000

Desired output-

Contract	Line	Jan-20	Feb-20	Mar-20	Apr-20	May-20	Jun-20	Jul-20	Aug-20	Sep-20	Oct-20	Nov-20	Dec-20
123345	10	1000	1000	1000	1000	1000	1000	1000	1000	1000	1000	1000	1000
12346	10	0	2000	1000	1000	1000	1000	1000	1000	1000	1000	1000	1000
12347	10	1000	1000	1000	1000	1000	1000	1000	1000	1000	1000	1000	1000

Thank you for the help. 

Also, the list of contracts will overlap time frames. so, it will need to return a 0 for revenue during a month the contract is not active of course. 

[RolandSchubert · Accepted solution]

Hi @BrianGBennett ,

the first step is to convert the dates to date format to enable calculation. I calculated the number of periods to divide the total amount by number of periods, to identify monthly amount. I used Formula tool / Multi-Row Formula tool to assign the amounts to periods. Cross-Tab tool moves values to columns.

I've attached a sample workflow. Hope this is helpful.

Best,

Roland

[BrianGBennett]

I am still digesting it a bit, but so far this looks perfect. the looping of the period calculation is perfect, that is where I was stuck. Awesome work!  Thanks for the fast reply!

[BrianGBennett]

Ok, I realized I need to have it calculate a daily rate rather than a monthly rate but still only apply the monthly revenue if booked before the end of the month.  I think I only need to change the per period caclution and then the calculation to count the periods. should work?

[RolandSchubert]

I think it should work, assume you would change the period to day (and calculate amounts for days as well) and run an aggregation to monthly level before moving rows to columns, should require only small modifications.