SPB Git

Repository Public

ScheduleWriter.swift 9.2 KB 154 lines swift

1import Foundation2import SheetCore3import SheetUI4import FinanceModels5import AnalysisKit67/// Inserts amortization schedules into the workbook, as values or as live formulas.8enum ScheduleWriter {9    typealias Assumption = (label: String, value: Value, format: String)1011    static func scheduleTable(_ s: AmortizationSchedule, title: String?) -> ReportTable {12        let hasExtra = s.totalExtra > 013        var rows: [[Value]] = [[.text(L("Period")), .text(L("Payment")), .text(L("Interest")), .text(L("Principal"))]14                               + (hasExtra ? [.text(L("Extra payment"))] : []) + [.text(L("Balance"))]]15        for r in s.rows {16            rows.append([.number(Double(r.period)), Fin.value(r.payment), Fin.value(r.interest), Fin.value(r.principal)]17                        + (hasExtra ? [Fin.value(r.extra)] : []) + [Fin.value(r.balance)])18        }19        var t = ReportTable(title: title, rows: rows, headerColumn: false)20        for c in 1..<(rows[0].count) { t.columnFormats[c] = .money }21        t.columnFormats[0] = .integer22        return t23    }2425    /// New sheet with a labelled assumptions block and the schedule as VALUES. Call inside a transaction.26    @discardableResult27    static func insertValues(engine: CalcEngine, at index: Int?, name: String, title: String, assumptions: [Assumption], schedule s: AmortizationSchedule,28                             notes: [String]) -> Int {29        let si = engine.addSheet(named: ReportWriter.uniqueSheetName(name, in: engine.workbook), at: index.map { min($0, engine.workbook.sheets.count) })30        var w = BlockWriter(engine: engine, sheet: si)31        w.title(title)32        w.note(L("Schedule inserted as values: it does not recalculate when the assumptions below are edited."))33        w.skip()34        w.heading(L("Assumptions (as computed)"))35        for a in assumptions {36            if case .number(let x) = a.value { w.valueRow(a.label, x, format: a.format) } else { w.textRow(a.label, a.value.textValue ?? "") }37        }38        w.skip()39        w.heading(L("Totals"))40        w.valueRow(L("Total interest"), Fin.double(s.totalInterest), format: "#,##0.00", bold: true)41        w.valueRow(L("Total principal repaid"), Fin.double(s.totalPrincipal), format: "#,##0.00")42        w.valueRow(L("Total paid"), Fin.double(s.totalPaid), format: "#,##0.00", bold: true)43        w.valueRow(L("Number of payments"), Double(s.numberOfPayments), format: "0")44        w.skip()45        w.heading(L("Amortization schedule"))46        let hasExtra = s.totalExtra > 047        w.header([L("Period"), L("Payment"), L("Interest"), L("Principal")] + (hasExtra ? [L("Extra payment")] : []) + [L("Balance")])48        let first = w.row49        for r in s.rows {50            var values = [Double(r.period), Fin.double(r.payment), Fin.double(r.interest), Fin.double(r.principal)]51            if hasExtra { values.append(Fin.double(r.extra)) }52            values.append(Fin.double(r.balance))53            for (c, x) in values.enumerated() { engine.set(sheet: si, row: w.row, col: c, .value(.number(x))) }54            w.row += 155        }56        let width = hasExtra ? 6 : 557        if w.row > first {58            engine.setStyle(sheet: si, range: RangeRef(r0: first, c0: 1, r1: w.row - 1, c1: width - 1)) { $0.numberFormat = "#,##0.00" }59            engine.setStyle(sheet: si, range: RangeRef(r0: first, c0: 0, r1: w.row - 1, c1: 0)) { $0.numberFormat = "0" }60        }61        if !notes.isEmpty { w.skip(); for n in notes { w.note(n) } }62        engine.editMeta(sheet: si) { m in m.colWidths[0] = 250; for c in 1..<width { m.colWidths[c] = 112 } }63        return si64    }6566    /// Why a loan cannot be laid out as faithful live formulas (nil when it can).67    static func formulaObstacle(_ t: LoanTerms, hasFunction: (String) -> Bool) -> String? {68        if !t.extraPayments.isEmpty || t.recurringExtraPayment > 0 || t.paymentOverride != nil {69            return L("Extra or overridden payments shorten the loan, which a fixed block of formulas cannot follow.")70        }71        if case .balloon = t.type { return L("Balloon loans are inserted as values (the final balloon settlement has no faithful formula layout).") }72        if t.rounding.mode != .halfUp { return L("Only half-up rounding can be reproduced with the ROUND function.") }73        if !hasFunction("ROUND") { return L("The ROUND function is not available in this workbook's function library.") }74        if case .continuous? = t.compounding, !hasFunction("EXP") { return L("The EXP function (continuous compounding) is not available in this workbook's function library.") }75        return nil76    }7778    /// New sheet with input cells (blue) and a schedule of LIVE formulas. Call inside a transaction, after79    /// `formulaObstacle` returned nil. Returns the sheet index and the addresses of the key cells.80    @discardableResult81    static func insertFormulas(engine: CalcEngine, at index: Int?, name: String, title: String, terms t: LoanTerms) -> (sheet: Int, cells: [String: CellRef]) {82        let si = engine.addSheet(named: ReportWriter.uniqueSheetName(name, in: engine.workbook), at: index.map { min($0, engine.workbook.sheets.count) })83        var w = BlockWriter(engine: engine, sheet: si)84        var cells = [String: CellRef]()85        let scale = t.rounding.scale86        let money = "#,##0." + String(repeating: "0", count: max(scale, 0))87        func ref(_ abs: String) -> CellRef { CellRef(a1: abs.replacingOccurrences(of: "$", with: "")) ?? CellRef(0, 0) }88        w.title(title)89        w.note(L("Live model: edit the blue input cells and the schedule recalculates."))90        w.skip()91        w.heading(L("Assumptions"))92        let p = w.input(L("Loan amount"), Fin.double(t.principal), format: money); cells["principal"] = ref(p)93        let r = w.input(L("Annual interest rate"), t.annualRate, format: "0.000%"); cells["rate"] = ref(r)94        let ppy = w.input(L("Payments per year"), t.paymentsPerYear, format: "0"); cells["paymentsPerYear"] = ref(ppy)95        var m = ""96        if case .perYear(let cm)? = t.compounding { m = w.input(L("Compounding periods per year"), cm, format: "0") }97        w.text(w.row, 0, L("Number of payments")); w.number(w.row, 1, Double(t.periods), format: "0")98        let n = "$B$\(w.row + 1)"; cells["periods"] = ref(n)99        w.row += 1100        let iFormula: String101        switch t.compounding {102        case nil: iFormula = "=\(r)/\(ppy)"103        case .perYear?: iFormula = "=(1+\(r)/\(m))^(\(m)/\(ppy))-1"104        case .continuous?: iFormula = "=EXP(\(r)/\(ppy))-1"105        }106        let i = w.formulaRow(L("Rate per period"), iFormula, format: "0.00000%"); cells["periodicRate"] = ref(i)107        var pmt = ""108        if case .fixedPayment = t.type {109            let f = t.annualRate == 0 ? "=ROUND(\(p)/\(n),\(scale))" : "=ROUND(\(p)*\(i)/(1-(1+\(i))^(-\(n))),\(scale))"110            pmt = w.formulaRow(L("Regular payment"), f, format: money, bold: true); cells["payment"] = ref(pmt)111        }112        w.skip()113        w.heading(L("Totals"))114        let totalsRow = w.row115        w.row += 3116        w.skip()117        w.heading(L("Amortization schedule"))118        w.header([L("Period"), L("Payment"), L("Interest"), L("Principal"), L("Balance")])119        let first = w.row120        for k in 1...t.periods {121            let row = w.row, a = row + 1122            let prev = k == 1 ? p : "E\(row)"123            let last = k == t.periods124            engine.set(sheet: si, row: row, col: 0, .value(.number(Double(k))))125            w.formula(row, 2, "=ROUND(\(prev)*\(i),\(scale))", format: money)126            let principal: String127            switch t.type {128            case .fixedPayment: principal = last ? "=\(prev)" : "=MIN(\(pmt)-C\(a),\(prev))"129            case .fixedPrincipal: principal = last ? "=\(prev)" : "=MIN(ROUND(\(p)/\(n),\(scale)),\(prev))"130            default: principal = last ? "=\(prev)" : "=0"131            }132            w.formula(row, 3, principal, format: money)133            w.formula(row, 1, "=C\(a)+D\(a)", format: money)134            w.formula(row, 4, last ? "=\(prev)-D\(a)" : "=ROUND(\(prev)-D\(a),\(scale))", format: money)135            w.row += 1136        }137        let lastRow = w.row138        engine.setStyle(sheet: si, range: RangeRef(r0: first, c0: 0, r1: lastRow - 1, c1: 0)) { $0.numberFormat = "0" }139        // totals (above the schedule, referencing it)140        var tw = w141        tw.row = totalsRow142        cells["totalInterest"] = ref(tw.formulaRow(L("Total interest"), "=SUM(C\(first + 1):C\(lastRow))", format: money, bold: true))143        cells["totalPrincipal"] = ref(tw.formulaRow(L("Total principal repaid"), "=SUM(D\(first + 1):D\(lastRow))", format: money))144        cells["totalPaid"] = ref(tw.formulaRow(L("Total paid"), "=SUM(B\(first + 1):B\(lastRow))", format: money, bold: true))145        cells["firstRow"] = CellRef(first, 0)146        w.skip()147        w.note(L("Each interest amount is ROUND(balance × rate per period); the last payment clears the balance, so the schedule ends at 0."))148        w.note(L("The number of rows is fixed: to change the number of payments, insert a new schedule."))149        w.note(L("Formulas compute in binary floating point; a rounding tie can differ by one cent from the decimal schedule shown in the studio."))150        engine.editMeta(sheet: si) { mm in mm.colWidths[0] = 250; for c in 1..<5 { mm.colWidths[c] = 112 } }151        return (si, cells)152    }153}154
Files main
Menu

Theme