artvaluationfinance

Auction Valuation Simulator: Build an Art-Appraisal Spreadsheet Using the Hans Baldung Grien Example

UUnknown

2026-02-03

10 min read

Build a spreadsheet auction simulator to model bid dynamics, buyer premiums, reserves and provenance risk—test the Hans Baldung Grien scenario.

Hook: Stop guessing—simulate art-auction outcomes in minutes

Pain point: you’ve seen auction estimates and headlines (for example: a previously unknown 1517 Hans Baldung Grien drawing estimated up to $3.5M), but manual arithmetic, fuzzy rules for buyer premiums, opaque reserve behavior and provenance risk make it hard to answer: what is the real expected value to buyer and seller?

The promise: a practical art auction valuation simulator you can build in a spreadsheet

This article walks you, step-by-step, through constructing a reusable art auction valuation simulator that models bid dynamics, tiered buyer premiums, reserve-price adjustments, provenance risk and auction fees. By the end you’ll have a Monte Carlo-capable template (Excel 365 / Google Sheets) to test scenarios for the Hans Baldung Grien example or any artwork.

Why this matters in 2026

Provenance due diligence has accelerated since late 2024–2025: auction houses now publish more provenance flags, and regulatory inspections increased in 2025—making provenance risk a material variable for price modeling.
Digital bidding, synced analytics and real-time remote bidders became ubiquitous by 2026; that expands bidder pools and changes bid dynamics.
Spreadsheet platforms (Excel 365 dynamic arrays, LET/LAMBDA, Google Sheets RANDARRAY) now make large-scale Monte Carlo simulations feasible inside the sheet—no external code required.

What the template models (at a glance)

Bidder population: distribution for number of active bidders.
Individual valuations: random draws (Normal or Lognormal) for each bidder’s willingness-to-pay (WTP).
Bid dynamics rule: ascending auction approximation where final hammer is second-highest plus increment (or highest if no jump).
Reserve price behavior: lot unsold if highest bid < reserve; optional reserve adjustment (seller may accept lower with probability).
Buyer premium and auction fees: customizable tiered buyer-premium schedule and seller commissions/fees.
Provenance risk: probability of provenance issue causing either (a) lot withdrawal, or (b) discounted realized price post-sale.
Monte Carlo iterations: average outcomes and distributional outputs (median, percentiles, probability of sale).

Real-world example: Hans Baldung Grien, postcard-sized 1517 drawing

Use the public estimate (headline: “could fetch up to $3.5M”) as an anchor. We’ll test three reserve scenarios: no reserve, reserve = $1.5M, reserve = $3.0M. Typical modern buyer premium tiers are parameterized so you can change them to match Christie's, Sotheby's, or regional houses.

Step-by-step: Build the simulator in your spreadsheet

1) Parameters sheet (single place to tweak)

Create a sheet named Params. Keep all inputs here so scenarios are reproducible.

Estimate low / high: MinEstimate, MaxEstimate (for reference).
Base estimate / headline max: EstimateMid = 3,500,000 (for Baldung example).
Expected active bidders: ExpectedBidders (use 6 as default), BiddersStdDev (2).
Valuation distribution: choose Lognormal or Normal. For art, lognormal often fits right-skew in valuations. Set ValLogMu and ValLogSigma for lognormal.
Lot entry threshold: minimum WTP to join bidding (e.g., 0.25 * EstimateMid).
Bid increment rule: IncrementPct (e.g., 0.05 = 5% of current price) or fixed increment (e.g., $10,000).
Reserve price: Reserve (scenario input) and ReserveAdjustmentProbability (probability seller will accept a negotiated sale below reserve).
Buyer premium tiers: table of (threshold, rate) in descending order. Example:
- Up to $250k -> 25%
- $250k–$1M -> 20%
- $1M+ -> 12.5%
Seller commission: SellerCommPct (e.g., 10%).
Provenance: ProbIssue (e.g., 0.08 = 8% chance), IssueBehavior = {Withdraw | Discount}. For Discount set DiscountFactor (e.g., 0.7 = 30% haircut).
Iterations: N (e.g., 10,000).

2) Simulate number of active bidders

For each iteration, draw the number of bidders. Use Poisson, Normal (rounded), or fixed. Example (Excel 365):

=ROUND(NORM.INV(RAND(), Params!BiddersMean, Params!BiddersStdDev),0)

Ensure a minimum of 1: wrap with MAX(1,...).

3) Generate bidder valuations (WTPs)

For each bidder i generate a WTP. If using lognormal, in Excel:

=EXP( NORM.INV(RAND(), Params!ValLogMu, Params!ValLogSigma) )

For Google Sheets use NORM.INV(RAND(), mu, sigma) then EXP. Create an array of size = number of bidders in the iteration. If your sheet supports dynamic arrays (Excel 365 / Google Sheets), use RANDARRAY and map into NORM.INV. When preparing these simulated draws, apply sound data hygiene practices (see data engineering patterns) so your sampled distributions remain reproducible and auditable.

4) Apply entry threshold and form the active-bidder set

