Disbursement scenarios

Antoine74 

To avoid retyping data for each scenario you want to test, you can use the Data Table feature in Excel.

Here's how you can set it up:

1. Create a table that contains the inputs you want to test. For example, you could have a column for the investment amount, a column for the start date, and a column for the number of months over which the investment will be spread.
2. Set up your cash outflow calculations in a separate table or range of cells. Use cell references to refer to the input values you want to test. For example, if your investment amount is in cell A1, your start date is in cell B1, and your investment period is in cell C1, you might have a formula like this: =-A1/C1 in cell D1, which calculates the monthly cash outflow.
3. Select the range of cells that contains your input values (including the headers) and go to the "Data" tab in the Excel ribbon. Click on "What-If Analysis" and then select "Data Table".
4. In the "Data Table" dialog box, enter the cell reference for the output of your cash outflow calculations (e.g. D1) in the "Row Input Cell" box. This tells Excel which cell to update with the output values for each scenario.
5. Enter the cell references for the input values you want to test (e.g. A1:C1) in the "Column Input Cell" box. This tells Excel which cells to vary in order to test different scenarios.
6. Click "OK" and Excel will generate a table that shows the output values for each combination of input values you specified.

By using a data table, you can quickly test different scenarios by changing just a few input values, without having to manually update all of your cash outflow calculations.

7.       Or…Using an IF statement without repeating a long formula (LET)

Let's assume you have the following input values:

Investment amount: $100,000

Start date: June 1, 2024

Investment period: 24 months

You can define variables for these values using the LET function, like this:

=LET(
    investment_amount, 100000,
    start_date, DATE(2024, 6, 1),
    investment_period, 24,
    monthly_outflow, -investment_amount/investment_period,
    monthly_dates, EOMONTH(start_date, 0)+1+SEQUENCE(investment_period,1,0,1),
    cash_outflow, IF(monthly_dates<=TODAY(), monthly_outflow, 0),
    cash_outflow
)

Hope I could help you with these information / link.