Historical VaR in Excel with PERCENTILE.INC

What historical VaR tells you

Value at Risk is a loss threshold with a confidence level and a horizon. A 1-day 95% VaR of USD 156,900 means: on 95% of days the portfolio should not lose more than USD 156,900, and on about 1 day in 20 it will. VaR is not the maximum loss. It says nothing about how bad the bad days are, which is what expected shortfall adds.

The historical simulation method makes no assumption about the shape of the return distribution. It asks: if the next day looks like one of the last N days, what happens to the portfolio we hold today?

Step 1: returns and P&L on today's portfolio

Start with daily returns. From prices, the simple return is =B3/B2-1, filled down. Then apply every return to today's portfolio value: =B2*$C$23. The $ signs lock the portfolio value while the return moves down the rows.

Using today's value matters. The portfolio was smaller or larger in the past, and its historical P&L in dollars mixes market moves with changes in size. VaR is a statement about the risk of the current position.

Step 2: read the loss at the percentile

The 95% VaR is the 5th percentile of the P&L, shown as a positive loss: =-PERCENTILE.INC(C2:C21,0.05). PERCENTILE.INC interpolates between ranked values. With 20 days the 5th percentile sits at position 1.95, between the worst day (−212,000) and the second worst (−154,000): −212,000 + 0.95 × 58,000 = −156,900, so the VaR is USD 156,900.

Some desks avoid interpolation and take a ranked day directly with SMALL, which gives 154,000 for the second-worst day here. With a realistic window of 250 days, PERCENTILE.INC at 5% falls between the 13th and 14th worst days, and the choice of method moves the result much less. The example uses 20 days only to keep the table readable. Use at least one year, often two, of daily data.

Worked example: historical VaR from 20 daily returns

Twenty synthetic daily returns of an equity fund (yellow inputs) applied to today's portfolio value of USD 10,000,000 in C23. Rows 24 to 31 calculate the 1-day 95% historical VaR, the number of days beyond it, expected shortfall, the ranked-day alternative, the 10-day VaR, the daily volatility and the parametric VaR for comparison.

Worked example: historical VaR from 20 daily returns

	A	B	C
1	Day	Daily return	P&L on today's portfolio
2	1	0.42%	42,000
3	2	-0.81%	-81,000
4	3	1.15%	115,000
5	4	-0.23%	-23,000
6	5	0.67%	67,000
7	6	-1.54%	-154,000
8	7	0.31%	31,000
9	8	0.89%	89,000
10	9	-0.47%	-47,000
11	10	-2.12%	-212,000
12	11	0.58%	58,000
13	12	0.12%	12,000
14	13	-0.96%	-96,000
15	14	1.43%	143,000
16	15	-0.35%	-35,000
17	16	0.74%	74,000
18	17	-1.28%	-128,000
19	18	0.21%	21,000
20	19	0.49%	49,000
21	20	-0.62%	-62,000
22
23	Portfolio value today		10,000,000
24	1-day 95% historical VaR		156,900
25	VaR as % of the portfolio		1.57%
26	Days with a loss bigger than VaR		1
27	Expected shortfall: average loss beyond VaR		212,000
28	Second-worst day (SMALL, no interpolation)		154,000
29	10-day VaR (√10 scaling)		496,161
30	Daily volatility		0.94%
31	Parametric 95% VaR for comparison		154,819

Synthetic returns, shortened to 20 days so the table stays readable. A real historical VaR uses at least 250 trading days. Educational only, not investment advice.

Step 3: check it and add expected shortfall

Count the days that lost more than the VaR: =COUNTIF(C2:C21,"<"&-C24) returns 1 of 20 days, the 5% you expect. Over a long history this count is the basis of VaR backtesting.

Expected shortfall (also called CVaR) is the average loss on the days beyond VaR: =-AVERAGEIF(C2:C21,"<"&-C24) = USD 212,000. With one tail day it equals the worst day. Bank regulation under the Basel market-risk framework uses expected shortfall at 97.5% because it looks into the tail that VaR ignores.

10-day VaR and parametric VaR for comparison

A 1-day VaR is scaled to a longer horizon with the square-root-of-time rule: 156,900 × √10 = USD 496,161. The rule assumes independent daily returns with the same volatility, which real markets only roughly follow.

The parametric (variance-covariance) method assumes normal returns instead: VaR = −NORM.S.INV(0.05) × σ × value = 1.645 × 0.94% × 10,000,000 = USD 154,819. The two are close on this calm sample. On real data with fat tails, historical VaR at 99% usually comes out higher than the normal estimate. Volatility is the same σ used in the Sharpe ratio.

Limits of historical VaR

• The window decides the answer. A calm year gives a low VaR, and a crash drops out of the window on a fixed date, so VaR jumps for no new reason.
• Only the past. A scenario that never happened in the window cannot appear. Stress tests fill that gap.
• Few tail points. A 99% VaR from 250 days rests on two or three observations.
• Backtest it. Compare each day's VaR with the next day's actual P&L and count the exceptions against what the confidence level allows.

Common mistakes

• A negative VaR. PERCENTILE.INC returns a loss as a negative number. Put a minus sign in front so VaR is reported as a positive amount.
• 0.95 instead of 0.05. PERCENTILE.INC(P&L,0.95) reads the best days, not the worst. For 95% VaR on P&L use the 5th percentile.
• Historical dollar P&L. Past P&L reflects a different portfolio size. Apply past returns to today's value.
• A window that is too short. Twenty days, as in this example, puts the whole VaR on one or two observations. Use 250 days or more.
• VaR read as the worst case. On 5% of days the loss is bigger, and here the worst day lost USD 212,000. Report expected shortfall next to VaR.
• Scaling with the wrong root. A 10-day VaR uses √10, not 10 or √250. √250 turns daily volatility into annual volatility.