axmo: "1.0"
name: Loan outstanding
description: >-
  Recipe — a balance calculated backwards from the future. The amount still owed at the end of a month is
  everything repaid after it: next month's repayment plus what is still owed at the end of next month. The
  formula refers forward in time, to its own next value.
dimensions:
  Loans:
    type: categorical
    layers: [Top_Loans, Loan]
    tree:
      All_Loans:
        - Equipment loan
        - Vehicle loan
  Time:
    type: sequential
    sequentialType: axmo_time
    parameters:
      selected_levels: [month, quarter]
      start: "2027-01"
      end_mode: end_point
      end: "2027-06"
series:
  Repayment:
    type: input
    description: Principal repaid in the month, from the repayment schedule.
    dimensions: [Loans, Time]
  Outstanding:
    type: calculated
    description: Principal still owed at the end of the month. Across time, the value at the end of the period.
    formula: Nz(Outstanding[Month[+1]], 0) + Nz(Repayment[Month[+1]], 0)
    dimensions:
      - Loans:
          rollUp: SUM
      - Time:
          rollUp: LAST
dataSources:
  Schedule:
    sourceType: excel-range
    rangeRef: Inputs!A1:C13
    hasHeaders: true
    shape: table
dataLoaders:
  Load schedule:
    type: seriesLoader
    source: Schedule
    target: Repayment
    loadMode: table
    valueColumn: Repayment
    duplicateHandling: BLOCK
    dimensionMappings:
      Loans: { column: Loan }
      Time: { column: Month }
outputRanges:
  Loan report:
    cell: Report!A1
    rowDef:
      blocks:
        - dimension: Loans/Loan
        - dimension: Loans/Top_Loans
        - series: Repayment
          format: "#,##0"
          newLine: true
        - series: Outstanding
          format: "#,##0"
    colDef:
      blocks:
        - dimension: Time/Month
        - dimension: Time/Quarter
data:
  Inputs!A1:C13:
    values:
      - [Loan, Month, Repayment]
      - [Equipment loan, "2027-01", 2000]
      - [Equipment loan, "2027-02", 2000]
      - [Equipment loan, "2027-03", 2000]
      - [Equipment loan, "2027-04", 2000]
      - [Equipment loan, "2027-05", 2000]
      - [Equipment loan, "2027-06", 2000]
      - [Vehicle loan, "2027-01", 500]
      - [Vehicle loan, "2027-02", 500]
      - [Vehicle loan, "2027-03", 1500]
      - [Vehicle loan, "2027-04", 1500]
      - [Vehicle loan, "2027-05", 0]
      - [Vehicle loan, "2027-06", 3000]