Please use Excel and Excel formulas

PP3.5 Deferred Annuities Exp. After 5 years of development, a new material will save $750k/yr for the following 15 years, / 10%Find PW? P = A (P/A, i, N-D) (P/F, I, D) P= Show alternate Eq. gets same result P = A (P/A, 1, M) - A. (P/A, 1, P= For problem 3.5, N = 20 years (life of annual savings plus development time = total project life cycle), and D = 5 years (development time only) Generate a cash flow table for this problem in Excel for the total 20-year life cycle. Solve for present value of total life cycle two ways: first follow and duplicate both interest factor methods shown above with the interest table below, then by setting up the theoretical algebraic formula for the interest factor used to solve the problem, then solve by using present value function NPV in Excel with the cash flow table you developed. 10% Compound Interest Factors 10% Single Payment Uniform Payment Series Arithmetic Gradient Compound Amount Factor Find F Given P F/P Present Worth Factor Find P Sinking Fund Factor Find A Given F Capital Recovery Factor Find A Given P A/P Compound Amount Factor Find F Given A Present Worth Factor Find P Given A Gradient Uniform Series Find A Given G A/G Gradient Present Worth Find P Given F Given G P/F A/F F/A P/A P/G n 1 1.0000 0.909 0 1 1.000 2.100 .9091 .8264 .7513 2 0 0.476 .4762 1.736 0.826 2 1.100 1.210 1.331 1.464 1.611 3 3021 1.1000 5762 .4021 3155 2638 3.310 2.487 0.937 2.329 3 1.381 4 5 .6830 .6209 .2155 .1638 4.641 6.105 3.170 3.791 1.810 4.378 6.862 4 5 6 1.772 .5645 .1296 7.716 4.355 6 7 .1054 9.487 4.868 7 1.949 2.144 2.358 5132 .4665 8 9.684 12.763 16.029 2296 2054 .1874 .1736 .1627 .0874 11.436 2.224 2.622 3.004 3.372 3.725 5.335 8 9 .4241 13.579 5.759 19.421 9 .0736 .0627 10 2.594 3855 15.937 22.891 10 11 2.853 .3505 .1540 18.531 6.145 6.495 6.814 4.064 .0540 .0468 11 26.396 29.901 12 3.138 .3186 21.384 12 4.388 4.699 13 3.452 .2897 .0408 24.523 7.103 33.377 13 .1468 .1408 .1357 .1315 14 3.797 7.367 4.996 36.801 14 2633 .2394 .0357 .0315 15 4.177 7.606 5.279 40.152 15 16 4.595 .2176 .1278 7.824 16 27.975 31.772 35.950 40.545 45.599 51.159 17 5.054 .0278 .0247 .0219 .1978 .1247 8.022 17 18 5.560 43.416 46.582 49.640 52.583 .1799 1219 5.549 5.807 6.053 6.286 6.508 8.201 18 19 6.116 .1635 .0195 .1195 8.365 19 20 6.728 .1486 .1175 57.275 8.514 55.407 20 21 .1351 21 .0175 .0156 .0140 .0126 7.400 8.140 8.954 1156 .1140 64.003 71.403 22 58.110 60.689 .1228 6.719 6.919 7.108 8.649 8.772 8.883 8.985 22 23 .1117 .1126 79.543 63.146 23 24 9.850 .1015 .0113 .1113 88.497 7.288 65.481 24 25 10.835 .0923 .0102 .1102 98.347 9.077 7.458 67.696 25 26 .0839 .00916 .1092 7.619 69.794 26 27 11.918 13.110 14.421 .0763 .1083 27 109.182 121.100 134.210 148.631 7.770 7.914 28 .0693 .1075 71.777 73.650 75.415 28 29 15.863 .0630 .1067 9.161 9.237 9.307 9.370 9.427 9.479 9.526 8.049 29 30 17.449 .0573 .1061 164.494 8.176 77.077 .00826 .00745 .00673 .00608 .00550 .00497 .00450 .00407 30 31 19.194 .0521 .1055 181.944 8.296 31 78.640 80.108 32 21.114 .0474 .1050 201.138 8.409 32 9.569 33 34 23.225 25.548 .1045 .1041 .1037 222.252 245.477 81.486 82.777 8.515 8.615 8.709 33 34 9.609 35 28.102 .00369 271.025 9.644 83.987 35 40 45.259 .00226 .1023 442.593 9.096 88.953 40 .0431 .0391 .0356 .0221 .0137 .00852 .00529 .00328 45 .1014 92.454 45 72.891 117.391 50 .00139 .00086 .00053 .1009 9.779 9.863 9.915 9.947 9.967 94.889 718.905 1 163.9 1880.6 3034.8 50 9.374 9.570 9.708 9.802 55 189.059 -1005 55 96.562 97.701 60 304.482 .00033 .1003 60 65 490.371 .1002 9.980 9.867 98.471 65 .00204 .00127 70 789.748 .1001 9.987 9.911 98.987 70 .00020 .00013 .00008 .00005 4893.7 7887.5 12 709.0 20474.0 75 80 1 271.9 2048.4 .00079 .00049 .1001 .1000 9.992 9.995 9.941 9.961 99.332 99.561 99.712 75 80 85 3 299.0 .00030 .00003 .1000 32 979.7 9.997 9.974 85 90 .00019 .1000 53 120.3 9.983 90 5313.0 8556.7 13780.6 .00002 .00001 95 .00012 -1000 9.998 9.999 9.999 9.989 85 556.9 137 796.3 99.812 99.877 99.920 95 100 .00007 .00001 .1000 9.993 100 PP3.5 Deferred Annuities Exp. After 5 years of development, a new material will save $750k/yr for the following 15 years, / 10%Find PW? P = A (P/A, i, N-D) (P/F, I, D) P= Show alternate Eq. gets same result P = A (P/A, 1, M) - A. (P/A, 1, P= For problem 3.5, N = 20 years (life of annual savings plus development time = total project life cycle), and D = 5 years (development time only) Generate a cash flow table for this problem in Excel for the total 20-year life cycle. Solve for present value of total life cycle two ways: first follow and duplicate both interest factor methods shown above with the interest table below, then by setting up the theoretical algebraic formula for the interest factor used to solve the problem, then solve by using present value function NPV in Excel with the cash flow table you developed. 10% Compound Interest Factors 10% Single Payment Uniform Payment Series Arithmetic Gradient Compound Amount Factor Find F Given P F/P Present Worth Factor Find P Sinking Fund Factor Find A Given F Capital Recovery Factor Find A Given P A/P Compound Amount Factor Find F Given A Present Worth Factor Find P Given A Gradient Uniform Series Find A Given G A/G Gradient Present Worth Find P Given F Given G P/F A/F F/A P/A P/G n 1 1.0000 0.909 0 1 1.000 2.100 .9091 .8264 .7513 2 0 0.476 .4762 1.736 0.826 2 1.100 1.210 1.331 1.464 1.611 3 3021 1.1000 5762 .4021 3155 2638 3.310 2.487 0.937 2.329 3 1.381 4 5 .6830 .6209 .2155 .1638 4.641 6.105 3.170 3.791 1.810 4.378 6.862 4 5 6 1.772 .5645 .1296 7.716 4.355 6 7 .1054 9.487 4.868 7 1.949 2.144 2.358 5132 .4665 8 9.684 12.763 16.029 2296 2054 .1874 .1736 .1627 .0874 11.436 2.224 2.622 3.004 3.372 3.725 5.335 8 9 .4241 13.579 5.759 19.421 9 .0736 .0627 10 2.594 3855 15.937 22.891 10 11 2.853 .3505 .1540 18.531 6.145 6.495 6.814 4.064 .0540 .0468 11 26.396 29.901 12 3.138 .3186 21.384 12 4.388 4.699 13 3.452 .2897 .0408 24.523 7.103 33.377 13 .1468 .1408 .1357 .1315 14 3.797 7.367 4.996 36.801 14 2633 .2394 .0357 .0315 15 4.177 7.606 5.279 40.152 15 16 4.595 .2176 .1278 7.824 16 27.975 31.772 35.950 40.545 45.599 51.159 17 5.054 .0278 .0247 .0219 .1978 .1247 8.022 17 18 5.560 43.416 46.582 49.640 52.583 .1799 1219 5.549 5.807 6.053 6.286 6.508 8.201 18 19 6.116 .1635 .0195 .1195 8.365 19 20 6.728 .1486 .1175 57.275 8.514 55.407 20 21 .1351 21 .0175 .0156 .0140 .0126 7.400 8.140 8.954 1156 .1140 64.003 71.403 22 58.110 60.689 .1228 6.719 6.919 7.108 8.649 8.772 8.883 8.985 22 23 .1117 .1126 79.543 63.146 23 24 9.850 .1015 .0113 .1113 88.497 7.288 65.481 24 25 10.835 .0923 .0102 .1102 98.347 9.077 7.458 67.696 25 26 .0839 .00916 .1092 7.619 69.794 26 27 11.918 13.110 14.421 .0763 .1083 27 109.182 121.100 134.210 148.631 7.770 7.914 28 .0693 .1075 71.777 73.650 75.415 28 29 15.863 .0630 .1067 9.161 9.237 9.307 9.370 9.427 9.479 9.526 8.049 29 30 17.449 .0573 .1061 164.494 8.176 77.077 .00826 .00745 .00673 .00608 .00550 .00497 .00450 .00407 30 31 19.194 .0521 .1055 181.944 8.296 31 78.640 80.108 32 21.114 .0474 .1050 201.138 8.409 32 9.569 33 34 23.225 25.548 .1045 .1041 .1037 222.252 245.477 81.486 82.777 8.515 8.615 8.709 33 34 9.609 35 28.102 .00369 271.025 9.644 83.987 35 40 45.259 .00226 .1023 442.593 9.096 88.953 40 .0431 .0391 .0356 .0221 .0137 .00852 .00529 .00328 45 .1014 92.454 45 72.891 117.391 50 .00139 .00086 .00053 .1009 9.779 9.863 9.915 9.947 9.967 94.889 718.905 1 163.9 1880.6 3034.8 50 9.374 9.570 9.708 9.802 55 189.059 -1005 55 96.562 97.701 60 304.482 .00033 .1003 60 65 490.371 .1002 9.980 9.867 98.471 65 .00204 .00127 70 789.748 .1001 9.987 9.911 98.987 70 .00020 .00013 .00008 .00005 4893.7 7887.5 12 709.0 20474.0 75 80 1 271.9 2048.4 .00079 .00049 .1001 .1000 9.992 9.995 9.941 9.961 99.332 99.561 99.712 75 80 85 3 299.0 .00030 .00003 .1000 32 979.7 9.997 9.974 85 90 .00019 .1000 53 120.3 9.983 90 5313.0 8556.7 13780.6 .00002 .00001 95 .00012 -1000 9.998 9.999 9.999 9.989 85 556.9 137 796.3 99.812 99.877 99.920 95 100 .00007 .00001 .1000 9.993 100