Filter WTPs >= EntryThreshold. Count active bidders. If none exceed threshold, hammer price = 0 (unsold).

5) Approximate ascending auction final hammer

A practical and commonly used approximation for English auctions in spreadsheets is:

If active bidder count = 1, hammer = their WTP (they can buy at their max).
If active bidders >= 2: hammer = second-highest WTP + increment (if that value <= highest WTP), else hammer = highest WTP.

Implement with:

=IF(ActiveCount=0,0,IF(ActiveCount=1,MAX(WTPs),MIN(MAX(WTPs), LARGE(WTPs,2) + Increment)))

Where Increment = MAX(FixedIncrement, IncrementPct * LARGE(WTPs,2)).

6) Reserve logic

If hammer < Reserve then two outcomes:

Lot unsold: realized sale = 0
OR seller accepts lower (negotiates): with probability ReserveAdjustmentProbability the seller accepts a fallback price. Model fallback price as SecondHighest * (1 + NegotiateAddPct) or as a fixed % of Reserve (parameterize).

Spreadsheet formula (simplified):

=IF(Hammer < Params!Reserve, IF(RAND() < Params!ReserveAdjustmentProb, FallbackPrice, 0), Hammer)

For modelling a sophisticated post-hammer negotiation strategy, consider referencing an advanced ops playbook style approach to parameterizing fallback probabilities and expected seller behavior.

7) Provenance risk

After a sale is concluded, roll a random draw:

If IssueBehavior = Withdraw and RAND() < ProbIssue then realized sale = 0 (withdrawn).
If IssueBehavior = Discount and RAND() < ProbIssue then realized sale = realized sale * DiscountFactor.

8) Buyer premium and fees

Compute buyer pays = Hammer * (1 + BuyerPremiumRate) (apply tiered logic). Seller receives = RealizedHammer * (1 - SellerCommPct) - SellerCosts. If buyer premium is charged on hammer only (most houses), use tier lookup:

=LET(x, Hammer,
     rate, IF(x >= Tier2Thresh, Tier3Rate, IF(x >= Tier1Thresh, Tier2Rate, Tier1Rate)),
     BuyerPays, x*(1+rate), BuyerPays)

For squarely defined tiers use VLOOKUP or INDEX/MATCH on thresholds. If you need to model how auction houses shift buyer-premium schedules (or apply conditional fees or dynamic pricing), check the industry update on URL privacy & dynamic pricing for context on how fee disclosures and dynamic pricing practices are evolving.

9) Monte Carlo aggregation

Run N iterations. For each iteration store:

Hammer price
BuyerPays
SellerProceeds
SaleOccurred (1/0)

Aggregate with AVERAGE, MEDIAN, PERCENTILE, STDEV and probability metrics (e.g., P(Sale) = SUM(SaleOccurred)/N). If you plan to run large batch jobs or automate reporting of results, consider piping these iterations into cloud workflows or scheduled exports (see automation with prompt chains) so scenario reports can be generated reproducibly.

Example parameterization — Hans Baldung Grien drawing

Plug these starting values into Params to reproduce the example results. Tweak to test sensitivity.

EstimateMid = $3,500,000
ExpectedBidders = 8, BiddersStdDev = 3
Valuation distribution: lognormal with ValLogMu = LN(2,500,000) – convert by solving mu = LN(mean) - 0.5*sigma^2; choose ValLogSigma = 0.6 (adjustable)
EntryThreshold = $150,000 (pre-auction interest floor)
IncrementPct = 0.05
Reserve scenarios: 0, 1,500,000; 3,000,000
ReserveAdjustmentProbability = 0.25
Buyer premium tiers: 25% up to $250k; 20% $250k–$1M; 12.5% above $1M
SellerCommPct = 10%
Provenance: ProbIssue = 0.06, IssueBehavior = Discount, DiscountFactor = 0.6
Iterations = 10,000

Interpreting results: what the simulator tells you

After running the Monte Carlo you’ll get:

P(Sale): probability the lot sells under your reserve scenario.
Expected buyer payment: average of hammer*(1+buyer premium).
Expected seller proceeds: average post-commission, accounting for withdrawals & discounts.
Price distribution: median, interquartile, 90th percentile prices to inform auction marketing or reserve setting.

Sample insight (illustrative)

With the assumptions above you may find:

No reserve: P(Sale) ≈ 95%, ExpectedSellerProceeds ≈ $2.6M
Reserve $1.5M: P(Sale) ≈ 88%, ExpectedSellerProceeds ≈ $2.9M
Reserve $3.0M: P(Sale) ≈ 52%, ExpectedSellerProceeds ≈ $2.7M

Interpretation: raising reserve to $3M increases the chance of selling above $3M for the ones that achieve it, but reduces P(Sale) enough that expected proceeds may not improve. This is the classic reserve-price tradeoff visible in the simulator.

Advanced strategies and 2026 trends you should test

Below are higher-fidelity extensions useful for instructors and serious valuations in 2026.

