Financial Excel Spreadsheets

123456789 10 11 12 13 14 15 16 17 18 A B C D E F G H INFLATION AND THE REAL RATE OF INTEREST Interest rate 12.00% Inflation rate 5.00% Real interest rate 6.67% ( 1 + Interest Rate ) /( 1 + Inflation Rate ) -1 EXAMPLE Cash today 100.00 Plus interest 12.00 Cash next year 112.00 Price today 106.67 Plus inflation 5.33 Price next year 112.00 Page 1123456789 10 11 12 13 14 15 16 17 18 19 A B C D E F INFLATION AND CONSTANT DOLLAR FACTORS Current price 200.00 Inflation rate 5.00% Year 0 1 2 3 Price 200.00 210.00 220.50 231.53 Inflation factor 100.00% 105.00% 110.25% 115.76% Inflation Factor = Previous Inflation Factor x ( 1 + Inflation Rate ) Price 200.00 210.00 220.50 231.53 Constant dollar factor 100.00% 95.24% 90.70% 86.38% Price in constant dollars 200.00 200.00 200.00 200.00 Constant dollar factor = 1 /Inflation Factor Constant dollar factor = Previous Constant Dollar Factor /( 1 + Inflation Rate ) Page 2123456789 10 11 12 13 14 15 16 17 18 19 20 21 A B C D E F G H NPV CAN BE CALCULATED IN NOMINAL OR REAL TERMS Interest rate 12.00% Inflation rate 5.00% Real interest rate 6.67% Year 0 1 2 3 Cash flow (400,000) 100,000 200,000 300,000 PV factor 100% 89% 80% 71% PV of cash flow (400,000) 89,286 159,439 213,534 Cumulative PV (400,000) (310,714) (151,276) 62,259 NPV 62,259 Cash flow (400,000) 100,000 200,000 300,000 Constant dollar factor 100% 95% 91% 86% Real cash flow (400,000) 95,238 181,406 259,151 Cash Flow x Const $ Factor Real PV factor 100% 94% 88% 82% Real PV (400,000) 89,286 159,439 213,534 Real CF x Real PV Factor Cumulative real PV (400,000) (310,714) (151,276) 62,259 NPV 62,259 Page 3