portfolio var in excel

CFA Level III Portfolio Management
#1

I want to calculate the var of a portfolio at the end of a multi year year period with a 95% confidence. Given R (discreet 1 year rate), Sd, T (T is greater than 1)

I can do =(R*T)-(1.65*SQRT(T)*(Sd))

However this is not capturing the impact of compounding with R compounding on itself, it is producing consistent with returns being additive (i compared to my monte carlo simulation sode). What am I doing incorrectly?

#2

Okay I think I answered my question, what i had to do is assume the given R is continuously compounded with and then apply exp() on the above formula. This allows the monte carlo and the analytical method to converge

The sheet is here for anyone who cares to take a look… Thanks

https://1drv.ms/x/s!AgEdDvW0qb2MxA6PAx6uHti5lyIu

#3

Watch out, because of likely GIGO effect.