Samples / Asset-finance pricing calculator

Asset-finance pricing calculator

A deal-pricing workbook of the kind a lending or leasing team keeps: deal inputs, a rate card looked up two ways, a 72-row amortisation schedule, deal economics with NPV, IRR and a break-even rate, a sensitivity grid and a quote summary.

Sheets

6

Formula cells

641

Logic blocks

52

Input cells

139

Final outputs

97

What the workbook contains, on purpose

• Deal inputs with NETWORKDAYS, WORKDAY, DATEDIF and YEARFRAC for the settlement and first-payment dates, and six defined names.
• A rate card looked up two ways: a two-way INDEX/MATCH on grade and term, and an XLOOKUP base-rate curve with an "n/a" fallback.
• A 72-row amortisation schedule built from PMT, IPMT and PPMT, every row guarded by IF(period<=TermMonths, ..., ""), an opening balance that reads the previous closing balance, and a cumulative interest running total.
• Deal economics with SUMPRODUCT over boolean array arithmetic, MAXIFS, NPV, IRR, RATE, PV, FV and NPER; a payment sensitivity grid; a summary using IFS, SWITCH, TEXTJOIN, COUNTIF, AVERAGEIF and AND.
• The habits that make migration hard: a 360-day count and a commission rate typed inside formulas, a plug typed over the interest column, the last row of the sensitivity grid having lost its $A absolute reference, and a SUMPRODUCT that is already #VALUE! in Excel because it multiplies the schedule's "" cells.

Functions used most: IF (577), PMT (103), EDATE (75), PPMT (72), IPMT (71), MATCH (2), COUNTIF (2), TEXT (2), ROUND (1), NETWORKDAYS (1), WORKDAY (1), DATEDIF (1), YEARFRAC (1), INDEX (1).

Results by target

Every formula cell is compared with the value Excel stored in the file. A match is within 1e-9; errors must match by code. The SQL target declares that Excel errors and the empty string are NULL, and the report counts the cells that matched only under that policy.

Generated input variations: 20 input sets were run through Excel 16.0 and the Python code side by side; 641 of 641 cells agreed on every set, 0 disagreed.

The dependency graph

Sheets laid out by calculation depth; arrows carry formula counts. Click a sheet for its blocks and functions.

The generated code