Model asymmetric information: give some bidders higher probability of recognizing a strong provenance (higher WTP if provenance clear).
Include remote/incremental bidders: add late-arrival probability to simulate online bidding surges seen in 2025–2026.
Blockchain provenance integration: test a scenario where provenance is certified on-chain—reduce ProbIssue by X% to model the premium for blockchain-certified provenance (emerged 2025–2026).
Condition and attribution uncertainty: model a conditional probability that attribution is upgraded or downgraded post-sale with associated payoff adjustments (use Markov-style transitions across epochs).
Seller negotiation algorithm: instead of a single reserve adjustment probability, implement a seller strategy LAMBDA that accepts offers that exceed expected future-discounted value of holding (requires discount rate input).

Practical tips & common pitfalls

Sanity-check your distribution: if your simulated median far exceeds known market comparables, adjust ValLogMu/ Sigma—benchmarks matter.
Be explicit about which fees are buyer vs seller: many users mistakenly include buyer premium in seller proceeds.
Run sensitivity analysis: vary ProbIssue, Reserve, and BuyerPremium tiers to see which levers matter most — use sound data engineering when you aggregate scenario outputs.
Think in probabilities: headlines report top estimates (e.g., up to $3.5M). The simulator converts those headlines into probabilities and expected values.
Document assumptions: each simulation sheet should include a short assumptions block so students and clients can audit choices.

“A valuation is only as useful as its assumptions—make them explicit, test them, then decide.”

How to implement Monte Carlo inside Excel 365 / Google Sheets

If you have Excel 365 or Google Sheets with dynamic arrays, you can perform large simulations directly in-sheet using RAND(), RANDARRAY(), NORM.INV and array formulas. For Excel users, using LET and LAMBDA improves clarity and reusability. If your spreadsheet slows, reduce iterations to 2,000 for exploratory work and increase when you finalize. If you prefer to offload heavy runs, export your sheet and automate runs or reporting to a small reproducible micro-app or backend job (see micro-app starter kits).

Sample Excel formulas (compact)

  // Draw valuations for N bidders (Excel 365)
  =EXP(NORM.INV(RANDARRAY(N), Params!ValLogMu, Params!ValLogSigma))

  // Hammer price approximation
  =LET(w, FILTER(wtps, wtps >= Params!EntryThreshold),
       n, ROWS(w),
       inc, MAX(Params!FixedInc, Params!IncPct * LARGE(w,2)),
       IF(n=0,0, IF(n=1, MAX(w), MIN(MAX(w), LARGE(w,2)+inc)))
  )

Teaching and classroom uses

For students and teachers, this template is ideal for:

Demonstrating order statistics and auction theory with hands-on simulation.
Comparative statics labs: change reserve and observe expected proceeds.
Provenance modules: show how due diligence changes expected value.

Limitations and what this model does not capture

No model is perfect. This spreadsheet intentionally balances complexity and transparency.

It approximates strategic bidding rather than solves a full game-theoretic equilibrium—this is often sufficient for price forecasting and decision support.
It does not model collusion, shill bidding, or legal disputes in detail—those require case-level legal analysis.
It treats buyer premium and seller commissions as deterministic—if you need stochastic fee schedules, add them as random draws.

Actionable takeaways

Build the Params sheet first and document every assumption.
Use a lognormal valuation distribution for right-skewed art prices and validate against comparables.
Model provenance as a separate binary risk—its effect on sale probability and discount can dominate small changes in buyer premium.
Run scenario comparisons for at least three reserve levels and present both P(Sale) and ExpectedSellerProceeds to sellers.
Use dynamic-array functions to run 2k–10k iterations in-sheet; if you need more, export to Python/R for backend simulation (and automate exports responsibly — see cloud workflow automation).

Where to go next (2026-ready extensions)

Plug in live auction-house buyer premium schedules (they changed several times in 2024–2025).
Integrate provenance scores from AI-based provenance detectors (emerged late 2025) to set ProbIssue by pedigree confidence.
Publish a reproducible scenario report (one-click export of assumptions + percentile results) for clients or LMS embedding (consider hardware and performance and edge friendly distribution patterns like automated exports).

Final note: why this approach beats rule-of-thumb pricing

Headlines report a single top-end estimate. Buyers, sellers and students need distributions, not single numbers. Modeling bid dynamics, reserve mechanics, buyer premiums and provenance risk gives you the probabilistic language to negotiate intelligently. In the Hans Baldung Grien example, the calculator turns “could fetch up to $3.5M” into a structured risk–reward assessment you can act on.

Call to action

Download the ready-made Auction Valuation Simulator template at Calculation.Shop to try the Hans Baldung Grien scenarios (Excel 365 and Google Sheets versions included). Use the built-in scenario manager to compare reserves, provenance strategies and buyer-premium schedules. Want a walkthrough video or classroom-ready worksheet? Subscribe to our template bundle and get weekly updates tuned to 2026 auction-house fee changes and provenance tooling.

Unknown

Contributor

Senior editor and content strategist. Writing about technology, design, and the future of digital media. Follow along for deep dives into the industry's moving parts.