excel_lambdas_freqconvert.txt

/*
    It converts an amount of a period from a frequency to another.
    Usually used to calculate periodic billing.
    
    Inputs:
        - from_freq [string with input amount frequency]
        - to_freq [string of frequency to convert to]
        - amount [input amount for the given frequency]
        
    Supported frequencies:
        - day or dayly
        - week or weekly
        - month or monthly
        - year or yearly
        - 2-years
        - 3-years
        
    Non-supported frequencies or blank entries will produce an #N/A error.
    Use IFERROR(FREQCONVERT(...),"") to produce a cleaner result or add it to lambda function.
    
    Days in a month, weeks in a year, etc. are averages.
    They are set in variables in case it is required to change them for specific purposes.
*/

FREQCONVERT = LAMBDA(from_freq, to_freq, amount,
    LET(
        _from_freq, SUBSTITUTE(LOWER(from_freq), "ly", ""),
        _to_freq, SUBSTITUTE(LOWER(to_freq), "ly", ""),
        days_in_week, 7,
        days_in_year, 365,
        days_in_month, 30.4167,
        weeks_in_year, 52.1429,
        weeks_in_month, 4.34524,
        months_in_year, 12,
        IF(
            _from_freq = _to_freq,
            amount,
            SWITCH(
                _to_freq,
                "day",
                SWITCH(
                    _from_freq,
                    "3-years",
                    amount / days_in_year / 3,
                    "2-years",
                    amount / days_in_year / 2,
                    "year",
                    amount / days_in_year,
                    "month",
                    amount / days_in_month,
                    "week",
                    amount / days_in_week
                ),
                "week",
                SWITCH(
                    _from_freq,
                    "3-years",
                    amount / weeks_in_year / 3,
                    "2-years",
                    amount / weeks_in_year / 2,
                    "year",
                    amount / weeks_in_year,
                    "month",
                    amount / weeks_in_month,
                    "day",
                    amount * days_in_week
                ),
                "month",
                SWITCH(
                    _from_freq,
                    "3-years",
                    amount / months_in_year / 3,
                    "2-years",
                    amount / months_in_year / 2,
                    "year",
                    amount / months_in_year,
                    "week",
                    amount * weeks_in_month,
                    "day",
                    amount * days_in_month
                ),
                "year",
                SWITCH(
                    _from_freq,
                    "3-years",
                    amount / 3,
                    "2-years",
                    amount / 2,
                    "month",
                    amount * months_in_year,
                    "week",
                    amount * weeks_in_year,
                    "day",
                    amount * days_in_year
                ),
                "2-years",
                SWITCH(
                    _from_freq,
                    "3-years",
                    amount / 3 * 2,
                    "year",
                    amount * 2,
                    "month",
                    amount * months_in_year * 2,
                    "week",
                    amount * weeks_in_year * 2,
                    "day",
                    amount * days_in_year * 2
                ),
                "3-years",
                SWITCH(
                    _from_freq,
                    "2-years",
                    amount / 2 * 3,
                    "year",
                    amount * 3,
                    "month",
                    amount * months_in_year * 3,
                    "week",
                    amount * weeks_in_year * 3,
                    "day",
                    amount * days_in_year * 3
                )
            )
        )
    )
);

/*
    It gets the due date given the last payment and frequency.
    
    Inputs:
        - last_pay_date [date]
        - freq [string of payment frequency]
        
    Supported frequencies:
        - day or dayly
        - week or weekly
        - month or monthly
        - year or yearly
        - 2-years
        - 3-years
        
    Non-supported frequencies or blank entries will produce an #N/A error.
    Use IFERROR(DUEDATE(...),"") to produce a cleaner result or add it to lambda function.
*/

DUEDATE = LAMBDA(last_pay_date, freq,
    LET(
        _freq, SUBSTITUTE(
            LOWER(freq),
            "ly",
            ""
        ),
        days_in_week, 7,
        days_in_year, 365,
        days_in_month, 30.4167,
        SWITCH(
            _freq,
            "3-years",
            last_pay_date + days_in_year*3,
            "2-years",
            last_pay_date + days_in_year*2,
            "year",
            last_pay_date + days_in_year,
            "month",
            EDATE(last_pay_date,1),
            "week",
            last_pay_date + days_in_week,
            "day",
            last_pay_date + 1
        )
    )
);