CalcBuilder Tutorial

▶ A two-minute video walkthrough of this example (English audio & subtitles). The written steps below cover the same ground.

This is the Excel workbook version of the loan calculator. The result is identical — the same monthly payment, total interest and year-by-year amortization schedule — but instead of writing the finance formulas as PHP, we upload an .xlsx and let Calc Builder run it. Ideal when the model already exists in a spreadsheet, or when the person who maintains the maths works in Excel, not in code.

⇩ Download the sample workbook — loan_model.xlsx (6 KB)

1. Build the workbook

Create loan_model.xlsx with one sheet. Reserve three cells for the inputs and put the formulas in the rest:

• B1 loan amount, B2 annual rate %, B3 term in years — these are the input cells.
• B5 =B2/100/12 (monthly rate), B6 =B3*12 (number of payments).
• B8 monthly payment: =IF(B5=0, B1/B6, B1*B5*(1+B5)^B6/((1+B5)^B6-1))
• B9 =B8*B6 (total repaid), B10 =B9-B1 (total interest).
• A schedule block from row 14: for each year, -CUMPRINC(...), -CUMIPMT(...) and the remaining balance, wrapped in IF(year>$B$3,"",…) so unused rows stay blank.

Calc Builder evaluates the formulas with PhpSpreadsheet, so the standard Excel functions — including CUMPRINC and CUMIPMT — work on the server. Keep the result cells in the General number format so they come back as plain numbers.

2. Create the calculator and the form fields

Components → Calc Builder → Add → name it Loan Calculator (Excel). On Form Fields add the same three Number fields as the PHP version: loan_amount (default 20000), annual_rate (2 decimals, default 6.5), years (default 5).

3. Upload the workbook — Spreadsheet screen

1. Open the Spreadsheet screen from the toolbar.
2. Choose File and pick loan_model.xlsx.
3. Spreadsheet calculation launches: choose At a custom point. This lets us place a //[EXCEL] marker in the PHP code so we control exactly when the workbook runs (and can read extra cells afterwards). Before the code also works if you do no further PHP.
4. Leave cache off while testing; set Locale to English.

4. Map the form fields to input cells

On Input fields mapping, click Add once per field and fill Variable name → Sheet Number → Cell:

• loan_amount → sheet 0 → B1
• annual_rate → sheet 0 → B2
• years → sheet 0 → B3

“Sheet Number” is the zero-based index of the tab — 0 is the first sheet.

5. Map result cells to variables

On Output fields mapping, map Sheet Number → Cell → Variable name:

• 0 / B8 → monthly
• 0 / B9 → total_paid
• 0 / B10 → total_interest
• 0 / B6 → payments
• 0 / B5 → monthly_rate

Save. After the calculation these become PHP variables, exactly as if your code had assigned them.

6. PHP code — just read and format

There is no finance maths here. The //[EXCEL] line is where Calc Builder injects the “write inputs, recalculate, read outputs” code; after it, $objPHPExcel is the loaded workbook, so we read the schedule block straight out of the sheet:

//[EXCEL]

$sheet = $objPHPExcel->getSheet(0);
$rows  = '';
for ($r = 14; $r <= 13 + (int) $years; $r++) {
    $yr = $sheet->getCell("A".$r)->getFormattedValue();
    if ($yr === "") break;
    $pr  = number_format((float) $sheet->getCell("B".$r)->getFormattedValue(), 2);
    $int = number_format((float) $sheet->getCell("C".$r)->getFormattedValue(), 2);
    $bal = number_format((float) $sheet->getCell("D".$r)->getFormattedValue(), 2);
    $rows .= "<tr><td>$yr</td><td class='text-end'>$pr</td>"
           . "<td class='text-end'>$int</td><td class='text-end'>$bal</td></tr>";
}
$schedule = "<table class='table table-sm table-striped'><thead><tr>"
          . "<th>Year</th><th class='text-end'>Principal</th><th class='text-end'>Interest</th><th class='text-end'>Balance</th>"
          . "</tr></thead><tbody>$rows</tbody></table>";

$monthly        = number_format((float) $monthly, 2);
$total_paid     = number_format((float) $total_paid, 2);
$total_interest = number_format((float) $total_interest, 2);
$loan_amount_f  = number_format((float) $loan_amount, 2);
$payments       = (int) $payments;

If you keep the whole schedule inside the spreadsheet and map every cell, you can skip the loop entirely — then the PHP box only formats numbers, or is empty.

7. Exit Layout, Preferences & publish

The Exit Layout is the same as the PHP version — three summary cards and ##schedule##:

<div class="row g-3 mb-4">
  <div class="col-md-4">…##monthly## €…</div>
  <div class="col-md-4">…##total_interest## €…</div>
  <div class="col-md-4">…##total_paid## €…</div>
</div>
<h5>Yearly amortization schedule</h5>
##schedule##

On Preferences set the button text and calculate on load. Publish with a menu item of type Calc Builder and select Loan Calculator (Excel) on the Options tab.

Result

Identical output to the PHP version — monthly payment 391.32 €, total interest 3,479.38 €, and the full schedule — but the entire financial model lives in a file a non-developer can open, edit and re-upload.

PHP code vs. Excel workbook — which to use?

• PHP code: nothing to upload, easy version control, full programming power, best when the logic is simple or very custom.
• Excel workbook: reuse a model that already exists, hand maintenance to whoever owns the spreadsheet, keep complex financial/engineering formulas readable. Slightly slower per calculation (enable cache for heavy workbooks).