Writing an If Statement to calculate monthly payments using date fields

How would I write an if statement to calculate a monthly payment in a new field

For example

Let's say I have a record where the [payment start date] is 1/1/2022 and the [payment end date] is 12/31/2022 and the [monthly payment] is $1,000

I want to set up new fields for each month (Jan - Dec) using the Formula function

Just beginning but here is what i have so far

if([PaymentStartDate]<'01-01-2022'
AND
[PaymentEndDate]>'01-31-2022'
then [PaymentAmount]
else 0
endif

I would also like to be able to calculate prorated payments as well, so any ideas would be very helpful. For example - calculate a payment for 1/15/2022 - 1/31/2022 where the monthly payment is $1,000 ($1,000/EOMday * (EOMday-Startdateday+1))

Date Time

Accepted answers

chuckleswk

It's not going to be as simple as writing a simple if statement. You can accomplish this with the following flow. The generate row will generate all of the rows that you need but because you are wanting to start on the 1st of the month you will have to create an adjusted start date for those that do not have a PaymentStartDate of the 1st of the month. Once that is done you can use the Generate Row tool in order to create all of your subsequent date. After that you can use the formula tool to create a corrected payment amount. This will be because you have dates that are not starting on the 1st of the month and you also want the full amount to be paid on the following months.