How to generate Cashflow dates and values automatically from a set of Data?

So I am trying to create a returns tracker for my Insurance investments.

I have created a master list in the first sheet and accordingly I am creating cashflow of each policy in the 2nd sheet.

This process of manually entering the dates as per the cashflow is quite tedious and I am sure there is a better way to manage this.

Requesting if you can kindly have a look and see how to create this?

I have created 2 policies, 1 with maturity payout at the end of the policy, and another with payouts every year after the premium term.

Here is the link to Google sheet - you can view and create a copy of it.

>This process of manually entering the dates as per the cashflow is quite tedious and I am sure there is a better way to manage this.

Example: Policy XXXX1245S

1. "Year" column is a range of dates.
   • First date is the "Start Date" in Cashflow sheet'!J5
   • Dates increment by 1 year
   • Final date is x years after the "Start Date" where x = "Policy term" 'Cashflow sheet'!J9
2. Outflow is a number based on the "Premium amount" 'Cashflow sheet'!J3
   • Outflow is included on the "Start Date" and for y more years, where y = "Prem. pmt term" 'Cashflow sheet'!J8

Named Ranges

To make the formula easier to follow I've used named ranges

Formula

The following formula will dynamically create the first three columns ^{<img src="https://i.stack.imgur.com/o2bPu.png" width="200" />} with as many rows as is required and properly populated. If the logic for the "Inflow" column was better understood, it too could be populated in the same formula. Frankly the entire table including the information at the top could probably be populated from one formula based solely on the "Policy No." if all the required data is stored in the Master List sheet.

={ "Cashflow", "Year", "Outflow";
    MAP(SEQUENCE(pol+1), LAMBDA(s, 
        { s,(EDATE(start, (s-1)*12)), IF(s<=pmt,-prem,0) })) }

<img src="https://i.stack.imgur.com/w0BOJ.png" width="600" />

1. A LAMBDA function is used to create the array.
2. A SEQUENCE of numbers representing policy term years is generated and MAP into the LAMBDA function which applies the formula once for each value in the SEQUENCE
3. "Cashflow" column is equal to the position in the SEQUENCE, so the mapped value s is placed in the first column.
4. In the "Year" column EDATE is used to offset the "Start Date" by the number of years equal to the mapped value (s-1)*12. The years are multiplied by 12 because EDATE offsets in months.
5. Finally, the "Outflow" column is populated with the "Premium Amount" if the mapped value s is <= to the "Prem. pmt term". If not, the "Outflow" column is set to zero.

&nbsp;

&nbsp;

Additional Info

The SEQUENCE function creates an array of specified dimensions, populated with a number sequence starting at a specified value, and incrementing (stepping) by another specified value. The only required value, however, is rows (height).

Syntax: SEQUENCE(rows, [columns], [start], [step])

=SEQUENCE(5)       // rows=5, cols=1, start=1, step=1
={1;2;3;4;5}           

=SEQUENCE(2,4)     // rows=2, cols=4, start=1, step=1
={1,2,3,4;5,6,7,8}

The MAP function enables mapping a set of values into a LAMBDA function. Inside the LAMBDA function, a formula can be applied iteratively to each mapped value. The LAMBDA declares one (arbitrary) name (variable) for each set of mapped values which the formula uses in order to refer to the current mapped value.

# one set of values mapped
=MAP(values, LAMBDA(name, formula))

# two sets of values mapped
=MAP(values1, values2, LAMBDA(name1, name2, formula))

So, looking at the initial MAP and LAMBDA parts of the formula:

# - 'pol+1' is sequenced instead of 'pol' because
#    it was noted that the date list was always 1 
#    row longer than the number in "Policy term" 
# - 's+0' is the 'formula' in this example just to 
#    avoid confusing the 'name' and the 'formula'
# - 'pol'=4 in this example
# - 'MAP(values, LAMBDA(name, formula))'

=MAP(SEQUENCE(pol+1), LAMBDA(s, s+0) 
={ 1; 2; 3; 4; 5 }

Now to build the 3 column array:

• The first row is set as { "Cashflow", "Year", "Outflow" }
• Each row has s in the "Cashflow" column:
  { 1; 2; 3; 4; 5 }
• the "Start date" start, offset by s-1 years, goes in column 2
• start is offset s-1 years instead of s years because start is always the first date in the table, therefore the first calculation must be start+0yr not start+1yr:
  { start+0yr; start+1yr; start+2yr; start+3yr; start+4yr }
• lastly, the negative "Premium amount" (-prem) is placed in column 3, but only if the date in column 2 falls within the "Prem. pmt term" (pmt), otherwise a 0 is placed instead

# - Instead of 'start+(s-1)years', as EDATE
#   offsets in months (not years), the calculation
#   is adapted by multiplying by 12:
#   '(EDATE(start, (s-1)*12)'
# - 'pol'=4, 'start'=2/28/2021,
#   'prem'=199, `pmt`=3   

={ "Cashflow", "Year", "Outflow";
    MAP(SEQUENCE(pol+1), LAMBDA(s, 
      { s,(EDATE(start, (s-1)*12)),
        IF(s<=pmt,-prem,0)         }
    ))
 }

= { "Cashflow", "Year", "Outflow";
    1, 2/28/2021, -199;
    2, 2/28/2022, -199;
    3, 2/28/2023, -199;
    4, 2/28/2024, 0;
    5, 2/28/2025, 0 }