Repository Public
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