Suppose you hold a portfolio like this.
Astra Int shares (ASII) with market value of IDR1,000,000,000.00
Indonesia Govt bonds (Indo-17) with market value of IDR1,000,000,000.00
Total market value of your portfolio is IDR2,000,000,000.00
You may wonder how much your portfolio value would be tomorrow in the worst-case scenario.
How do we get that value? Such value is known as Value-at-Risk (VaR).
The procedure:
1. Collect historical daily data for Astra share prices and calculate the daily return.
2. Collect historical daily date for Indonesia govt bonds yields, generate index and calculate the daily return.
3. Generate covariance matrix for share return and bond return.
4. Calculate portfolio variance and standard deviation. In Excel, it can be done using MMULT(MMULT(TRANSPOSE(value),covariancematrix),value)
5. Calculate VaR at 99% confidence level as 2.326 multiply by standard deviation.
6. Tomorrow's worst value = Today's value - VaR
From the example, I found tomorrow's worst case value is IDR1,937,346,708.11
Download the Excel model here >>>
notes
- This service has been inactive since 2010 as my new role in TM1 consulting.
- Please disregard any commercial offers as this website has been archived and transformed into a blog rather than a commercial website.
- Please contact me for more info in BI and Financial Modelling consultancy.
0 comments:
Post a Comment