Copilot Skill for adding AmortizeDebtλ to financial models

amortizedebt

---
name: amortizedebt
description: Assists in configuring inputs for and linking them to the 5g function, AmortizeDebtλ.
---
  metadata:
    version: 1.0.0
    tags:
      - excel
      - lambda
      - afe
      - excel labs
      - named functions
      - documentation
      - 5g modeling
  triggers:
    - AmortizeDebt
    - Amortize Debt
    - Setup AmortizeDebt
context:
  terms:
    Day Count Convention: https://www.investopedia.com/terms/d/daycount.asp
    Grace Period: https://gocardless.com/guides/posts/how-a-grace-period-works
    Arrears: https://www.investopedia.com/terms/a/arrears.asp

  holiday_definition: >
    A holiday is a suspension of repayments, interest charges, or interest payments.
    Each action has its own start/stop table. Each table has one column, Date.
    The first date starts the action and may introduce a grace period. The second
    date stops the action. The third date restarts the action, and so on.

strict execution rules:
  - Ask questions one at a time and wait for response before continuing.
  - When displaying options, display them as numbered lists.
  - Use cell Styles for formatting where possible. Keep worksheets clean and professional by not applying colors independently.
  - Remove gridlines from all worksheets. Do not add borders and do not use wrapped rows.
  - Use the function help text as authoritative for defaults and validation lists; do not infer ranges.
  - If a function fails, do not try to fix it.
  - When adding data validation, put the function argument name in the Input Message Title and the argument help text description in the Input Message box.
  - Start table names with "tbl" followed by a very short name. Use abbreviations where practical.
  - Apply the default table style to all tables. Do not apply additional formatting to table headers.

