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
Best Answer
-
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.
1
Comments
-
@Ashutosh I think you may find an answer on the other topic I've replied today:
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.
1 -
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
0 -
- 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?
0
