Calculation of Payment Frequecy

Hi All,

I've one use case where I've to calculated payment according to payment frequency i.e monthly, annually and quarterly.

I've system module dimensioned by lease where user enters lease start date, end date and payment frequency.

For e.g. Sr. No Start date End Date Payment frequency

Lease1 4th-May-2012 12th-july-2022 Quarterly

But I am facing issue to calculate payment because we are using only current year, Previous year and Future years. But in our case it's starts from 2012. So How we decide the quarter month in 2021 and tick that months accordingly. We need it by Month. So target module dimensioned by Time(Months), Lease list.

Can anyone help on this please?

Thanks

Ashutosh

Accepted answers

luke_e

I assume you're using something like ADDMONTHS() in a way to determine the interval to use as the pay period, but you probably need to establish a basis or anchor on your timescale before the calculations work properly.

I don't know how you treat actuals but you could use something like DATE() as a way to get the anchor. Long story short, you could try,

1. DATE(YEAR(<Previous Year>),MONTH(<START DATE>),DAY(<START DATE>)) which will give you an anchor of your start date, but in the first year of your timescale.

2. Using this anchor, apply whatever frequency calculations to determine whether a payment is applicable

3. Ensure you have boolean logic that blocks the payment if the next payment date (or the anchor date) exceeds the end date.

This is obviously on the basis that historical periods are actual and don't need to be calculated in the same way.

All comments

Alexandru.Rosca

@Ashutosh I think you may find an answer on the other topic I've replied today:

https://community.anaplan.com/t5/Anaplan-Platform/inquiry-on-how-to-execute-time-ranges-to-minimize-model-size/m-p/140460/highlight/true#M37141

I would go with a time range that includes all your lease periods, in your example: 2012-2022 (you can also use future years). Then create a calculation module where you will apply this new time range and calculate your lease payment plan in it. But avoid using other dimensions as it will dramatically increase the size.

Will you use a graphical display anywhere else in an app? Do you need to have months ticked, or you just need to see what is total payment in a particular month? If so, you will be able to achieve this by comparing current month with end date. If it's <= then you need to pay. You can also map months to pay for quarterly and yearly payments in a system module.

Ashutosh

Hi Alex,

Thank you for response. But We can't uses time ranges because sometimes selected date comes in past 20 years and it will increase size drastically.

That's the main problem. Is any other way to work around using formulas?

Regards

Ashutosh

Alexandru.Rosca

- What is the final result? you want to calculate full payment? or total monthly payments for your leases?

- if it's a quarter or yearly payment, how you decide what is the month you will be paying on?

luke_e

I don't know how you treat actuals but you could use something like DATE() as a way to get the anchor. Long story short, you could try,

1. DATE(YEAR(<Previous Year>),MONTH(<START DATE>),DAY(<START DATE>)) which will give you an anchor of your start date, but in the first year of your timescale.

2. Using this anchor, apply whatever frequency calculations to determine whether a payment is applicable

3. Ensure you have boolean logic that blocks the payment if the next payment date (or the anchor date) exceeds the end date.

This is obviously on the basis that historical periods are actual and don't need to be calculated in the same way.

Quick Links

Can you help?

Unanswered questions

Request for Workflow Design: End-User Report Finalization & Stakeholder Notification with Version Co
Hi everyone, I'm looking to design a workflow and would appreciate your input or guidance on how to set this up effectively in Anaplan. So, the goal is: Once end users complete their data input, they should be able to trigger a process that: Notifies stakeholders via email with a link to a report. The report includes: A…
OAUTH 2.0 vs Google Sheets Extension
Hi, My org is looking to explore leveraging Google Apps script to extract data out of Anaplan and update already built templates in Google Sheets. We use the Anaplan Extension currently but find it crashes often with the amount of tabs, data, etc. Has anyone used both Anaplan OAUTH 2.0 API and Goolge Apps Script to write…
Exports with anaplan using informatica connector
I'm trying to execute an Action to export data with the use of a Mapping in Informatica. But it gives us null rows .If we export directly from Anaplan , the export file has all the rows filled. Both exports have same number of rows but in the informatica export the values appear as blank. This issue is with only some…