To calculate one-day historical VaR at 99% confidence in Excel, use the first percentile of portfolio returns and convert that return into a positive dollar loss. With daily decimal returns in B2:B101 and portfolio value in D1, enter =-PERCENTILE.INC(B2:B101,0.01)*$D$1. This estimates the loss that was exceeded in about 1% of the observations in your sample; it does not predict the largest possible loss.
VaR means value at risk. The result depends on your data, the portfolio value, and the time period represented by each observation. The steps below use the historical method, which estimates the threshold from past portfolio returns rather than assuming a particular return distribution.
What You’ll Need
- Microsoft Excel for Microsoft 365 on Windows. The functions used here are also available in current Excel for Mac and Excel for the web, although ribbon locations and some controls may look different.
- A consistent series of historical returns for the portfolio, ideally one return per trading day. Use returns for the portfolio you want to measure, not a single security unless that security is the portfolio.
- The current portfolio value if you want VaR stated in currency. The formula can also report VaR as a percentage without a portfolio value.
- Enough observations to make the estimate meaningful. A 99% tail is based on the worst 1% of observations, so a very short sample gives a particularly unstable estimate.
Step-by-Step
- Choose the VaR definition and horizon. These instructions calculate one-day, 99% historical VaR. If each row is a daily return, the result is a one-day estimate; if each row is a weekly return, it is a one-week estimate. Do not label a daily result as monthly VaR without a separate, justified method for changing the horizon. Historical VaR is different from a parametric VaR that assumes a statistical distribution.
- Set up a clean worksheet. Put dates in column A and portfolio returns in column B, with one observation on each row. For example, use A1 for Date and B1 for Return, then enter the first observation in row 2. Enter returns as percentages or decimal equivalents: a loss of 2% can be stored as -2% or -0.02. Excel treats those as the same value when the cell is formatted appropriately. Avoid mixing percentage points, dollar P&L, and decimal returns in one column.
- Check what the returns represent. Use returns calculated consistently for the same portfolio and interval. Decide whether returns include dividends, fees, or other relevant cash flows, and apply that choice consistently across the history. Check that dates are in order, that each value is numeric, and that there are no accidental blanks or text entries inside the selected range. A return series for a changing portfolio may not describe the risk of today’s holdings.
- Enter the portfolio value. Put the current market value of the portfolio in D1, for example 100000, and label the cell so it is clear what it contains. Format it as currency if useful. This value scales a percentage loss into a currency estimate; it does not alter the historical percentile. If you only need percentage VaR, you can skip this cell and report the return threshold as a positive percentage loss instead.
- Calculate the 1st percentile of returns and convert it to a loss. With decimal daily returns in B2:B101 and the portfolio value in D1, enter this formula in an empty cell:
=-PERCENTILE.INC(B2:B101,0.01)*$D$1. The 0.01 argument selects the lower 1% tail of returns. The minus sign changes a negative return threshold into a positive VaR amount, while the dollar signs keep the value reference fixed if you copy the formula. Format the result as currency. To display percentage VaR instead, with the same returns in B2:B101, use=-PERCENTILE.INC(B2:B101,0.01)and format the result as a percentage. - Verify the sign and interpret the number. If the formula returns a positive amount, it represents the estimated loss threshold under this convention. If it returns a negative amount, inspect the returns and formula: the series may have no negative observations, or the sign may have been reversed. A one-day VaR of $9,901 means the model’s historical threshold is a loss of about $9,901; it does not mean the portfolio cannot lose more.
- Check the calculation against a simple example. Suppose B2:B101 contains 100 daily returns running from -10.0% to -0.1% in 0.1-percentage-point steps, and D1 contains $100,000. This is an artificial arithmetic example, not realistic market data. PERCENTILE.INC interpolates at position 1 + (100 – 1) × 0.01 = 1.99 in the ascending return series. The 1st-percentile return is -9.901%, so the formula returns a VaR of $9,901. The result is positive because the formula negates the negative return threshold.
- Record the assumptions beside the output. Label the result with its confidence level, method, currency, and horizon, such as “Historical VaR, 99%, one trading day.” Note the sample start and end dates and the portfolio value used. That context makes it much less likely that someone will mistake the result for a forecast, a maximum loss, or a VaR estimate over a different period.
If your input is already a column of positive loss amounts rather than returns, use the 99th percentile of those losses instead. With positive daily loss amounts in C2:C101, enter =PERCENTILE.INC(C2:C101,0.99). The 99th percentile of losses is the matching tail threshold. Do not apply the negative-return formula to positive losses: that would select the wrong tail and may produce a misleading sign.
Common Mistakes
- Using the 99th percentile of returns. For returns, the severe losses are at the low end, so use the 1st percentile and negate it. The 99th percentile of returns usually describes a strong positive return, not a loss threshold. For a column of positive losses, use the 99th percentile instead.
- Mixing units. If returns are stored as -0.02 for -2%, multiplying by portfolio value gives the correct scale. If you type -2 to mean -2% and leave it as a number, Excel interprets it as -200%. Use percentage formatting or convert the values consistently before calculating VaR.
- Including the wrong cells. A header, total, blank, unrelated value, or extra future row can distort the result or trigger an error. Confirm the first and last observation in the formula range, and expand the range when you add data. Do not include the portfolio value cell in the return range.
- Using too little history. With 100 observations, the worst 1% is roughly one observation, with interpolation depending on the percentile function. With fewer than 100 observations, there may be no observation in the 1% tail at all. A number can still be calculated, but it may not be a stable estimate of extreme losses.
- Calling VaR a worst-case loss. VaR marks a percentile threshold. It says little about how large losses might be after that threshold is crossed. Expected shortfall, also called conditional VaR in some contexts, is a different measure that averages losses in the tail.
- Ignoring portfolio changes or dependence. Past returns for a different mix of holdings may not represent current portfolio risk. Also, scaling a one-day estimate to a longer horizon by multiplying by the square root of the number of days relies on assumptions that may not hold. Treat horizon changes as a modeling decision, not an automatic Excel adjustment.
Tips
- Keep raw observations intact and place the VaR calculation in a clearly labeled summary area. This helps you audit the formula without overwriting source data.
- Use a fixed range while validating the result, then update it deliberately as new observations arrive. If the range changes over time, record the sample dates so successive results remain comparable.
- Consider calculating VaR for several dates or portfolios in separate, clearly labeled cells. Make sure every comparison uses the same return interval, confidence level, method, and consistent treatment of cash flows.
- Check the sign visually by sorting or filtering the returns from smallest to largest. The lower tail should contain the most negative returns. A percentile formula does not require sorted data, but sorting can help you spot a reversed series or unusual values.
- Use PERCENTILE.INC when you want the inclusive percentile calculation used in the examples. PERCENTILE.EXC is another real Excel function, but it uses a different convention and may return an error for some small samples or percentile settings. Do not switch functions without noting the change.
- Keep the estimate in context. Historical VaR is sensitive to the selected sample and may understate risk when the history excludes unusual market conditions. It is a risk summary, not a guarantee or a substitute for examining stress scenarios.
Troubleshooting
- The formula shows #NUM!. Check that the percentile argument is between 0 and 1 and that the selected range contains numeric observations. For the returns method, the percentile argument should be 0.01; for positive loss amounts, it should be 0.99. Confirm that your range is not empty.
- The result is unexpectedly negative. Confirm that losses in the return column are negative. If the data records loss amounts as positive numbers, use the positive-loss formula instead. If all returns are positive, the lower percentile may also be positive, so negating it produces a negative result; check whether the sample truly contains no losses or whether loss signs were entered incorrectly.
- The answer is off by a factor of 100. Inspect the stored values, not just their display format. A cell displayed as 2% should usually store 0.02. If it stores 2, the formula will treat it as 200% unless you divide by 100 or correct the input.
- The formula returns #VALUE! or Excel does not calculate it. Check for text in the data range, including numbers imported as text. Also check whether your regional Excel settings expect semicolons rather than commas between function arguments. The formulas here use commas, as requested; change separators only if Excel’s regional settings require it.
- Excel gives a different result from another tool. Compare the sample range, treatment of blanks, return signs, confidence level, and percentile convention. Inclusive and exclusive percentile calculations can differ, especially with small samples. Make sure the other result is also historical VaR, rather than parametric VaR or VaR calculated from dollar losses.
Frequently Asked Questions
What does 99% confidence mean for VaR?
It means the estimated loss threshold is exceeded in about 1% of observations under the method and sample used. It does not mean there is a 99% guarantee against losses, nor does it describe the size of losses beyond the threshold.
Why do I use the 1st percentile of returns?
Returns that represent losses are negative and sit at the low end of the return distribution. The 1st percentile selects that lower tail; negating the result reports the threshold as a positive loss amount.
Can I calculate VaR from profit-and-loss data?
Yes. If the observations are positive loss amounts, calculate their 99th percentile. If the data is signed P&L, where losses are negative, use a lower-tail percentile and reverse the sign, taking care that the units and sign convention are consistent.
How many observations do I need?
There is no single number that makes a VaR estimate reliable in every case. A 99% estimate needs enough history to represent the extreme tail; with 100 observations, the tail is informed by approximately one observation. More data can help, but older observations may represent a different portfolio or market environment.
Can I use the same formula for 95% VaR?
Yes. For returns in B2:B101 and portfolio value in D1, replace 0.01 with 0.05 in the formula. That selects the 5th percentile of returns. Label the result as 95% VaR so it is not confused with the 99% estimate.
Is VaR the same as expected shortfall?
No. VaR is a loss threshold at a chosen percentile. Expected shortfall estimates the average loss among observations beyond a selected tail threshold. The two measures answer different questions and should not be treated as interchangeable.
Related
- Excel Maximum Row Height: The 409-Point Limit
- Excel Max Date: What Is the Latest Date Excel Supports?
- Excel Max Absolute Value: Find the Largest Magnitude
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Column Grouping: How to Collapse ColumnsTo group columns in Excel for Microsoft 365 on Windows, select the column letters you want to fold…
- Google Sheets Date Format dd/mm/yyyy: Formula and StepsTo show dates as dd/mm/yyyy in Google Sheets, select the date cells and choose Format > Number >…
- Excel Paste Special Shortcut: Keys and OptionsIn Excel for Microsoft 365 on Windows, copy the cells, select the destination, and press Ctrl+Alt+V to open…
- Out of Office in Outlook: Set It Up on Desktop or MobileTo set an out-of-office reply in Outlook, open Automatic Replies for the account you want to use, turn…
- Excel Middle Value Extraction: Find the MedianTo get the middle value of a numeric list in Excel, use =MEDIAN(A2:A7) when the values are in…
- How to Add Page Numbers in MLA Format in WordTo add MLA-style page numbers in Word, insert an automatic page number in the top-right header, then place…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.