Checking VaR results with Excel

In my previous post, I claimed that the VaR at the 99% confidence level for an asset with a mean daily return of $1000 and a daily standard deviation of $500 was $165, i.e. the maximum daily loss under normal market conditions won’t exceed $165, 99% of the time.

This can be easily checked using excel.  Use the Norm.Dist function, replacing x with the VaR amount:

=Norm.dist(-165, 1000, 500, TRUE)

The “True” returns the cumulative distribution function.  So what we’ve told excel to do is return the probability that a normally distributed random variable with a mean of $1000 and standard deviation of $500 takes on a value less than or equal to -$165.  Since we computed the VaR for the 99% confidence level, excel should return the number .01.  You can check that it does (approximately).

We can use this function to compute other values of interest, like the probability that we’ll earn a negative daily return.  The setup is:

=Norm.dist(0, 1000, 500, TRUE) = .02275 = 2.275%.

And, finally, to demonstrate the symmetry of normally distributed variables, calculate the probability that the asset will generate a daily return in excess of $2,165:

(1 – Norm.Dist(2165, 1000, 500, true)) =(1 –  .990) = 1%

This is equivalent to the VaR value, but in the positive direction.  1% of values lie below $-165, and 1% lie above $2,165.  Intuitively we could just add these together to find the probability that a return between $-165 and $2,165 is achieved, but more mathematically we could do so by subtracting the CDF of the smaller number from the larger number:

Pr(-165 < Return < 2,165) = CDF(2,165) – CDF(-165) = F(2,165) – F(-165) = .99 – .01 = .98 = 98%

If we want to change the confidence level, and compute a new VaR, we’ll have to change our z-score of 165 because it depends on the confidence level.  We’ll use the same formula, but this time we’ll use z = -1.645 because 95% of observations are greater than -1.645 (in terms of standardized z units) on a normal curve.  Remember the substitutions z = -z and VaR = -VaR:

1.645 = (VaR – 1000)/500 -> VaR = -177.5, which is really positive $177.5 because we substituted VaR = -VaR.  So our daily return won’t be less than $177.5, 95% of the time.  Notice that $177.5, the 95% VaR, isn’t even a loss, it’s just a return that is 1.645 standard deviations less than average.


About schapshow

Math & Statistics graduate who likes gymnastics, 90s alternative music, and statistical modeling. View all posts by schapshow

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

Open source projects for neuroscience!

Systematic Investor

Systematic Investor Blog

Introduction to Data Science, Columbia University

Blog to document and reflect on Columbia Data Science Class

Heuristic Andrew

Good-enough solutions for an imperfect world

"History doesn't repeat itself but it does rhyme"

My Blog

take a minute, have a seat, look around

Data Until I Die!

Data for Life :)

R Statistics and Programming

Resources and Information About R Statistics and Programming

Models are illuminating and wrong

Data & Machine Learning & Product

Xi'an's Og

an attempt at bloggin, nothing more...

Practical Vision Science

Vision science, open science and data analysis

Big Data Econometrics

Small posts about Big Data.

Simon Ouderkirk

Remote Work, Small Data, Digital Hospitality. Work from home, see the world.


Quantitative research, trading strategy ideas, and backtesting for the FX and equity markets


I can't get no

The Optimal Casserole

No Line Is Ever Pointless

SOA Exam P / CAS Exam 1

Preparing for Exam P / Exam 1 thru Problem Solving


Mathematical statistics for the layman.

%d bloggers like this: