Writing an If Statement to calculate monthly payments using date fields

[jmlabelle65]

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))
 

[IraWatt]

Hey @jmlabelle65,

Would you be able to give an example input and output?

Thanks,

Ira

[jmlabelle65]

See attached

[jmlabelle65]

Really just looking for help with the syntax and a push in the right direction, I am eager to solve this

[chukleswk · Accepted solution]

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.

I hope that this helps!

 

[jmlabelle65]

This is great - I have a lot more records with different start and end dates but i should be able to use a join and replace the Nulls with 0s

Great learning exercise for me, thank you.

[chukleswk]

Not a problem. If this solution works for you please select it as a solution so that others can find it as use it!

[chukleswk]

The data cleansing tool will replace all of your Nulls with 0s, which is why I put it in there.