How I Evaluated Excel Formulas Programmatically in Rust

Mar 4, 2026

Cowrie

Dev @ Bswen

I tried to build a loan approval system in Rust last month. The backend needed to calculate monthly payments, total interest, and amortization schedules - exactly what Excel does with its PMT, IPMT, and PPMT functions. Simple enough, right?

I opened the calamine documentation, wrote the code to load my Excel template, and… got formula strings instead of calculated values.

// What I expected: calculated values
// What I got: formula strings like "=PMT(A2/12, A3, -A1)"

That was the moment I realized the Rust Excel ecosystem has a critical gap. Let me walk you through what I found and how I solved it.

The Problem: Reading Isn’t Calculating

Here’s the thing about most Rust Excel libraries - they’re designed for reading or writing, but not both, and definitely not for evaluating formulas.

I spent days evaluating options. Here’s what I found:

┌─────────────────────────────────────────────────────────────────┐
│                    Rust Excel Library Landscape                 │
├──────────────────┬──────────┬──────────┬───────────┬────────────┤
│ Library          │ Read     │ Write    │ Evaluate │ License    │
├──────────────────┼──────────┼──────────┼───────────┼────────────┤
│ calamine         │ ✓        │ ✗        │ ✗         │ MIT        │
│ rust-xlsxwriter  │ ✗        │ ✓        │ ✗         │ MIT        │
│ HyperFormula     │ ✓        │ ✓        │ ✓         │ AGPL-3.0   │
│ Formularizer     │ ✓        │ ✓        │ ✓         │ MIT/Apache │
└──────────────────┴──────────┴──────────┴───────────┴────────────┘

Calamine is great - I use it all the time for reading Excel files. But it can’t evaluate formulas:

use calamine::{Reader, Xlsx, open_workbook};

let mut workbook: Xlsx<_> = open_workbook("financial_model.xlsx")?;
if let Ok(formula_range) = workbook.worksheet_formula("Sheet1") {
    // I get: "=PMT(A2/12, A3, -A1)"
    // NOT: 85.61
    for row in formula_range.rows() {
        println!("Formula: {:?}", row[0]);
    }
}

This limitation makes sense from a complexity standpoint. Building a formula evaluator is an entirely different beast from parsing Excel files. Just ask the Excel team - they’ve been at it for decades.

HyperFormula: Powerful But License Problems

I looked at HyperFormula next. It’s the engine behind Handsontable, supports 400+ functions, and handles complex spreadsheet logic. The problem? AGPL-3.0 license.

For my commercial loan system, that was a dealbreaker. AGPL requires you to open-source your entire application if you distribute it. My client needed a proprietary solution.

I couldn’t ship AGPL code without either:

1. Open-sourcing our entire backend
2. Purchasing a commercial license

Neither worked for us.

Formularizer: The Missing Piece

That’s when I found Formularizer. It checked all the boxes:

• Actual formula evaluation (not just reading strings)
• MIT/Apache-2.0 dual license - use in any project
• 320+ Excel-compatible functions
• Apache Arrow integration for performance
• Rust-native (no JS dependencies like HyperFormula)

The killer feature for me was the combination of permissive licensing and actual calculation capabilities. Here’s how it works:

use formualizer_workbook::Workbook;
use formualizer_common::LiteralValue;

fn calculate_loan_payment() -> Result<f64, Box<dyn std::error::Error>> {
    let mut wb = Workbook::new();
    wb.add_sheet("LoanCalc")?;

    // Set input values
    wb.set_value("LoanCalc", 1, 1, LiteralValue::Number(1000.0))?;  // Principal
    wb.set_value("LoanCalc", 2, 1, LiteralValue::Number(0.05))?;    // Annual rate
    wb.set_value("LoanCalc", 3, 1, LiteralValue::Number(12.0))?;    // Periods

    // Set formula: PMT(rate/12, periods, -principal)
    wb.set_formula("LoanCalc", 1, 2, "=PMT(A2/12, A3, -A1)")?;

    // Evaluate and get the CALCULATED result
    let payment = wb.evaluate_cell("LoanCalc", 1, 2)?;

    println!("Monthly payment: ${:.2}", payment);
    Ok(payment)
}

Output: Monthly payment: $85.61

This is what I needed. Not the formula string - the actual calculated value.

Real-World Implementation

For the loan approval system, I built something like this:

async fn calculate_loan_scenarios(
    principal: f64,
    rates: Vec<f64>,
    terms: Vec<i32>
) -> Result<Vec<LoanScenario>, Error> {
    let mut scenarios = Vec::new();

    for rate in rates {
        for term in &terms {
            let mut wb = Workbook::new();
            wb.add_sheet("Calc")?;

            wb.set_value("Calc", 1, 1, LiteralValue::Number(principal))?;
            wb.set_value("Calc", 2, 1, LiteralValue::Number(rate))?;
            wb.set_formula("Calc", 1, 2, "=PMT(A2/12, A3, -A1)")?;

            let payment = wb.evaluate_cell("Calc", 1, 2)?;

            // Calculate total interest too
            wb.set_formula("Calc", 1, 3, "=A1*A3*A2/12+A1")?;
            let total_interest = wb.evaluate_cell("Calc", 1, 3)?;

            scenarios.push(LoanScenario {
                rate,
                term: *term,
                monthly_payment: payment,
                total_interest,
            });
        }
    }

    Ok(scenarios)
}

