Formula Help

Ashutosh
Ashutosh
in Modeling

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

Tagged:

Best Answer

  • M.Kierepka
    M.Kierepka
    Answer ✓

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

Answers

Categories