Formula Help

Ashutosh

Hi Everyone,

 

I’m trying to do something really simple but failing. I’ve got a list of loans and each loan has a starting date. Interest is payable every x months from the starting date. I want to create a Boolean every x months from the starting date. How do I do this? I can put a boolean in the first repayment date but then I’m failing.

Example:

Loan 1 has a start date of 11 March 2020 and interest is payable every 3 months. I want a tick in 11 June 2020, 11 Sep 2020, 11 Dec 2020, 11 March 2021 etc.
Loan 2 has a start date of 20 November 2019 and interest is payable every 5 months. I want a tick in 20 Apr 2020, 20 Sep 2020, 20 Feb 2021 etc.

 

Time scale - In days

 

Regards

Ashutosh

M.Kierepka

Hi @Ashutosh,

So I assume you have line items like:
Start = start date of your loan, dimensioned by list of loans
Frequency = every how many months payment has to happen, dimensioned by list of loans
Day = Current day (like ITEM(Time), dimensioned by Time

Then to get boolean (dimensioned by Time and list of loans):

Day >Start AND  MOD(YEAR(Day) * 12 + MONTH(Day) - (YEAR(Start) * 12 + MONTH(Start)), Frequency) = 0 AND DAY(Start) = DAY(Day)

EDIT: DISCLAIMER: It won't work perfectly for dates with day 29,30,31, because months have different lengths. However, if you need such colution, use DAYSINMONTH function to calculate it properly. Then have line item 'last_day_of_month' (Boolean, only dimension of Time) with formula:
DAYSINMONTH(YEAR(Day),MONTH(Day)) = DAY(Day)
And then upgrade your formula to:
Day >Start AND  MOD(YEAR(Day) * 12 + MONTH(Day) - (YEAR(Start) * 12 + MONTH(Start)), Frequency) = 0 AND (DAY(Start) = DAY(Day) OR (DAY(Start) > DAY(Day) AND last_day_of_month))