Penggunaan MS Excel untuk Estimasi Model GARCH(1,1)

Didit Budi Nugroho; Bambang Susanto; Mince M. M. Rosely

doi:10.24198/jmi.v14.n2.17680.71-82

Penggunaan MS Excel untuk Estimasi Model GARCH(1,1)

Didit Budi Nugroho, Bambang Susanto, Mince M. M. Rosely

Abstract

Permasalahan umum yang sering dijumpai dalam banyak studi keuangan yaitu volatilitas tak konstan untuk \emph{return} aset. Suatu pendekatan untuk memodelkan runtun waktu keuangan dengan heteroskedastisitas pada \emph{return} aset yaitu model GARCH. Studi ini bertujuan untuk menunjukkan bagaimana MS Excel dapat digunakan untuk mengestimasi model GARCH(1,1). MS Excel menawarkan suatu kemudahan penghitungan dan mungkin merupakan piranti yang paling banyak digunakan untuk menganalisis data keuangan. Untuk tujuan penyelidikan, studi ini mengadopsi data simulasi dan data riil kurs beli USD terhadap IDR periode 2010--2017. Parameter-parameter dalam model GARCH(1,1) diestimasi menggunakan metode likelihood maksimum dengan bantuan Solver yang tersedia di MS Excel. Hasil empiris pada data simulasi menunjukkan bahwa MS Excel menyediakan keakuratan yang baik untuk menaksir model GARCH(1,1). Aplikasi untuk data riil menghasilkan nilai-nilai optimal untuk model GARCH(1,1) yang serupa dengan yang dihasilkan oleh metode-metode MCMC menggunakan alat bantu Matlab.

Keywords

Excel, GARCH(1,1), kurs beli, volatilitas

Full Text:

PDF (Bahasa Indonesia)

References

Abdalla, S. Z. S., and Winker, P., 2012, Modelling stock market volatility using univariate GARCH models: Evidence from Sudan and Egypt, International Journal of Economics and Finance Volume 4, Issue 8, pp 161--176.

Adanu, K., 2006, Optimizing the GARCH model--An application of two global and two local search methods, Computational Economics, Volume 28, Issue 3, pp 277--290.

Alberg, D., Shalit, H., and Yosef, R., 2008, Estimating stock market volatility using asymmetric GARCH models, Applied Financial Economics, Volume 18, Issue 15, pp 1201--1208.

Alexander, C., 2008, Market Risk Analysis II: Practical Financial Econometrics, John Wiley & Sons, Chichester.

Bollerslev, T., 1986, Generalized autoregressive conditional heteroskedasticity, Journal of Econometrics, Volume 31, Issue 3, pp 307--327.

Brewer, K. D., Feng, Y., and Kwan, C. C. Y., 2012, Geometric Brownian motion, option pricing, and simulation: Some spreadsheet-based exercises in financial modeling, Spreadsheets in Education, Volume 5, Issue 3. Retrieved from http://epublications.bond.edu.au/ejsie/vol5/iss3/4

Brown, R., and Klingenberg, B., 2015, Real estate risk: Heavy tail modelling using Excel. Journal of Property Investment & Finance, Volume 33, Issue 4, pp 393--407.

Christoffersen, P. F., 2012, Elements of Financial Risk Management (2nd ed.), Academic Press, New York.

Epaphra, M., 2017, Modeling exchange rate volatility: Application of the GARCH and EGARCH models, Journal of Mathematical Finance, Volume 7, Issue 1, pp 121--143.

Fan, J., Qi, L., and Xiu, D., 2014, Quasi-maximum likelihood estimation of GARCH models with heavy-tailed likelihoods, Journal of Business and Economic Statistics, Volume 32, Issue 2, pp 178--191.

Francq, C., Lepage, G., and Zakoian, J.-M., 2011, Two-stage non Gaussian QML estimation of GARCH models and testing the efficiency of the Gaussian QMLE, Journal of Econometrics, Volume 165, pp 246--257.

Francq, C., and Zakoian, J.-M., 2004, Maximum likelihood estimation of pure GARCH and ARMA-GARCH processes, Bernoulli, Volume 10, pp 605--637.

French, N., 2006, Value and worth: probability analysis, Journal of Property Investment & Finance, Volume 24, Issue 4, pp 374--380.

French, N., 2015, Spreadsheets for valuations – 50 worked examples using Microsoft Excel 2010, Journal of Property Investment & Finance, Volume 33, Issue 2, pp 205--206.

Giot, P., and Laurent, S., 2004, Modelling daily Value-at-Risk using realized volatility and ARCH type models, Journal of Empirical Finance, Volume 11, Issue 3, pp 379--398.

Harmon, M., 2011, Step-by-step optimization with Excel Solver, Excel Master Series.

Huang, D., Wang, H., and Yao, Q., 2008, Estimating GARCH models: When to use what? Econometrics Journal, Volume 11, Issue 1, pp 27--38.

Kamal, Y., Ghani, U., and Khan, M., 2012, Modeling the exchange rate volatility, using generalized autoregressive conditionally heteroscedastic (GARCH) type models: Evidence from Pakistan, African Journal of Business Management, Volume 6, Issue 8, pp 2830--2838.

Kuka, S., and Karamani, B., 2011, Using Excel and VBA for Excel to learn numerical methods. In The 1st International Sympsosium on Computing in Informatics and Mathematics (pp. 365--376). Retrieved from https://fac.ksu.edu.sa/sites/default/files/739-2470-1-pb.pdf

Kyng, T. J., 2016, Excel implementation of finite difference methods for option pricing, Spreadsheets in Education, Volume 9, Issue 3. Retrieved from http://epublications.bond.edu.au/ejsie/vol9/iss3/2/

Kyng, T. J., and Konstandatos, O., 2014, Multivariate Monte-Carlo simulation and economic valuation of complex financial contracts : An excel based implementation. Spreadsheets in Education, Volume 7, Issue 2. Retrieved from http://epublications.bond.edu.au/ejsie/vol7/iss2/5/

Lawrence, K. D., Klimberg, R. K., and Lawrence, S. M., 2009, Fundamentals of Forecasting Using Excel, Industrial Press, New York.

Levy, G., 2004, Computational Finance: Numerical Methods for Princing Financial Instruments, Elsevier, Oxford.

Nadler, S., and Kros, J. F., 2007, Forecasting with Excel: Suggestions for managers, Spreadsheets in Education, Volume 2, Issue 2. Retrieved from http://epublications.bond.edu.au/cgi/viewcontent.cgi?article=1038&context=ejsie

Namugaya, J., Weke, P. G. O., and Charles, W. M., 2014, Modelling volatility of stock returns: Is GARCH(1,1) enough? International Journal of Sciences: Basic and Applied Research, Volume 16, Issue 2, pp 216--223.

Nugroho, D. B., 2018, Comparative analysis of three MCMC methods for estimating GARCH models. In The 1th International Conference on Engineering and Applied Technology (ICEAT), IOP (forthcoming).

Nugroho, D. B., and Susanto, B., 2017, Volatility modeling for IDR exchange rate through APARCH model with student-t distribution. In AIP Conference Proceedings (Vol. 1868, p. 040005-1--040005-8), AIP Publishing LLC. https://doi.org/10.1063/1.4995120

Nugroho, D. B., Susanto, B., and Pratama, S. R., 2017, Estimation of exchange rate volatility using APARCH-type models: A case study of Indonesia (2010--2015), Jurnal Ekonomi dan Studi Pembangunan, Volume 9, Issue 1, pp 65--75.

Poon, S.-H., and Granger, C. W. J., 2003, Forecasting volatility in financial markets: A review, Journal of Economic Literature, Volume 41, Issue 2, pp 478--539.

Posedel, P., 2005, Properties and estimation of GARCH(1,1) model, Metodološki Zvezki, Volume 2, Issue 2, pp 243--257.

Racicot, F. E., and Theoret, R., 2007, Book review of: Option pricing models & volatility using Excel-VBA (by F. D. Rouah and G. Vainberg), Journal of Derivatives & Hedge Funds, Volume 13, Issue 2, pp 181--183.

Rouah, F. D., and Vainberg, G., 2007, Option Pricing Models and Volatility Using Excel-VBA, John Wiley & Sons, Hoboken.

Ruppert, D., 2011, Statistics and Data Analysis for Financial Engineering, Springer, New York.

Salim, F. C., Nugroho, D. B., and Susanto, B., 2016, Model volatilitas GARCH(1,1) dengan error Student-t untuk kurs beli EUR dan JPY terhadap IDR, Jurnal MIPA, Volume 39, Issue 1, pp 63--69.

Thadewald, T., and Buning, H., 2007, Jarque-Bera test and its competitors for testing normality - A power comparison, Journal of Applied Statistics, Volume 34, Issue 1, pp 87--105.

Tung, H. K. K., Lai, D. C. F., and Wong, Mi. C. S., 2010, Professional Financial Computing Using Excel and VBA. John Wiley & Sons, Singapore.

Williams, N., 2001, Excel for surveyors, Journal of Property Investment & Finance, Volume 19, Issue 3, pp 330--331.

Winston, W. L., 1998, @RISK: Advanced risk analysis for spreadsheets simulating modeling using @Risk, Journal of Property Valuation and Investment, Volume 16, Issue 3, pp 344--345.

DOI: https://doi.org/10.24198/jmi.v14.n2.17680.71-82