Loan model: PMT, accounting formats and the File tab
A mortgage model built from the sheet's own financial functions: PMT for the payment, IPMT and PPMT for each period's split, NPER and RATE for the term and rate that fit, SUMPRODUCT for the first year of interest. The money cells wear the Accounting format, Extra Payment carries a validation rule with an Input Message, and Circle Invalid Data rings what breaks it. The File tab is the point: Save As writes the whole model as an .xlsx Excel opens with its formulas, formats and rules, Save As ODS writes the same model for LibreOffice Calc and Save As XLS the Excel 97-2003 binary, Open reads any of them (or a .csv) back by their bytes rather than their name, and Export CSV takes the active sheet. (requires @svgrid/enterprise)
A live, editable Svelte 5 data grid example from the SvGrid gallery (Spreadsheet). See the SvGrid documentation for the full API.
About this example
A mortgage model on the Svelte 5 spreadsheet shell built from the sheet's own financial functions: PMT for the fixed payment, IPMT and PPMT for each period's split, NPER and RATE for the term and rate that fit, SUMPRODUCT for the first-year interest. Money cells wear the Accounting number format so symbols and figures line up, an Extra Payment column carries a validation rule with an Input Message, and Circle Invalid Data rings the entries that break it. The File tab saves the whole model as an .xlsx that Excel opens with formulas, formats and rules in place, as an .ods for LibreOffice Calc, or as an Excel 97-2003 .xls; it opens any of the three back, or a .csv, deciding which from the bytes rather than the file name, and exports the sheet as CSV; Ctrl+O and Ctrl+S do the same. Enterprise, in @svgrid/enterprise.
A mortgage model on the spreadsheet shell, built from the sheet's own financial functions rather than a plug-in engine:
PMT the fixed payment from three inputs (B2:B4) IPMT / PPMT the interest and principal parts of any period NPER / RATE the term that fits a payment, and the rate that does CUMULATIVE a SUMPRODUCT over the first-year interest
The money cells carry the Accounting format (the $ button, or Format Cells > Number > Accounting), so the symbols line up on the left and the figures on the right, the way a statement reads. Extra Payment has a validation rule with an Input Message that shows while the cell is selected; Data > Data Validation > Circle Invalid Data rings the two that were typed wrong.
The File tab is the point, and it writes three formats: Save As downloads the whole model as an .xlsx that Excel opens with the formulas, the formats and the rule in place; Save As ODS writes the same model as OpenDocument, which LibreOffice Calc opens; Save As XLS writes the Excel 97-2003 binary, which every Excel since 1997 opens and which is still what a bank or an ERP hands out. Open takes any of the three back, or a .csv, and decides which it is from the bytes rather than the file name, so a file downloaded with no extension still opens as itself. Export CSV gives the sheet as its cells show; Print (Ctrl+P) hands the sheet to the browser's print dialog as the Page Layout tab says. Ctrl+O and Ctrl+S are the same actions.
The three formats do not carry the same things: the .xlsx carries the most, and the .xls the least (no charts, pictures, pivots, validation or conditional formatting, and a formula using a function Excel 97 never had is saved as the value it worked out). The model here is formulas, formats and a validation rule, so it survives all three.
Try: change the rate in B3 and watch the payment. Select F3 to see the input message. Data > Sort on the schedule with two levels. Save As, then New, then Open the file you just saved - then do the same round trip through Save As ODS and Save As XLS.
Imports, features and API used
Imports: @svgrid/enterprise
Frequently asked questions
Does the saved .xlsx keep the formulas?
Yes. Every formula goes out as a formula with its cached value, so Excel shows the numbers at once and recalculates when an input changes. Formats, column widths, the frozen row, the validation rule and its input message go with it.
Which financial functions does the sheet have?
PMT, IPMT, PPMT, PV, FV, NPER, RATE, NPV, IRR and SLN, with Excel's sign convention: money paid out is negative, money received is positive. RATE and IRR are solved numerically.
Can an app keep the file somewhere other than the user's disk?
Yes. Take file-open and file-save-xlsx over in onAction and call the component's open(file) and toXlsx() with a Blob from wherever the workbook lives.
Which spreadsheet files can it open and save?
Open takes an .xlsx as Excel and Google Sheets write one, an .ods as LibreOffice Calc does, an .xls as any Excel since 1997 does, or a .csv, and decides which from the bytes rather than the file name. Save As writes the first three. Each format carries what it has a place for: the .xlsx the most, the .xls the least, where a formula using a function Excel 97 never had is saved as the value it worked out.
Related documentation
Related articles
- A Fill Handle (Drag to Fill) in SvGrid - Build a working spreadsheet-style fill handle on top of SvGrid's cell selection and editing - pointer tracking, range highlighting, series fill, and undo/redo integration all covered.
- Inside SvGrid: The Row Model and Sorting - How sorting shaped SvGrid's row-model pipeline - the decisions made early that every later feature inherited.
- Pivot Tables in Svelte - Summarize Data Without a Spreadsheet - Run a cross-tab pivot directly inside your Svelte app using @svgrid/enterprise createPivotModel - no Excel, no server-side aggregation, no stale exports.
Source code (484-loan-model-files.svelte)
<script lang="ts">
/**
* 474. Loan model: PMT, accounting formats, and the File tab
* ----------------------------------------------------------
* A mortgage model on the spreadsheet shell, built from the sheet's own
* financial functions rather than a plug-in engine:
*
* PMT the fixed payment from three inputs (B2:B4)
* IPMT / PPMT the interest and principal parts of any period
* NPER / RATE the term that fits a payment, and the rate that does
* CUMULATIVE a SUMPRODUCT over the first-year interest
*
* The money cells carry the Accounting format (the $ button, or Format
* Cells > Number > Accounting), so the symbols line up on the left and
* the figures on the right, the way a statement reads. Extra Payment has
* a validation rule with an Input Message that shows while the cell is
* selected; Data > Data Validation > Circle Invalid Data rings the two
* that were typed wrong.
*
* The File tab is the point, and it writes three formats: Save As
* downloads the whole model as an .xlsx that Excel opens with the
* formulas, the formats and the rule in place; Save As ODS writes the
* same model as OpenDocument, which LibreOffice Calc opens; Save As XLS
* writes the Excel 97-2003 binary, which every Excel since 1997 opens
* and which is still what a bank or an ERP hands out. Open takes any of
* the three back, or a .csv, and decides which it is from the bytes
* rather than the file name, so a file downloaded with no extension
* still opens as itself. Export CSV gives the sheet as its cells show;
* Print (Ctrl+P) hands the sheet to the browser's print dialog as the
* Page Layout tab says. Ctrl+O and Ctrl+S are the same actions.
*
* The three formats do not carry the same things: the .xlsx carries the
* most, and the .xls the least (no charts, pictures, pivots, validation
* or conditional formatting, and a formula using a function Excel 97
* never had is saved as the value it worked out). The model here is
* formulas, formats and a validation rule, so it survives all three.
*
* Try: change the rate in B3 and watch the payment. Select F3 to see the
* input message. Data > Sort on the schedule with two levels. Save As,
* then New, then Open the file you just saved - then do the same round
* trip through Save As ODS and Save As XLS.
*/
import { SvSheet, createWorkbook, createSheetDocument, FORMAT_PRESETS, accountingPattern } from '@svgrid/enterprise'
const SCHEDULE = 12
const rows: string[][] = [
['Loan', '', '', '', 'Period', 'Extra', 'Payment', 'Interest', 'Principal', 'Balance'],
['Principal', '320000', '', '', '', '', '', '', '', ''],
['Annual rate', '0.0525', '', '', '', '', '', '', '', ''],
['Years', '30', '', '', '', '', '', '', '', ''],
['', '', '', '', '', '', '', '', '', ''],
['Monthly payment', '=PMT(B3/12,B4*12,-B2)', '', '', '', '', '', '', '', ''],
['Periods at $2,000', '=NPER(B3/12,-2000,B2)', '', '', '', '', '', '', '', ''],
['Rate for $1,500', '=RATE(B4*12,-1500,B2)*12', '', '', '', '', '', '', '', ''],
['First-year interest', '=SUMPRODUCT(H2:H13)', '', '', '', '', '', '', '', ''],
['Total interest', '=B6*B4*12-B2', '', '', '', '', '', '', '', ''],
]
// The schedule sits beside the inputs, one row per period from row 2.
const extras = ['0', '0', '250', '0', '0', '0', '1000', '0', '0', '0', '0', '0']
for (let p = 1; p <= SCHEDULE; p += 1) {
const r = p + 1
while (rows.length < r) rows.push(['', '', '', '', '', '', '', '', '', ''])
const line = rows[r - 1]!
while (line.length < 10) line.push('')
line[4] = String(p)
line[5] = extras[p - 1]!
line[6] = '=$B$6+F' + r
line[7] = '=IPMT($B$3/12,E' + r + ',$B$4*12,-$B$2)'
line[8] = '=G' + r + '-H' + r
line[9] = p === 1 ? '=$B$2-I2' : '=J' + (r - 1) + '-I' + r
}
const wb = createWorkbook([{ name: 'Mortgage', cells: rows }])
const doc = createSheetDocument({ workbook: wb })
const sheet = doc.get('Mortgage')
const at = { rowIdAt: (i: number) => `r${i}`, columnIdAt: (i: number) => String.fromCharCode(65 + i) }
const money = accountingPattern('$', 2)
sheet.formats.set([[0, 0, 0, 9]], { bold: true, fill: '#e2e8f0', color: '#0f172a' }, at)
sheet.formats.set([[1, 1, 1, 1], [5, 1, 5, 1], [8, 1, 9, 1], [1, 5, 13, 9]], { numFmt: money }, at)
sheet.formats.set([[2, 1, 2, 1], [7, 1, 7, 1]], { numFmt: FORMAT_PRESETS.percent }, at)
sheet.formats.set([[6, 1, 6, 1]], { numFmt: '0.0' }, at)
sheet.formats.set([[1, 1, 3, 1]], { color: '#1d4ed8' }, at)
sheet.formats.set([[5, 0, 9, 0]], { bold: true }, at)
sheet.widths.A = 150
sheet.widths.B = 120
sheet.freeze = { rows: 1, cols: 0 }
sheet.validation = [{
id: 'extra', rects: [[1, 5, 13, 5]], allow: 'decimal', operator: 'between', value1: '0', value2: '5000',
ignoreBlank: true, inCellDropdown: false,
alert: { style: 'stop', title: 'Extra payment', message: 'Between 0 and 5,000.' },
input: { title: 'Extra payment', message: 'An amount paid on top of the fixed payment this period, 0 to 5,000.' },
}]
// Two entries that break the rule, for Circle Invalid Data to find.
wb.setRaw('Mortgage', 4, 5, '-50')
wb.setRaw('Mortgage', 9, 5, '9000')
</script>
<SvSheet document={doc} height="100%" columns={10} rows={20} />More Spreadsheet examples
- Spreadsheet + Ribbon bar - The whole Excel surface as one component, <SvSheet workbook={wb} />: a six-tab ribbon (Home, Insert, Formulas, Data, Review, View), the Name Box and fx bar, sheet tabs and the Sum / Average / Count status bar. Format cells, merge them, comment on them, validate what goes in, colour them by rule, filter the region, protect the sheet; every button is the same call as its shortcut, so Ctrl+B and the Bold button cannot drift. A two-sheet P&L with the formats travelling in the document.
- Spreadsheet + formulas - Real formula engine inside the grid: cell refs (A1), ranges (A1:A10), SUM / AVG / IF / COUNTIF / ROUND, arithmetic, string concat, cycle detection.
- Per-cell custom borders (KPI) - Editable KPI scorecard. spreadsheetLayout paints spreadsheet-style per-edge custom borders via an absolute-positioned overlay (no border-collapse conflicts). Edit any quarter or target - the borders re-derive: green double = beat target, blue solid = hit, amber dotted = near miss, red dashed = bad miss; row champion gets a colored full frame.
- Cell merging (spreadsheet shell) - A real invoice rendered on an Excel-style shell: A / B / C / D / E column letters across the top, row numbers down the left. Brand band, bill-from / bill-to address blocks, meta block, line items, totals, notes, signatures - all assembled from MergeSpec + CellBorderSpec. Editable Qty / Rate / addresses / notes; totals recompute live.
- HyperFormula integration - Full HyperFormula engine wired into the grid as a peer-optional dep. Editable spreadsheet with A1-style cell refs, dozens of formulas across math (SUM / SUMIF), lookup (VLOOKUP / INDEX-MATCH), text (CONCAT / UPPER), date (TODAY / DATEDIF), logical (IF nests), financial (PMT / IRR / NPV), statistical (AVERAGE / MAX / RANK).