Search code examples
google-sheets

How can I automatically generate due dates in Google Sheets based on payment plans?

I'm trying to create an automated system in Google Sheets where I have a list of payment plans, and I want to generate due dates for each installment based on the following columns:

Column B: Plan (Description) Column C: Frequency (Months) Column D: Number of Installments Column E: Amount ($) Column F: Start Date I want the due dates to be calculated automatically based on the Frequency (Months) and Start Date, and the Number of Installments determines how many due dates should be generated.

I’m having trouble with creating the formula that will correctly generate the due dates in a separate table (for example, starting at cell H4). Each payment plan will have its own sequence of due dates.

For example:

Plan A starts on 01/01/2023, with a frequency of 3 months and 6 installments.

The due dates should be: 01/01/2023 01/04/2023 01/07/2023 01/10/2023 01/01/2024 01/04/2024 Plan B starts on 01/02/2025, with a frequency of 2 months and 24 installments.

The due dates should be: 01/02/2025 01/04/2025 01/06/2025 And so on, until 24 installments. How can I write a formula that will generate these due dates in a list, automatically filling each row in the due dates table (in columns H to K)? I'm specifically struggling with how to handle the frequency and number of installments, so I don’t have to manually enter each date.

I’ve tried using SEQUENCE and ARRAYFORMULA, but I’m running into issues where the due dates don’t populate correctly, and I end up with repeated or incorrect values.

Thank you for your help!

Edit: Here's an example input and output, created with the tool provided in the comments:

Descripcion Frecuencia (Meses) Cantidad de Cuotas Monto ($) Fecha de Inicio Fecha de Venc. Descripción Monto ($)
Plan A 3 3 $15,000.00 1/1/2023 1/1/2023 Plan A (1/3) $15,000.00
1/4/2023 Plan A (2/3) $15,000.00
1/7/2023 Plan A (3/3) $15,000.00
Solution

  • Use map(), like this:

    =map(sequence(C2, 1, 0), lambda(i, let( 
  j, i * B2, 
  hstack( 
    edate(E2, j), 
    join("", A2, " (", i + 1, "/", C2, ")"), 
    D2 
  ) 
)))

    screenshot

    See map(), sequence(), lambda(), let(), hstack(), edate() and join().