workflow:
  - step 0: Ask preferred language
    actions:
        - Detect the workbook application culture/locale setting.
        - Identify the most likely language from the regional setting.
        - Ask: "Which language would you prefer to use?" in the regional setting's likely language.
        - Display options as a numbered list:
            1 English — based on your regional setting `en-US`
            2 Español
            3 Français
            4 Deutsch
            5 Italiano
            6 Português
            7 中文
            8 日本語
            9 한국어
            10 Other — type the language you want to use.
        - If the user selects a numbered option, remember that language preference.
        - If the user selects "Other", ask the user to type the language they want to use.
        - Switch to the selected language and use for the remaining steps and worksheet entries.
  - step: 1
    name: Pre-setup
    actions:
      - Alert the user that Microsoft's free Advanced Formula Environment add-in is highly recommended for importing libraries.
      - Tell the user that the following libraries should already be loaded from https://sites.google.com/site/beyondexcel/home/5g-modeling/5g-component-libraries:
          - Array Essentials (for SumRCλ)
          - Dates and Scheduling (for Timelineλ)
          - Debt Library (for AmortrizeDebtλ)
      - Ask whether these libraries are loaded. If not, tell the user to load them first.
      - Tell the user to save the workbook before continuing.
      - Add: "When ready to continue, type C and press ENTER."
      - Ask for the preferred currency abbreviation, such as usd, eur, gbp, jpy, aud, or cad.
      - Ask for the preferred currency style, such as Comma, Comma [0], Currency, or Currency [0].
      - If the Styles gallery has date styles, ask which date style to use. If not, ask the user to enter a date in the preferred style.
      - Ask: "In which column should assumption names be placed?" Remember as Argument Column.
      - Ask: "In which column should timelines start?" Remember as Timeline Column.

  - step: 2
    name: Setup single value arguments
    actions:
      - Analyze AmortizeDebtλ and determine which arguments are single value arguments.
      - Ask: "Which worksheet should hold constant arguments?"
      - List all worksheets as numbered options.
      - If InpC does not exist, include "Create InpC" as an option.
      - Include "Or enter a worksheet name to create" as an option.
      - If needed, add the worksheet.
      - If you added the worksheet, merge A1:G1, add text "Input Constants", and apply cell style Title.
      - Find space below row 3 and, starting in the Argument Column, add headings:
          - Name
          - Value
          - UOM
          - Description
      - Apply Heading 4 style to the headings.
      - In the next row of the Argument Column, list argument names in human-readable form by separating words and expanding abbreviations.
      - In the next column, add input cells and format them with the Input style.
      - Name input cells using Pascal Case.
      - Add appropriate data validation to each input cell.
      - In the next column, place units of measure:
          monetary_amounts: Use the user's preferred currency abbreviation.
          boolean_values: TRUE/FALSE
          dates: date
          rates: rate
          validation_lists: Use formula =XLOOKUP(V,VList,VText,0,0)
      - In the next column, add brief descriptions for each argument.
      - Populate defaults where defined in the function help text.
      - Check that all defaulted arguments are populated.
      - Check that all arguments have appropriate data validation.
      - Remove temporary setup notes before continuing.
      - Ask whether the user needs clarification on the arguments.
      - Provide explanations if requested.
      - Instruct the user to type C to continue when finished asking questions.
      - Ask the user to make entries into these inputs and type C, then press ENTER to continue.

  - step: 3
    name: Setup tables for multiple instance arguments
    actions:
      - Analyze AmortizeDebtλ (and ONLY AmortizeDebtλ) and determine which arguments are multiple instance arguments.
      - Ask: "Which worksheet should hold table arguments?"
      - List all worksheets as numbered options.
      - If InpT does not exist, include "Create InpT" as an option.
      - Include "Or enter a worksheet name to create" as an option.
      - If you created the worksheet:
          - merge A1:G1
          - Put "Input Tables" in cell A1.
          - Apply cell style Title to A1.
      - Find space starting in row 4 with nothing beneath it.
      - Arrange tables horizontally across the page. No table should be below another table.
      - For each table:
          - Add table always in row 6
          - Use the function's exact argument names as column headers.
          - Merge cells in row 4 above the table and add a very brief table description.
          - Format the description using cell style Normal and bold font.
          - Format only the table data cells with the cell style Input.
          - Add appropriate data validation to each value cell.
          - Do not add defaults to tables.
      - Do not combine arguments that may have different row counts, including start/stop arguments.
      - Tell the user: >
          Entries in schedule tables continue to repeat, if they repeat, until replaced
          by another entry. To stop repeating completely, enter 0 in the amount column,
          the date after the last repeat in the date column, and O for one time in the
          Repeats column.
      - Ask whether the user needs clarification on the tables.
      - Provide explanations if requested.
      - Instruct the user to type C to continue when finished asking questions.
      - Ask the user to make entries into these tables and type C when finished.
      - Before continuing, check each table and delete empty rows beyond the first row.

  - step: 4
    name: Add a timeline
    actions:
      - Ask which worksheet should hold the function and model timeline.
      - Ask whether to create a Debt worksheet or use an existing worksheet.
      - Tell the user the default worksheet is Debt, which stands for Debt Schedule.
      - If needed, add the worksheet.
      - Put "Debt Schedule" in cell A1.
      - Apply Title style and merge A1:G1.
      - If needed, add a timeline to the worksheet.
      - If another worksheet has a timeline, replicate it in the same location and format.
      - If no timeline exists:
          - In the single value argument worksheet:
              - Add Model Start, Model Periods, and Model Interval in the Argument Column.
              - Apply Normal style.
              - In the next column, apply Input style.
              - In the next column, add units of measure.
              - In the next column, add brief descriptions.
              - Add data validation based on Timelineλ help text.
              - Add defaults where appropriate based on Timelineλ help text.
              - Name input cells using Pascal Case.
              - Ask the user to make entries and type C, then press ENTER to continue.
          - In the Debt Worksheet:
              - add a formula in row 3 of the Argument Column referencing the three name and value cells for the timeline arguments.
              - Apply cell Style Date to the Model Start
              - Add "Periods" to row 3, one column left of the Timeline Column.
              - Apply Normal style.
              - Add formula =SEQUENCE(,ModelPeriods) in row 3 of the Timeline Column.
              - Name this array Periods.
              - Add "Starts" to row 4, one column left of the Timeline Column.
              - Apply Normal style.
              - Add formula =Timelineλ(ModelStart,ModelPeriods,ModelInterval,TRUE) in row 4 of the Timeline Column.
              - Name this array Starts.
              - Add "Ends" to row 5, one column left of the Timeline Column.
              - Apply Normal style.
              - Add formula =Timelineλ(ModelStart,ModelPeriods,ModelInterval,FALSE) in row 5 of the Timeline Column.
              - Name this array Ends.

  - step: 5
    name: Add function AmortizeDebtλ
    actions:
      - Using the worksheet from step 4, find a cell below the Timeline Column and place AmortizeDebtλ with all arguments linked to the created inputs.
      - If column titles are not provided, use:
          - Name
          - Value
          - UOM
      - Apply Normal style and bold.
      - If any constant inputs or tables have no entries or totally blank entries, do not use those arguments.
      - Leave blank or missing optional and required arguments out so the function handles them.
      - If AmortizeDebtλ does not produce the expected result, ignore it and do not try to fix it.
      - In column B of the same row as the function, enter =AmortizeLabelsλ("","USD"), replacing USD with the user's preferred currency.
      - In the column just left of the function, add totals using =SumRCλ($G$8#), replacing $G$8# with the location of the function.
      - Totals go next to rows containing these values in the Argument Column:
          - draw
          - repayment
          - extra payment
          - interest chart
          - interest payments
          - total payments

checklist:
  - Apply the selected workbook Cell Style from step 1 to all headings, monetary amount, and date cells.
  - Do not apply direct number formats or colors unless instructed.
  - Ensure Style gallery changes can propagate workbook-wide.