Value at Risk (VaR) in Excel: Historical, Parametric & Monte Carlo (2026)

July 1, 2026 · VeloraAI Team
Data Analysis Financial Modeling Excel

On October 19, 1987 — Black Monday — the S&P 500 lost 20.5% in a single session, a move that a normal distribution said should occur roughly once every 10²⁰ years. That single day is why every trading desk, risk committee, and Basel-regulated bank now reports value at risk every morning. VaR compresses portfolio risk into one sentence: "We are 95% confident we will not lose more than $X over the next Y days." If you can build a value at risk model in Excel, you can answer the question your CFO, CRO, or fund investors ask before markets open.

This guide walks through the three canonical VaR methodologies — historical, parametric, and Monte Carlo — with real formulas you can drop into Excel today. We also cover Expected Shortfall (the metric that fixed VaR's biggest blind spot), portfolio VaR with a covariance matrix, and how to backtest whether your model actually works.

What Is Value at Risk (VaR)?

Value at Risk (VaR) is the maximum loss a portfolio is expected to suffer over a defined time horizon at a given confidence level, under normal market conditions. A one-day 95% VaR of $1M means there is a 5% probability the portfolio will lose more than $1M in one day. It answers how bad a bad day looks.

VaR became the industry standard after J.P. Morgan's 1994 RiskMetrics publication and is now embedded in Basel III capital rules, the SEC's Rule 18f-4 for mutual funds, and virtually every hedge fund LP report. It is intuitive to executives, aggregates across asset classes, and — critically — it is auditable.

ℹ️ Note: VaR is a quantile, not a worst-case number. It says nothing about how large the loss becomes once you enter the tail. Expected Shortfall (covered below) does.

How Do You Calculate Value at Risk in Excel?

You calculate VaR in Excel using one of three methods: historical simulation (rank actual past returns and read off the quantile), parametric / variance-covariance (assume a normal distribution and use NORM.S.INV), or Monte Carlo simulation (draw thousands of random return paths and read the quantile). Each has trade-offs in accuracy, computational cost, and how well it captures fat tails.

The three methods compared

Method Data Needed Excel Formula Captures Fat Tails? Best For
Historical VaR 250+ days of returns PERCENTILE.INC Yes (if in the data) Illiquid or nonlinear books
Parametric VaR Mean, standard deviation NORM.S.INV × σ No (assumes normal) Liquid, linear portfolios
Monte Carlo VaR Distribution assumptions NORM.INV(RAND(),...) Yes (chosen distribution) Derivatives, path-dependent
Delta-Normal (portfolio) Weights + covariance matrix MMULT + SQRT No Multi-asset equity books
Expected Shortfall Same as chosen VaR method AVERAGEIF on tail Yes Complement to any VaR 
graph TD
    A[Portfolio Returns] --> B{Method?}
    B -->|Historical| C[Rank returns, take PERCENTILE.INC at 1-alpha]
    B -->|Parametric| D[Estimate mu, sigma - use NORM.S.INV]
    B -->|Monte Carlo| E[Simulate N paths - take quantile of P&L]
    C --> F[VaR at confidence level]
    D --> F
    E --> F
    F --> G[Expected Shortfall = mean of losses beyond VaR]
    G --> H[Backtest with Kupiec test]

Method 1: Historical VaR in Excel

Historical VaR assumes the future looks like the past. You take a rolling window of daily returns (usually 250 trading days ≈ one year), rank them, and read the loss at the chosen quantile. No distribution assumptions, no covariance matrix — just data.

Step-by-step

  1. In column A, paste the last 250 trading days of portfolio value (P_t).
  2. In column B, compute daily returns: =LN(A3/A2) (log returns are additive over time; simple returns work too — pick one and be consistent).
  3. In cell D1, enter your confidence level, e.g. 0.95.
  4. In cell D2, compute the 1-day historical VaR as a return:
=-PERCENTILE.INC(B3:B252, 1-D1)
  1. Convert to a dollar VaR using the current portfolio value in E1:
=D2 * E1

The negative sign flips the fifth-percentile return (which is negative) into a positive loss number, which is how risk reports read.

💡 Pro Tip: Use PERCENTILE.INC (linear interpolation) for smoother quantile estimates than SMALL() indexing into a rank. It matches the RiskMetrics convention used by most banks.

Scaling to a 10-day VaR

Regulators (Basel) usually require a 10-day VaR. The square-root-of-time rule scales:

=D2 * SQRT(10) * E1

This is only valid if daily returns are IID (independent and identically distributed) with zero autocorrelation. For most equity portfolios it is a reasonable approximation; for mean-reverting assets (short-term rates, some commodities) it overstates risk.

⚠️ Warning: Historical VaR gives zero weight to any scenario that hasn't happened in your window. Feed it 250 days of a calm 2019 tape and your 2020 COVID drawdown is literally uncomputable. Use a window that spans at least one stress regime, or overlay a stressed-VaR calculation.

Method 2: Parametric (Variance-Covariance) VaR in Excel

Parametric VaR — also called the variance-covariance or delta-normal method — assumes returns are normally distributed. You only need two numbers: the mean and standard deviation. It is fast, closed-form, and reproducible, which is why it dominates end-of-day desk reports.

Single-asset parametric VaR

Given daily log returns in B3:B252:

Mean:      =AVERAGE(B3:B252)               -> cell F1
StdDev:    =STDEV.S(B3:B252)               -> cell F2
Z-score:   =NORM.S.INV(1-0.95)             -> cell F3 (returns -1.6449)
VaR (%):   =-(F1 + F3*F2)                  -> cell F4
VaR ($):   =F4 * E1                        -> cell F5

For a 99% VaR, replace 0.95 with 0.99 — NORM.S.INV(0.01) returns -2.3263. The larger the z-score in magnitude, the deeper into the tail you go.

Example: A portfolio worth $10M with a daily return mean of 0.04% and daily volatility of 1.20% has a 1-day 95% parametric VaR of -(0.0004 + (-1.6449)(0.012)) × $10M ≈ $193,400. Over 10 days that scales to $193,400 × √10 ≈ $611,600.

Portfolio parametric VaR (multi-asset)

For a portfolio of n assets, VaR is driven by the covariance matrix. The workflow in Excel:

  1. Build daily-return columns for each asset in a table (e.g. B3:F252 for 5 assets).
  2. Compute the covariance matrix using COVARIANCE.S in a 5×5 block, or use the Data > Data Analysis > Covariance tool for a one-shot build.
  3. Store portfolio weights in a column vector H3:H7 (must sum to 1).
  4. Compute portfolio variance with MMULT:
=MMULT(TRANSPOSE(H3:H7), MMULT(covariance_matrix, H3:H7))

Enter this as an array formula (Ctrl+Shift+Enter in older Excel; automatic in Excel 365 with dynamic arrays). It returns a single cell containing σ²ₚ.

  1. Portfolio standard deviation and VaR:
Portfolio Vol:  =SQRT(portfolio_variance)          -> cell J1
Portfolio VaR:  =-NORM.S.INV(0.05) * J1 * E1       -> cell J2

💡 Pro Tip: Wrap the whole calculation in a single LET for readability: =LET(sigma, SQRT(MMULT(TRANSPOSE(w), MMULT(C, w))), z, NORM.S.INV(0.05), -z*sigma*V). It documents itself and computes once.

Method 3: Monte Carlo VaR in Excel

Monte Carlo VaR simulates thousands of possible next-day returns from a chosen distribution and reads the quantile off the empirical distribution of simulated P&L. It handles nonlinear positions (options, structured notes) that break the delta-normal assumption.

Minimal Excel setup

  1. Estimate μ and σ from the historical return series (as in the parametric method).
  2. In cells A2:A10001, generate 10,000 simulated returns:
=NORM.INV(RAND(), $F$1, $F$2)
  1. In cell B1, compute the 1-day 95% Monte Carlo VaR:
=-PERCENTILE.INC(A2:A10001, 0.05) * E1
  1. Press F9 to reroll — the answer should be stable to within a few hundred dollars if N ≥ 10,000.

For fat tails, swap the normal draw for a Student's-t: =F1 + F2 * T.INV(RAND(), 6) uses six degrees of freedom, which fits equity returns better than the normal. We covered the full simulation architecture in our Monte Carlo simulation in Excel guide, including how to model correlated assets with Cholesky decomposition.

⚠️ Warning: RAND() recalculates on every workbook change. Freeze your simulation with Copy → Paste Special → Values before showing results to anyone; otherwise your VaR shifts every keystroke.

Portfolio VaR with a Covariance Matrix

Real books hold dozens of assets. The single most useful Excel skill for a risk analyst is turning a returns table into a covariance matrix and a portfolio VaR in three formulas.

The covariance matrix in Excel 365

Given a return table Returns[Asset1:Asset5]:

=COVARIANCE.S(Returns[Asset1], Returns[Asset1])   /* variance of asset 1 */

Or, faster, use dynamic arrays: =BYCOL(Returns, LAMBDA(c, BYCOL(Returns, LAMBDA(r, COVARIANCE.S(c, r))))) — this spills the full n×n matrix in one call.

Marginal and component VaR

Once you have the covariance matrix Σ and weights w:

  • Marginal VaR for asset i: MVaR_i = (Σw)_i / σ_p × z
  • Component VaR for asset i: CVaR_i = w_i × MVaR_i

Component VaRs sum to total VaR — this is what makes them the standard tool for risk-budget attribution. In Excel:

Marginal VaR vector:  =-NORM.S.INV(0.05) * MMULT(Sigma, w) / portfolio_sigma
Component VaR vector: =H3:H7 * marginal_var_vector

ℹ️ Note: Component VaR is what your PM cares about — it tells them which position is driving portfolio risk. A 5% position contributing 40% of VaR is a concentration flag before it becomes a P&L headline.

Expected Shortfall: What VaR Misses

VaR tells you the loss threshold; it says nothing about what happens beyond it. A portfolio can have a well-behaved 95% VaR and a catastrophic 99.5% loss. Expected Shortfall (ES) — also called Conditional VaR or CVaR — is the average loss conditional on breaching VaR.

Under Basel FRTB (in force since 2023 for internal-models banks), Expected Shortfall replaced VaR as the primary market-risk metric precisely because it is a coherent risk measure (VaR is not — it can fail sub-additivity).

Historical ES in Excel

Given the returns in B3:B252:

VaR (return):  =PERCENTILE.INC(B3:B252, 0.05)
ES (return):   =AVERAGEIF(B3:B252, "<="&F6, B3:B252)   /* F6 = the VaR return */
ES ($):        =-ES_return * E1

For a parametric normal distribution with volatility σ at confidence level α:

ES = sigma * NORM.S.DIST(NORM.S.INV(alpha), FALSE) / (1 - alpha)

In Excel:

=F2 * NORM.S.DIST(NORM.S.INV(0.95), FALSE) / (1 - 0.95) * E1

Under normality, the ES-to-VaR ratio at 95% is a constant ≈ 1.253. If your empirical ES/VaR ratio is materially higher, your returns have fatter tails than the normal model assumes — a strong signal to switch to historical or Student's-t VaR.

Backtesting Your VaR Model

A VaR model that has never been checked against realized losses is a compliance document, not a risk tool. The standard test is the Kupiec proportion-of-failures (POF) test, which asks whether the observed exception rate matches the model's stated confidence level.

Setting up the backtest

Over an out-of-sample window of N days (typically 250):

  1. For each day t, compute VaR_t using data up to day t-1 (rolling window).
  2. In column D, flag exceptions: =IF(actual_return_t < -VaR_t, 1, 0).
  3. Sum the exceptions: =SUM(D:D) gives x, the number of breaches.

Expected breaches at 95% VaR over 250 days = 12.5. You should see 6 to 20 exceptions to pass Kupiec at the 5% significance level. Fewer than 6 means you are over-reserving; more than 20 means the model understates risk and, under Basel, your regulatory multiplier climbs from 3.0 to 4.0.

Kupiec likelihood ratio in Excel

=-2 * LN(((1-0.05)^(250-x) * 0.05^x) / ((1-x/250)^(250-x) * (x/250)^x))

Reject the model if this value exceeds CHISQ.INV(0.95, 1) ≈ 3.84.

💡 Pro Tip: Layer a traffic-light framework — green (0–4 exceptions), yellow (5–9), red (10+) — on top of the Kupiec test to match how regulators actually read it.

Common VaR Pitfalls in Excel

The failure modes below turn up in almost every real risk audit. Fix them once and your model earns its keep.

  • Mixing return conventions. Historical VaR from log returns, parametric VaR from simple returns — the numbers won't reconcile. Pick one and enforce it.
  • Short lookback windows. 60 days of data won't capture a fat tail. Use 250+ for daily VaR; longer for illiquid books.
  • Ignoring stale prices. An illiquid bond marked at the same price for a week produces artificially low volatility. Use a Bloomberg BVAL or a modeled price series.
  • Static covariance matrices. Correlations rise in crises. Use an EWMA (exponentially weighted moving average) or GARCH-updated covariance for portfolios sensitive to regime shifts.
  • Confusing 1-day with 10-day VaR. Regulators quote 10-day; risk desks think 1-day. Always label the horizon.
  • Not backtesting. A VaR you never check is a story you tell yourself.

Frequently Asked Questions

What is the difference between historical VaR and parametric VaR?

Historical VaR uses actual past returns with no distribution assumption — it captures fat tails and asymmetries that were in the data. Parametric VaR assumes returns are normally distributed and needs only the mean and standard deviation. Historical is more robust for nonlinear or illiquid books; parametric is faster and works well for large, liquid, linear portfolios.

Is Value at Risk the same as Expected Shortfall?

No. VaR is a quantile: the loss threshold at a chosen confidence level. Expected Shortfall is the average loss beyond that threshold. VaR tells you where the tail begins; ES tells you how deep it goes. Basel FRTB replaced VaR with 97.5% Expected Shortfall as the primary market-risk capital measure in 2023.

How much data do I need to calculate VaR in Excel?

For daily VaR, use at least 250 trading days (one year) so your window covers a full business cycle. Basel III's stressed-VaR requirement asks for a 12-month window that includes a period of significant stress. Fewer than 100 observations makes both historical percentile estimates and parametric volatility estimates too noisy to trust.

Can I calculate VaR for a portfolio with options in Excel?

Yes, but delta-normal VaR will misprice the nonlinearity. For a portfolio with meaningful convexity, use Monte Carlo VaR: simulate underlying price paths, revalue the option (Black-Scholes or a lattice), and take the quantile of simulated P&L. See our Black-Scholes options pricing in Excel guide for the pricing engine.

What confidence level should I use — 95% or 99%?

Both. 95% VaR is used for internal desk-level risk limits (frequent breaches you can learn from). 99% VaR is used for capital allocation and Basel reporting (rare, severe tail). Fund investors typically ask for 95%; regulators typically ask for 99% one-day or 99% 10-day. Report both.

From VaR Model to Live Risk Report

A working VaR model in Excel is 200 formulas and a covariance matrix. A risk report is that model plus daily P&L, breach flags, component-VaR attribution, backtest history, and a stress-scenario overlay — all refreshed before the 8am risk meeting.

VeloraAI's Excel add-in shortens that last mile: pull the returns matrix in a natural-language query, generate the MMULT / LET covariance formulas from a plain description of the portfolio, and have the add-in flag any single position whose component VaR crosses a threshold. Next step: pick one book you already model manually, and run all three VaR methods on it this week. The gap between historical and parametric VaR is often the most honest report on your portfolio's real fat-tail exposure.