I am working on a group project for my International Finance class. The object is to predict future spot rates using different methods (RPPP, IFE, FUPFS). My part of the project is to determine "real realized appreciation." I cannot understand how to calculate this.

Thank you for your help

Analysis of Dollar v Pound Sterling Exchange Rates Data Quarterly data on spot and forward direct quote for pound, CPI 1 and 12 month LIBOR 2 for US and UK have been collected from Bloomberg from the 4 th quarter of 1991 (91.4) through the 4th quarter of 2015 (14.4). From these you can calculate twenty four years of annual rates and annualized quarterly rates. Methods for Calculating Annual Rates of Change a) Annual rate of change from the 4th quarter to the 4th quarter of subsequent year. For example, annual rate of change in 1998 will be: 98.4/97.4 - 1. b) Arithmetic mean for annualized subsequent quarter rates of change. For example, in 1998 it will be: [(98.4/98.3 -1)*4 + (98.3/98.2 -1)*4 + (98.2/98.1 -1)*4 + (98.1/97.4 -1)*4] / 4. c) Geometric mean for annualized subsequent quarter rates of change. For example, in 1998 it will be: {[1+(98.4/98.3-1)*4]*[1+(98.3/98.2-1)*4]*[1+(98.2/98.1-1)*4]*[1+(98.1/97.4-1)*4]} 1/4 - 1. d) Arithmetic mean for the same quarters of subsequent year annual change. For example, in 1998 it will be: [(98.4/97.4 -1) + (98.3/97.3 -1) + (98.2/97.2 -1) + (98.1/97.1 -1)] / 4. e) Geometric mean for the same quarters of subsequent year annual change. For example, in 1998 it will be: {[1+(98.4/97.4-1)]*[1+(98.3/97.3-1)]*[1+(98.2/97.2-1)]*[1+(98.1/97.1-1)]} 1/4 - 1. Assignments 1. XLS file due 11/2. Calculate annual realized nominal appreciations of and $ (a-e for each currency). 2. XLS file due 11/2. Calculate annualized forward premiums for and $ using 3, 6 and 12 month forward rates observed in the same quarter (horizontally). forward premiums in 1998 calculated using different methods: a) f360 = F36098.4/S98.4 - 1, f180$ = (F18098.4/S98.4 - 1)*2 and f90$ = (F9098.4/S98.4 - 1)*4. b) [(F36098.4/S98.4 - 1) + (F36098.3/S98.3 - 1) + (F36098.2/S98.2 - 1) + (F36098.1/S98.1 - 1)]/4, [(F18098.4/S98.4 - 1)*2 + (F18098.3/S98.3 - 1)*2 + (F18098.2/S98.2 - 1)*2 + (F18098.1/S98.1 - 1)*2]/4, [(F9098.4/S98.4 - 1)*4 + (F9098.3/S98.3 - 1)*4 + (F9098.2/S98.2 - 1)*4 + (F9098.1/S98.1 - 1)*4]/4. {[1+(F36098.4/S98.4 - 1)] *[1+(F36098.3/S98.3 - 1)] *[1+(F36098.2/S98.2 - 1)] *[1+(F36098.1/S98.1 - 1)]}1/4 - 1, {[1+(F18098.4/S98.4-1)*2] *[1+(F18098.3/S98.3-1)*2] *[1+(F18098.2/S98.2-1)*2] *[1+(F18098.1/S98.1-1)*2]}1/4 - 1 {[1+(F9098.4/S98.4-1)*4] *[1+(F9098.3/S98.3-1)*4] *[1+(F9098.2/S98.2-1)*4] *[1+(F9098.1/S98.1-1)*4]}1/4 - 1. Methods d and e cannot be used for forward premiums. c) 3. XLS file due 11/2. Calculate annual inflation rates of and $ using their respective CPIs (a-e for each currency). Methods c and e cannot be calculated if rate of change in any period is less than -100%. 4. XLS file due 11/2. Calculate annual realized real appreciations of and $. End of period real exchange rate in the 4th quarter of 1998 for methods a, d & e is S98.4' = S98.4[1+(CPI98.4/CPI97.4 - 1)]/[1+(CPI98.4$/CPI97.4$ - 1)]. Real appreciation in 1998 for methods a, d & e is s$' = S98.4' / S97.4 - 1. End of period real exchange rate in the 4th quarter of 1998 for methods b & c is S98.4' = S98.4[1+(CPI98.4/CPI98.3 - 1)]/[1+(CPI98.4$/CPI98.3$ - 1)]. Real appreciation in the 4th quarter of 1998 for methods b & c is s' = (S98.4' / S98.3 - 1)*4. 5. XLS file due 11/2. Test if 3, 6 and 12 month forward rate is an unbiased predictor of the future spot rate using regression (estimated S = f(F) = 0 + 1F should have Y intercept 0 = 0 and slope coefficient 1 = 1), CPI measures prices of a market basket of consumer goods and services. Its growth rates represent the inflation. London - Interbank Offered Rate (measured in percentages) is recorded each day at 11am & released at 11.45am (London time) as an average of the middle two quartiles of quotations from the banks determined by the ICE (Intercontinental Exchange) Benchmark Administration. 1 2 residuals (sum of differences between S and F should be zero) and graphical analysis (plot S and F on the same graph). Compare staggered data: F9098.1 v S98.2, F18098.1 v S98.3, F36098.1 v S99.1. Use measure analogous to sample standard deviation calculated on the differences between current forward and future spot exchange rate [(S-F)2]/(N-1) to find which forward rate is the best unbiased predictor of future spot. 6. XLS file due 11/2. Calculate relative purchasing power parity implied annual appreciation of and $. Pound appreciation in 1988 for methods a, d and e would be [1+(CPI98.4$/CPI97.4$ - 1)] / [1+(CPI98.4/CPI97.4 - 1)] - 1. Pound appreciation in the fourth quarter of 1998 for methods b and c would be calculated as [1+(CPI98.4$/CPI98.3$ - 1)*4] / [1+(CPI98.4/CPI98.3 - 1)*4] - 1. 7. XLS file due 11/2. Calculate international Fisher's effect implied annual appreciation of and $. Pound appreciation in 1998 for methods a, b and c would be (1 + I98.4$) / (1 + I98.4) - 1, {[(1+I98.4$)/(1+I98.4)-1] + [(1+I98.3$)/(1+I98.3)-1] + [(1+I98.2$)/(1+I98.2)-1] + [(1+I98.1$)/(1+I98.1)-1]} / 4 and {[(1+I98.4$)/(1+I98.4)]*[(1+I98.3$)/(1+I98.3)]*[(1+I98.2$)/(1+I98.2)]*[(1+I98.1$)/(1+I98.1)]}1/4 - 1. International Fisher's effect cannot be calculated for methods d and e. 8. XLS file due 11/2. Test if covered interest arbitrage holds for 12, 6 and 3 month forward rates and 12 month LIBORs for each quarter. Test for the 1 st quarter of 1998 would be 1 + I 98.1 = [S98.1(1 + I98.1$)] / F36098.1, 1 + I98.1/2 = [S98.1(1 + I98.1$/2)] / F18098.1 and 1 + I98.1/4 = [S98.1(1 + I98.1$/4)] / F9098.1. Use measure analogous to the sample standard deviation calculated on the differences between left (LHS) and right hand side (RHS) of the test [(LHS-RHS)2]/(N-1) to find which forward rate leads to the smallest violation of CIA. 9. XLS and PPT files due 12/5.Using available data through the fourth quarter of the previous year to estimate expected future spot nominal exchange rates for each quarter of the following 3 years starting at the first quarter of 1996. You can use all or subset of available data (it might differ as volatility of exchange rates in the estimation period changes). Repeat the exercise for each year through the first quarter of 2012. a) Use realized nominal and realized real appreciation. b) Use expected appreciations implied by 3, 6 and 12 month forward rates, international Fisher's effect and relative purchasing power parity. c) Use expected forward premium based on 3, 6 and 12 month forward rates implied by financial or money market hedge. d) Use regression estimates of y intercept and slope coefficients for historical exchange rates to project future spot rates. e) Team with the lowest standard deviation between estimated (use the same method to calculate appreciation but estimation period can vary) and realized exchange rates averaged over all 17 three year testing periods will earn extra 5%. 10. What are the three methods (used to calculate appreciation) with the most predictive power? Does the ranking of methods stay the same in every testing period? Does estimation period change over time? Discuss theoretical differences between arithmetic and geometric means, nominal and real realized appreciations, as well as appreciations calculated in different ways (e.g. realized and RPPP are backward looking while the others are forward looking since they are based on expectations about the future). 11. In addition to e-mailing me the spreadsheet with assignments on indicated dates, each team has to e-mail me their presentation (powerpoint file) and excel file on which it was based before the first presentation class. Each team also has to submit the print out of their presentation. Date S$/ F$/90 F$/180 F$/360 CPI$ 1983=100 CPI 2014=100 % LIBOR$360 % LIBOR360 12/31/2015 9/30/2015 6/30/2015 3/31/2015 12/31/2014 9/30/2014 6/30/2014 3/31/2014 12/31/2013 9/30/2013 6/28/2013 3/29/2013 12/31/2012 9/28/2012 6/29/2012 3/30/2012 12/30/2011 9/30/2011 6/30/2011 3/31/2011 12/31/2010 9/30/2010 6/30/2010 3/31/2010 12/31/2009 9/30/2009 6/30/2009 3/31/2009 12/31/2008 9/30/2008 6/30/2008 3/31/2008 12/31/2007 9/28/2007 6/29/2007 3/30/2007 12/29/2006 9/29/2006 6/30/2006 3/31/2006 12/30/2005 9/30/2005 1.4736 1.5128 1.5712 1.4818 1.5577 1.6213 1.7106 1.6662 1.6557 1.6186 1.5213 1.5198 1.6255 1.6167 1.5707 1.6008 1.5543 1.5584 1.6053 1.6028 1.5612 1.5716 1.4945 1.5184 1.6170 1.5982 1.6458 1.4323 1.4593 1.7805 1.9923 1.9837 1.9850 2.0473 2.0087 1.9678 1.9588 1.8721 1.8483 1.7372 1.7230 1.7643 1.473824 1.512345 1.570132 1.480881 1.556536 1.619993 1.709302 1.665061 1.654627 1.617535 1.520385 1.519103 1.625020 1.616300 1.570319 1.599821 1.552940 1.557044 1.603500 1.600730 1.560092 1.570595 1.494435 1.517575 1.616065 1.597822 1.645535 1.432811 1.456971 1.785499 1.978459 1.969460 1.980128 2.043160 2.005988 1.966931 1.959395 1.873865 1.852200 1.739215 1.722975 1.762328 1.474465 1.511745 1.569300 1.480160 1.555421 1.618245 1.707479 1.663866 1.653385 1.616532 1.519600 1.518610 1.624480 1.615793 1.569830 1.598847 1.551435 1.555890 1.601690 1.598003 1.558461 1.569532 1.494332 1.516806 1.615270 1.597664 1.645150 1.433800 1.456875 1.778715 1.964614 1.954568 1.974100 2.037504 2.002155 1.964731 1.958980 1.874540 1.856170 1.742030 1.724700 1.762251 1.476359 1.511175 1.568197 1.480249 1.554732 1.614344 1.701640 1.660590 1.650085 1.614550 1.518265 1.518401 1.623450 1.614775 1.568992 1.596605 1.548629 1.553705 1.597407 1.591405 1.554547 1.567250 1.494175 1.515908 1.613524 1.597204 1.644010 1.435725 1.457406 1.763382 1.939065 1.928599 1.959667 2.026584 1.992487 1.957310 1.956063 1.873800 1.861865 1.747075 1.728895 1.764650 238.041 237.489 237.423 235.859 236.464 237.510 237.016 235.897 234.847 233.642 232.357 232.313 231.272 230.987 228.506 228.894 227.223 226.597 224.806 223.046 220.472 218.275 217.199 217.353 217.347 215.861 214.790 212.495 211.398 218.877 217.463 213.448 211.445 208.547 207.234 205.288 203.100 202.800 201.800 199.700 198.100 198.800 100.3 100.1 100.0 99.5 100.2 100.1 100.0 99.4 99.3 98.7 98.4 97.7 97.3 96.1 95.8 95.1 94.7 93.8 93.3 91.8 90.5 89.6 89.3 88.2 87.6 86.9 86.4 85.4 85.8 85.7 84.6 82.9 82.6 81.7 81.8 81.0 80.9 80.3 79.8 78.7 78.8 78.4 1.17800 0.85110 0.77150 0.69420 0.62880 0.57860 0.54510 0.55810 0.58310 0.62940 0.68565 0.73150 0.84350 0.97300 1.06800 1.04850 1.12805 0.86489 0.73350 0.78250 0.78094 0.77775 1.17313 0.92000 0.98438 1.26375 1.60625 1.97188 2.00375 3.96250 3.31063 2.48625 4.22375 4.90125 5.42563 5.22000 5.32938 5.29750 5.69313 5.28750 4.83875 4.44000 1.07025 1.04150 1.04063 0.96588 0.97556 1.06150 1.04200 0.91125 0.91125 0.87469 0.89750 0.90938 1.01375 1.29563 1.68025 1.86306 1.87069 1.71625 1.57875 1.59875 1.50938 1.47078 1.45813 1.31813 1.24750 1.10250 1.69875 2.07063 3.07375 6.51875 6.44875 5.84375 5.74375 6.19375 6.32375 5.86125 5.58188 5.27438 5.00563 4.77938 4.56875 4.53750 6/30/2005 3/31/2005 12/31/2004 9/30/2004 6/30/2004 3/31/2004 12/31/2003 9/30/2003 6/30/2003 3/31/2003 12/31/2002 9/30/2002 6/28/2002 3/29/2002 12/31/2001 9/28/2001 6/29/2001 3/30/2001 12/29/2000 9/29/2000 6/30/2000 3/31/2000 12/31/1999 9/30/1999 6/30/1999 3/31/1999 12/31/1998 9/30/1998 6/30/1998 3/31/1998 12/31/1997 9/30/1997 6/30/1997 3/31/1997 12/31/1996 9/30/1996 6/28/1996 3/29/1996 12/29/1995 9/29/1995 6/30/1995 3/31/1995 12/30/1994 1.7915 1.8905 1.9182 1.8120 1.8204 1.8462 1.7858 1.6618 1.6546 1.5827 1.6100 1.5684 1.5335 1.4259 1.4546 1.4744 1.4153 1.4161 1.4930 1.4754 1.5141 1.5911 1.6182 1.6473 1.5778 1.6112 1.6600 1.6989 1.6679 1.6725 1.6452 1.6156 1.6656 1.6374 1.7140 1.5653 1.5527 1.5270 1.5496 1.5848 1.5955 1.6215 1.5648 1.786575 1.881995 1.907710 1.799265 1.805735 1.831545 1.773150 1.651220 1.644390 1.573600 1.599880 1.560095 1.524995 1.418350 1.447000 1.467550 1.410356 1.414165 1.495100 1.478000 1.516658 1.591598 1.618350 1.648014 1.578900 1.610191 1.655800 1.690700 1.659405 1.665050 1.638020 1.609585 1.661305 1.635225 1.710723 1.564275 1.551869 1.524925 1.546350 1.581689 1.592250 1.620110 1.564431 1.784228 1.875870 1.899280 1.787860 1.792450 1.815950 1.760050 1.640350 1.635100 1.564700 1.590050 1.551600 1.515950 1.411850 1.439700 1.460950 1.405350 1.412100 1.496025 1.479800 1.519788 1.592713 1.617400 1.647800 1.580900 1.610850 1.654150 1.683150 1.650700 1.658000 1.631125 1.603650 1.656350 1.632875 1.705550 1.563850 1.552150 1.522750 1.543050 1.579250 1.586950 1.617800 1.564150 1.782740 1.868325 1.886637 1.767875 1.771387 1.786200 1.735350 1.617930 1.617850 1.548450 1.571450 1.535150 1.498900 1.401750 1.426550 1.449150 1.396050 1.409050 1.497000 1.482825 1.525575 1.595550 1.616200 1.644300 1.584050 1.613750 1.653400 1.669750 1.635150 1.646950 1.620209 1.593210 1.648490 1.628400 1.695214 1.562674 1.553941 1.517897 1.536838 1.573031 1.573269 1.610071 1.562737 193.700 193.100 191.700 189.800 188.900 187.100 185.500 185.100 183.100 183.900 181.800 180.800 179.600 178.500 177.400 178.100 177.700 176.100 174.600 173.600 172.200 171.000 168.800 167.800 166.000 164.800 164.400 163.500 162.800 162.000 161.800 161.200 160.200 159.800 159.100 157.700 156.700 155.500 153.900 153.100 152.400 151.200 150.100 78.0 77.3 77.1 76.6 76.5 75.9 76.0 75.6 75.5 75.0 75.0 74.6 74.5 73.9 73.9 73.8 73.8 72.8 73.2 72.7 72.7 72.1 72.5 72.1 72.3 71.6 71.6 71.2 71.3 70.5 70.6 70.3 70.0 69.4 69.4 68.9 68.9 68.1 67.7 67.4 67.3 66.3 65.8 3.88000 3.84500 3.10000 2.48250 2.46250 1.35125 1.45688 1.30000 1.19000 1.28000 1.44938 1.72500 2.28625 3.00250 2.44250 2.64250 4.18375 4.66750 6.00000 6.80125 7.18000 6.94375 6.50000 6.03500 5.84000 5.25000 5.09844 5.06250 5.84375 5.89063 5.96875 6.00000 6.12500 6.27344 5.78906 5.98828 6.12500 5.72266 5.42578 6.00000 5.98438 6.81250 7.75000 4.53625 5.12188 4.93125 5.10375 5.28125 4.75500 4.43375 4.03391 3.55875 3.56875 3.99375 3.95109 4.67438 4.85422 4.51125 4.51125 5.69984 5.28047 5.80938 6.37875 6.54047 6.80750 6.70813 6.35531 5.50000 5.25000 5.62813 6.93750 8.00000 7.61328 7.68750 7.57422 7.31250 7.00000 7.04688 6.25000 6.12500 6.43750 6.37500 6.87500 7.56250 7.66406 8.00000 9/30/1994 6/30/1994 3/31/1994 12/31/1993 9/30/1993 6/30/1993 3/31/1993 12/31/1992 9/30/1992 6/30/1992 3/31/1992 12/31/1991 1.5775 1.5443 1.4840 1.4775 1.4960 1.4912 1.5145 1.5105 1.7773 1.9050 1.7363 1.8707 1.576000 1.543359 1.478901 1.470254 1.486193 1.481400 1.504300 1.497200 1.751100 1.876100 1.708894 1.840614 1.572450 1.543100 1.475500 1.465200 1.478127 1.473125 1.495754 1.486433 1.729925 1.849531 1.682690 1.811533 1.561361 1.539114 1.470024 1.454609 1.464889 1.460121 1.482791 1.474134 1.691675 1.811658 1.643598 1.759450 149.300 147.900 147.100 146.300 145.000 144.300 143.300 142.300 141.100 140.100 139.100 138.200 65.6 65.7 64.7 64.6 64.5 64.4 63.2 63.1 62.7 62.8 61.7 61.5 6.31250 5.81250 4.75000 3.81250 3.56250 3.75000 3.62500 4.06250 3.31250 4.31250 5.06250 4.31250 7.50000 6.25000 5.81250 5.50000 5.81250 6.00000 5.87500 6.68750 8.56250 9.75000 11.06250 10.93750