This runs every time a user submits a loan application. The formulas are evaluated server-side, no Excel required.

Cross-Sheet References

My financial model uses multiple sheets - one for assumptions, one for projections:

// Sheet 1: Assumptions
wb.add_sheet("Assumptions")?;
wb.set_value("Assumptions", 1, 1, LiteralValue::Number(100000.0))?;  // Revenue

// Sheet 2: Projections with cross-sheet reference
wb.add_sheet("Projections")?;
wb.set_formula("Projections", 1, 1, "=Assumptions!A1")?;  // Pull from another sheet

// Year-over-year growth
wb.set_formula("Projections", 1, 2, "=A1*(1+Assumptions!A2)")?;

// Totals across columns
wb.set_formula("Projections", 1, 4, "=SUM(A1:C1)")?;

Formularizer handles the dependency graph automatically. When I evaluate a cell, it recalculates everything that depends on it.

Error Handling That Actually Helps

One thing I appreciate is proper error handling. Excel formulas can fail in many ways:

use formualizer_common::CellValue;

match wb.evaluate_cell("Sheet1", 1, 1)? {
    CellValue::Number(n) => println!("Result: {}", n),
    CellValue::Error(e) => eprintln!("Formula error: {:?}", e),  // #DIV/0!, #REF!, etc.
    CellValue::String(s) => println!("Text result: {}", s),
    CellValue::Boolean(b) => println!("Boolean: {}", b),
    CellValue::Empty => println!("Empty cell"),
}

The CellValue enum covers all Excel data types, including errors. This is crucial for production systems where bad input shouldn’t crash your service.

Performance: Apache Arrow Integration

Formularizer uses Apache Arrow for columnar storage. For large datasets, this matters:

// Populate 10,000 rows efficiently
for i in 1..=10000 {
    wb.set_value("Data", i, 1, LiteralValue::Number(i as f64))?;
}

// SUM on 10,000 rows - Arrow optimizes this internally
wb.set_formula("Data", 1, 2, "=SUM(A1:A10000)")?;
let sum = wb.evaluate_cell("Data", 1, 2)?;

Arrow’s columnar format means batch operations are fast. For my loan system with 10,000+ amortization rows per calculation, this made a noticeable difference.

What Functions Are Supported

Formularizer covers the essentials I need (320+ functions):

• Financial: PMT, PV, FV, NPV, IRR, RATE, NPER
• Math: SUM, AVERAGE, ROUND, CEILING, FLOOR, MOD
• Statistical: COUNT, COUNTIF, MAX, MIN, MEDIAN
• Logical: IF, AND, OR, NOT, IFERROR, IFS
• Text: CONCATENATE, LEFT, RIGHT, MID, LEN, TRIM
• Date: TODAY, NOW, DATE, YEAR, MONTH, DAY
• Lookup: VLOOKUP, HLOOKUP, INDEX, MATCH, INDIRECT

Missing some edge cases? Check the GitHub repo for the full list.

Quick Start

cargo add formualizer-workbook formualizer-common

Then:

use formualizer_workbook::Workbook;
use formualizer_common::LiteralValue;

let mut wb = Workbook::new();
wb.add_sheet("Sheet1")?;
wb.set_value("Sheet1", 1, 1, LiteralValue::Number(10.0))?;
wb.set_value("Sheet1", 2, 1, LiteralValue::Number(20.0))?;
wb.set_formula("Sheet1", 1, 2, "=SUM(A1:A2)")?;
let result = wb.evaluate_cell("Sheet1", 1, 2)?;  // Returns 30.0

The Bottom Line

If you need to evaluate Excel formulas in Rust:

• Read-only: Use calamine
• Write-only: Use rust-xlsxwriter
• Evaluate + permissive license: Use Formularizer
• Evaluate + commercial license budget: Use HyperFormula

For my loan system, Formularizer was the clear choice. The MIT license meant I could ship my proprietary backend without legal concerns. The API was straightforward enough to integrate in a day. Performance has been acceptable for our scale (thousands of calculations per minute, not millions).

The Rust Excel ecosystem still has gaps, but Formularizer fills the most critical one for anyone building financial or calculation-heavy applications.

Final Words + More Resources

My intention with this article was to help others share my knowledge and experience. If you want to contact me, you can contact by email: Email me

Here are also the most important links from this article along with some further resources that will help you in this scope:

• 👨‍💻 Formularizer GitHub
• 👨‍💻 calamine crate
• 👨‍💻 rust_xlsxwriter crate
• 👨‍💻 HyperFormula
• 👨‍💻 Apache Arrow

Oh, and if you found these resources useful, don’t forget to support me by starring the repo on GitHub!

Comments

Tags:

• miscs

How to Implement Centralized Error Handling Middleware in Node.js Express? (Complete Guide)

Formualizer vs HyperFormula: Which Spreadsheet Engine Should I Choose?