project for fina 462 class
touramirou
HistoricalVolatility10e.xlsmHistorical Volatility
| Inputs: | Results: | ||||||
| Enter the data frequency: | Periodic | Annualized | |||||
| Variance | 0.000051 | 0.012875 | |||||
| Standard Deviation | 0.72% | 11.35% | |||||
| Mean | 0.05% | 12.08% | |||||
| Obs. | Date | Asset Price | Simple Rate of Return | Continuously Compounded Rate of Return | Squared Deviation | ||
| 1416.6000 | NA | NA | NA | ||||
| 1418.3400 | 0.12% | 0.12% | 0.000001 | ||||
| 1409.7100 | -0.61% | -0.61% | 0.000043 | ||||
| 1412.8400 | 0.22% | 0.22% | 0.000003 | ||||
| 1412.1100 | -0.05% | -0.05% | 0.000001 | ||||
| 1414.8500 | 0.19% | 0.19% | 0.000002 | ||||
| 1423.8200 | 0.63% | 0.63% | 0.000034 | ||||
| 1430.7300 | 0.49% | 0.48% | 0.000019 | ||||
| 1431.9000 | 0.08% | 0.08% | 0.000000 | ||||
| 1430.6200 | -0.09% | -0.09% | 0.000002 | ||||
| 1426.3700 | -0.30% | -0.30% | 0.000012 | ||||
| 1430.5000 | 0.29% | 0.29% | 0.000006 | ||||
| 1422.9500 | -0.53% | -0.53% | 0.000033 | ||||
| 1427.9900 | 0.35% | 0.35% | 0.000009 | ||||
| 1440.1300 | 0.85% | 0.85% | 0.000064 | ||||
| 1423.9000 | -1.13% | -1.13% | 0.000140 | ||||
| 1422.1800 | -0.12% | -0.12% | 0.000003 | ||||
| 1420.6200 | -0.11% | -0.11% | 0.000002 | ||||
| 1428.8200 | 0.58% | 0.58% | 0.000028 | ||||
| 1438.2400 | 0.66% | 0.66% | 0.000037 | ||||
| 1445.9400 | 0.54% | 0.53% | 0.000024 | ||||
| 1448.3900 | 0.17% | 0.17% | 0.000001 | ||||
| 1446.9900 | -0.10% | -0.10% | 0.000002 | ||||
| 1448.0000 | 0.07% | 0.07% | 0.000000 | ||||
| 1450.0200 | 0.14% | 0.14% | 0.000001 | ||||
| 1448.3100 | -0.12% | -0.12% | 0.000003 | ||||
| 1438.0600 | -0.71% | -0.71% | 0.000058 | ||||
| 1433.3700 | -0.33% | -0.33% | 0.000014 | ||||
| 1444.2600 | 0.76% | 0.76% | 0.000050 | ||||
| 1455.3000 | 0.76% | 0.76% | 0.000051 | ||||
| 1456.8100 | 0.10% | 0.10% | 0.000000 | ||||
| 1455.5400 | -0.09% | -0.09% | 0.000002 | ||||
| 1459.6800 | 0.28% | 0.28% | 0.000006 | ||||
| 1457.6300 | -0.14% | -0.14% | 0.000004 | ||||
| 1456.3800 | -0.09% | -0.09% | 0.000002 | ||||
| 1451.1900 | -0.36% | -0.36% | 0.000016 | ||||
| 1449.3700 | -0.13% | -0.13% | 0.000003 | ||||
| 1399.0400 | -3.47% | -3.53% | 0.001283 | ||||
| 1406.8200 | 0.56% | 0.55% | 0.000026 | ||||
| 1403.1700 | -0.26% | -0.26% | 0.000009 | ||||
| 1387.1700 | -1.14% | -1.15% | 0.000143 | ||||
| 1374.1200 | -0.94% | -0.95% | 0.000099 | ||||
| 1395.4100 | 1.55% | 1.54% | 0.000222 | ||||
| 1391.9700 | -0.25% | -0.25% | 0.000009 | ||||
| 1401.8900 | 0.71% | 0.71% | 0.000044 | ||||
| 1402.8500 | 0.07% | 0.07% | 0.000000 | ||||
| 1406.6000 | 0.27% | 0.27% | 0.000005 | ||||
| 1377.9500 | -2.04% | -2.06% | 0.000444 | ||||
| 1387.1700 | 0.67% | 0.67% | 0.000038 | ||||
| 1392.2800 | 0.37% | 0.37% | 0.000010 | ||||
| 1386.9500 | -0.38% | -0.38% | 0.000019 | ||||
| 1402.0600 | 1.09% | 1.08% | 0.000107 | ||||
| 1410.9400 | 0.63% | 0.63% | 0.000034 | ||||
| 1435.0400 | 1.71% | 1.69% | 0.000271 | ||||
| 1434.5400 | -0.03% | -0.03% | 0.000001 | ||||
| 1436.1100 | 0.11% | 0.11% | 0.000000 | ||||
| 1437.5000 | 0.10% | 0.10% | 0.000000 | ||||
| 1428.6100 | -0.62% | -0.62% | 0.000045 | ||||
| 1417.2300 | -0.80% | -0.80% | 0.000072 | ||||
| 1422.5300 | 0.37% | 0.37% | 0.000011 | ||||
| 1420.8600 | -0.12% | -0.12% | 0.000003 | ||||
| 1424.5500 | 0.26% | 0.26% | 0.000004 | ||||
| 1437.7700 | 0.93% | 0.92% | 0.000077 | ||||
| 1439.3700 | 0.11% | 0.11% | 0.000000 | ||||
| 1443.7600 | 0.30% | 0.30% | 0.000007 | ||||
| 1444.6100 | 0.06% | 0.06% | 0.000000 | ||||
| 1448.3900 | 0.26% | 0.26% | 0.000005 | ||||
| 1438.8700 | -0.66% | -0.66% | 0.000050 | ||||
| 1447.8000 | 0.62% | 0.62% | 0.000033 | ||||
| 1452.8500 | 0.35% | 0.35% | 0.000009 | ||||
| 1468.4700 | 1.08% | 1.07% | 0.000104 | ||||
| 1471.4800 | 0.20% | 0.20% | 0.000002 | ||||
| 1472.5000 | 0.07% | 0.07% | 0.000000 | ||||
| 1470.7300 | -0.12% | -0.12% | 0.000003 | ||||
| 1484.3500 | 0.93% | 0.92% | 0.000076 | ||||
| 1480.9300 | -0.23% | -0.23% | 0.000008 | ||||
| 1480.4100 | -0.04% | -0.04% | 0.000001 | ||||
| 1495.4200 | 1.01% | 1.01% | 0.000092 | ||||
| 1494.2500 | -0.08% | -0.08% | 0.000002 | ||||
| 1494.0700 | -0.01% | -0.01% | 0.000000 | ||||
| 1482.3700 | -0.78% | -0.79% | 0.000070 | ||||
| 1486.3000 | 0.27% | 0.26% | 0.000005 | ||||
| 1495.9200 | 0.65% | 0.65% | 0.000036 | ||||
| 1502.3900 | 0.43% | 0.43% | 0.000015 | ||||
| 1505.6200 | 0.21% | 0.21% | 0.000003 | ||||
| 1509.4800 | 0.26% | 0.26% | 0.000004 | ||||
| 1507.7200 | -0.12% | -0.12% | 0.000003 | ||||
| 1512.5800 | 0.32% | 0.32% | 0.000007 | ||||
| 1491.4700 | -1.40% | -1.41% | 0.000211 | ||||
| 1505.8500 | 0.96% | 0.96% | 0.000083 | ||||
| 1503.1500 | -0.18% | -0.18% | 0.000005 | ||||
| 1501.1900 | -0.13% | -0.13% | 0.000003 | ||||
| 1514.1400 | 0.86% | 0.86% | 0.000066 | ||||
| 1512.7500 | -0.09% | -0.09% | 0.000002 | ||||
| 1522.7500 | 0.66% | 0.66% | 0.000037 | ||||
| 1525.1000 | 0.15% | 0.15% | 0.000001 | ||||
| 1524.1200 | -0.06% | -0.06% | 0.000001 | ||||
| 1522.2800 | -0.12% | -0.12% | 0.000003 | ||||
| 1507.5100 | -0.97% | -0.97% | 0.000105 | ||||
| 1515.7300 | 0.55% | 0.54% | 0.000025 | ||||
| 1518.1100 | 0.16% | 0.16% | 0.000001 | ||||
| 1530.2300 | 0.80% | 0.80% | 0.000056 | ||||
| 1530.6200 | 0.03% | 0.03% | 0.000000 | ||||
| 1536.3400 | 0.37% | 0.37% | 0.000011 | ||||
| 1539.1800 | 0.18% | 0.18% | 0.000002 | ||||
| 1530.9500 | -0.53% | -0.54% | 0.000034 | ||||
| 1517.3800 | -0.89% | -0.89% | 0.000088 | ||||
| 1490.7200 | -1.76% | -1.77% | 0.000332 | ||||
| 1507.6700 | 1.14% | 1.13% | 0.000117 | ||||
| 1509.1200 | 0.10% | 0.10% | 0.000000 | ||||
| 1493.0000 | -1.07% | -1.07% | 0.000126 | ||||
| 1515.6700 | 1.52% | 1.51% | 0.000213 | ||||
| 1522.9700 | 0.48% | 0.48% | 0.000019 | ||||
| 1532.9100 | 0.65% | 0.65% | 0.000036 | ||||
| 1531.0500 | -0.12% | -0.12% | 0.000003 | ||||
| 1533.7000 | 0.17% | 0.17% | 0.000002 | ||||
| 1512.8400 | -1.36% | -1.37% | 0.000201 | ||||
| 1522.1900 | 0.62% | 0.62% | 0.000032 | ||||
| 1502.5600 | -1.29% | -1.30% | 0.000181 | ||||
| 1497.7400 | -0.32% | -0.32% | 0.000014 | ||||
| 1492.8900 | -0.32% | -0.32% | 0.000014 | ||||
| 1506.3400 | 0.90% | 0.90% | 0.000072 | ||||
| 1505.7100 | -0.04% | -0.04% | 0.000001 | ||||
| 1503.3500 | -0.16% | -0.16% | 0.000004 | ||||
| ========= | ========= | ========= | ========== | ||||
| Totals | 123 | 0.006283 | |||||
| Do not change the items below****************************** | |||||||
| daily | 1 | d | |||||
| weekly | |||||||
| monthly | |||||||
| yearly |
HISTORICAL VOLATILITY ESTIMATION 10e
Instructions
Fill in the column labeled "Asset Price". The columns containing the observation number and date are optional. Indicate the data frequency with a d, w, m, or y for daily, weekly, monthly or yearly (not case sensitive). This allows the spreadsheet to convert the periodic standard deviation into an annual standard deviation. Input cells are bordered by double lines. Output cells are bordered by single lines. To change the number of observations, add or delete rows above the double line (=====). Copy the formulas in columns, D, E and F of the last row of data onto any new rows you add. Make sure you do not erase the double line on the row between your last observation and the totals. If you do, the formulas will not automatically adjust to the number of observations you are using. Also, be sure there are no additional rows between the double line and the totals. Hit F9 to recalculate.
About
Written by Don M. Chance and Robert Brooks For use with An Introduction to Derivatives and Risk Management, 10th ed. (Mason, Ohio: South-Western Thomson, Inc., 2015) Date: 7/09 Last updated: 3/18/14 © 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
