HP VaR: 5 simple steps to follow
 

To use HP VaR add-in for MS-Excel, you

          (1) open a (new) workbook, add a new worksheet or select an existing one
               and name it ‘Data’ 

          (2) create a table anywhere in this worksheet to be filled in as follows:

                           - Name ‘Dates’ your first column heading
                           - Enter the pricing dates under this column heading
                           - Enter the name of each asset as column heading to the right of ‘Dates’
                           - Enter the prices under the asset names
                          
               Format your table, as you like, with Excel’s formatting tools
 

Some screenshots
click to maximize



Sheet 'Data'

Correlation matrix

Histogram 'CDF'

Historical VaR

          (3) select ‘Calculate returns and statistics’ under the ‘Value at Risk’ menu.

                           This generates the following outputs:

                          - Asset returns (for a period you specify)
                          - Mean and standard deviation of each asset
                          - Correlation matrix                         
 

          (4) specify the portfolio weight of each asset in the worksheet created by the
               previous macro

          (5) select ‘Estimate probabilities and VaR’ under the ‘Value at Risk’ menu

                           This generates the following outputs:

                          - Portfolio returns
                          - Mean and standard deviation of the portfolio return distribution
                          - Relative frequency distribution (RFD) – table and chart
                          - Cumulative frequency distribution (CFD) – table and chart
                          - Historical VaR (for a period you specify)
                          - Parametric (variance-covariance) VaR (for a period you specify)
                         
               Format the results, as you like, with Excel’s formatting tools

HP VaR's home page