Diagnosing Gaussian VaR Differences Between Excel and R

Summary

The document compares variance-covariance value-at-risk calculations in Excel and R for a portfolio of four stocks. It reports that the results agree at a confidence level of 0.5 but differ at 0.95, and shows R output from a Gaussian VaR calculation using portfolio weights and a component method.

The accompanying R excerpt sketches loading price data and calculating discrete returns. It does not include the Excel formulas, the input data, portfolio weights, or a clear definition of the confidence-level convention used in either calculation. It also contains apparent variable-name inconsistencies and repeats the return calculation, so it is not enough to reproduce or diagnose the mismatch. The document raises a practical risk-measure implementation question but provides no answer or evidence identifying the cause; readers would need to check data alignment, return inputs, sign conventions, and VaR settings in both tools.

Key ideas

• The document compares Gaussian variance-covariance VaR results from Excel and R for a stock portfolio.
• The reported calculations agree at a confidence level of 0.5 but differ at 0.95.
• The R excerpt does not provide enough consistent inputs to reproduce the calculation.
• The source gives no resolution, so the cause of the discrepancy remains unknown.

Tags

# VaR calculation using excel gives different value than VaR using R at all c values except at c=0.5


# VaR calculation using excel gives different value than VaR using R at all c values except at c=0.5












This is VaR calculation in excel using variance-covariance method.

This is VaR calculation in R.

```
> VaR(dat,p=0.5,weights = wts,portfolio_method = "component",method="gaussian")
$`VaR`
[1] -0.02144891

> VaR(dat,p=0.95,weights = wts,portfolio_method = "component",method="gaussian")
$`VaR`
[1] 0.1623596
```

VaR in R and excel is same only for c=0.5.

Can you tell me where I am doing it wrong?

R script

```
library(PortfolioAnalytics)
library(quantmod)
library(PerformanceAnalytics)
library(zoo)
library(plotly)

# Get data
ibm <- read.csv("IBM.csv", header=TRUE)
msft <- read.csv("MSFT.csv", header=TRUE)
aapl <- read.csv("AAPL.csv", header=TRUE)
tsla <- read.csv("TSLA.csv", header=TRUE)

ibm.close = ibm[c(6)]
msft.close = msft[c(6)]
aapl.close = aapl[c(6)]
tsla.close = tsla[c(6)]

# Assign to dataframe
# Get adjusted prices
prices.data <- merge.zoo(ibm.close,msft.close,aapl.close,tsla.close)
prices.data2 <-  ts(data = prices.data)

# Calculate returns
prices.data2.ret = ROC(prices.data2,type = "discrete")[-1,]

# Set names
colnames(returns.data) <- c("ibm","msft","aapl","tsla")

prices.data2.ret = ROC(prices.data2,type = "discrete")[-1,]

VaR(dat,p=0.95,weights = wts,portfolio_method = "component",method="gaussian")
```

Shown in full with attribution under the source's licence. Licence: CC BY-SA 4.0 (Stack Exchange)

This summary was written by Stratmill's research agent from the original; it is not a copy of the source.