You will have to create a SYS TIME module and have a mapping between Native Time and Counter Time. For Example Jun 2020 will be Month 10 something like that. Once done you can easily use LOOKUP based on the mapping and pull the values. Let me know if you want me to create an example for you
@Sorna Raja Prabhu has created an example for you already. The only thing I would add is if you have a logic you can automate the mapping between Native Time and Counter Time, if not then you can manually map these two
I think we need to create a staging module with Payment Scheme, Time, and Counter Time as lists. This will enable us to calculate the installment for each principal amount. The final module will summarize this staging module.
From your example, I notice that percentage split is always input from Month 1, no matter the real Month when the principal amount is issued
The first staging module is to perform the calculation for each principal
The second staging module is to summarize the payment for each Counter Time using TIMESUM
The result module needs to convert the Counter Time to Anaplan Time using SUM. Here is shown the Oct 19 - Dec 19 (which corresponds to Oct 18 - Dec 18 in your example)
In addition, we need the mapping module that @Sorna Raja Prabhu showed, and for my example the reverse mapping module which are derived by formula from the original mapping
My apologies for the late reply on your response. And I want to say thank you for taking time on answering the query. I would like to ask something about your solution particularly on the table below. I can see that the Counter Time column on the list was already set to Month 1 - Jan 19. I already had the Column Counter time no ready. Just want to ask, did you set the Month 1 as Jan 19 on the list.? I mean did you assign it manually on this table below? What if there is a value on "Principal" which is Sep 16, does this mean Sep 16 should be tagged as Month 1?
I am currently applying your solution to the set up that we have already. Just need some clarifications on this.