modeling daily vs annual


I am running some excel simulations by generating random data from a normal distribution

Assume an asset has an annualized vol of V and return of R, if I run the simulation using annual V and R I get a very different results than if I run it using daily calculated as V/sqr(250) and ((1+R)^(1/250))-1

I know this method is an approximation but there is a large difference… Am I doing something wrong?

Just to be sure, are you re-annualizing the output of your model after you run it using the daily volatility and returns?

It would be good to know more about what you want to do. Assuming the V and R inputs, the first thing that comes to my mind is that you are trying to calculate some Monte Carlo VaR for your asset or portfolio. If it is the case, and if obtaining more frequent observations is not possible (daily/weekly/monthly), I would calculate the annual VaR (since your inputs are annual as well), and then transform it to my preferred frequency (i.e. if daily, divide the VaR (or whatever) by sqrt(250)).

Coming back to your main question - there would be a difference, but not a large one. Let’s assume you are using Brownian Motion for forecating asset returns:

R1 = ln(Sn/S0) = (mu – sigma^2/2)*T + sigma*sqrt(T)*N(0,1) – annual return

R2 = ln(Sn/S0) = (mu/250 – (sqrt(250)*sigma)^2/2)*T + sqrt(250)* sigma*sqrt(T)*N(0,1) – daily return

To annualize R2, you need to multiply it by 250.

R2ann = (mu – 250*(sqrt(250)*sigma)^2/2)*T + 250*sqrt(250)* sigma*sqrt(T)*N(0,1)

You can see the difference by comparing R1 and R2ann.

Thanks, so as a follow up question

Let us say we used daily returns and we got an annualized R of 10% and V of 15% and assume we wanted to simulate using annual periods (portfolio value at the end of every year).

Is it valid to draw from a normal distribution every (with an R of 10% and V of 15%)? Can we assume the annual returns to be normally distributed? Because the product of two normally distributed returns is not normally distributed, so if daily returns are normally distributed multiplying 252 daily returns by each other will not give a normal distribution. However the natural log of annual returns will be normally distributed so can we take that drawn value from above and do exponential(value) and use that in the simulation?

So the question is to generate the annual random values which of these two approaches is better, the results of the first one are portfolio value after one year which is not normally distributed while the second one is giving normally distributed portfolio value after one year. If I



B) (NORM.INV(RAND(),E1,E2))+1

A few remarks and a suggestion:

1st - asset returns are never normally distributed. We assume that they are (to make it easy to work with them), but they are not. To check it, compute daily returns of any stock in SP500 and compute skewness and excess kurtosis.You can’t find even a single stock with normally distributed returns.

2nd - you are not multiplying 250 daily returns. If this is what you are doing, then you are doing it wrong. You should take mean of daily returns (for 1500 daily observations for example), and then multiply it by 250. You will get the annual return. If you assume that daily returns are normally distributed, then multiplying it by a constant will not change its distribution.

The suggestion:

The common industry practice to simulate asset values/returns is to use Brownian motion with drift (or even jump diffusion process). If not familiar with them and if they sound interesting, try to google and have a look. The main feature is that both of those approaches also account for stochastic deviations of the returns, along with the deterministic term. The formula for Brownian motion with drift will be the following:

Sn = S0 * exp((R - V^2/2)*T + V*sqrt(T)*NORMINV(RAND(),0,1))

Agree on all your points and this is what I am doing, however if you multiply daily returns by 250 and assume the resulting distribution is normal you can clearly see that it wont be correct, if you run a simulation generating 250 daily returns and multiply them by each other the result distribution at one year time will be skewed.

​​​​​​However what is giving a similar result to doing 250 daily generations to arrive at the one year is to take the annualized return drawn from a normal distribution (calculated as you mentioned) and applying exponential on that return…

So back to my question is A or B the better approach once you have the annualized vol and return.


I am trying to make sure my simulation is incorrect due to this