70.2K

Verified Solution

Link Copied!

---- ALL ANSWERS MUST BE WRITTEN IN EXCEL FORMULAS.

A1 face TABLE 2 B D E F G H I J K L M N O P 0 1 TABLE 2 Present Value of $1 2 3 periods 1.00% 1.50% 2.00% 2.50% 3.00% 3.50% 4.00% 4.50% 5.00% 5.50% 6.00% 7.00% 8.00% 9.00% 10.00 4 1 0.99010 0.98522 0.98039 0.97561 0.97087 0.96618 0.96154 0.95694 0.95238 0.94787 0.94340 0.93458 0.92593 0.91743 0.909 5 2 0.98030 0.97066 0.96117 0.95181 0.94260 0.93351 0.92456 0.91573 0.90703 0.89845 0.89000 0.87344 0.85734 0.84168 0.826 6 3 0.97059 0.95632 0.94232 0.92860 0.91514 0.90194 0.88900 0.87630 0.86384 0.85161 0.83962 0.81630 0.79383 0.77218 0.751 7 4 0.96098 0.94218 0.92385 0.90595 0.88849 0.87144 0.85480 0.83856 0.82270 0.80722 0.79209 0.76290 0.73503 0.70843 0.683 8 5 0.95147 0.92826 0.90573 0.88385 0.86261 0.84197 0.82193 0.80245 0.78353 0.76513 0.74726 0.71299 0.68058 0.64993 0.620 9 6 0.94205 0.91454 0.88797 0.86230 0.83748 0.81350 0.79031 0.76790 0.74622 0.72525 0.70496 0.66634 0.63017 0.59627 0.564 10 7 0.93272 0.90103 0.87056 0.84127 0.81309 0.78599 0.75992 0.73483 0.71068 0.68744 0.66506 0.62275 0.58349 0.54703 0.513 11 8 0.92348 0.88771 0.85349 0.82075 0.78941 0.75941 0.73069 0.70319 0.67684 0.65160 0.62741 0.58201 0.54027 0.50187 0.466 12 9 0.91434 0.87459 0.83676 0.80073 0.76642 0.73373 0.70259 0.67290 0.64461 0.61763 0.59190 0.54393 0.50025 0.46043 0.424 13 10 0.90529 0.86167 0.82035 0.78120 0.74409 0.70892 0.67556 0.64393 0.61391 0.58543 0.55839 0.50835 0.46319 0.42241 0.385 14 11 0.89632 0.84893 0.80426 0.76214 0.72242 0.68495 0.64958 0.61620 0.58468 0.55491 0.52679 0.47509 0.42888 0.38753 0.350 15 12 0.88745 0.83639 0.78849 0.74356 0.70138 0.66178 0.62460 0.58966 0.55684 0.52598 0.49697 0.44401 0.39711 0.35553 0.318 16 13 0.87866 0.82403 0.77303 0.72542 0.68095 0.63940 0.60057 0.56427 0.53032 0.49856 0.46884 0.41496 0.36770 0.32618 0.289 17 14 0.86996 0.81185 0.75788 0.70773 0.66112 0.61778 0.57748 0.53997 0.50507 0.47257 0.44230 0.38782 0.34046 0.29925 0.263 18 15 0.86135 0.79985 0.74301 0.69047 0.64186 0.59689 0.55526 0.51672 0.48102 0.44793 0.41727 0.36245 0.31524 0.27454 0.239 19 16 0.85282 0.78803 0.72845 0.67362 0.62317 0.57671 0.53391 0.49447 0.45811 0.42458 0.39365 0.33873 0.29189 0.25187 0.217 20 17 0.84438 0.77639 0.71416 0.65720 0.60502 0.55720 0.51337 0.47318 0.43630 0.40245 0.37136 0.31657 0.27027 0.23107 0.197 21 18 0.83602 0.76491 0.70016 0.64117 0.58739 0.53836 0.49363 0.45280 0.41552 0.38147 0.35034 0.29586 0.25025 0.21199 0.179 22 19 0.82774 0.75361 0.68643 0.62553 0.57029 0.52016 0.47464 0.43330 0.39573 0.36158 0.33051 0.27651 0.23171 0.19449 0.163 23 20 0.81954 0.74247 0.67297 0.61027 0.55368 0.50257 0.45639 0.41464 0.37689 0.34273 0.31180 0.25842 0.21455 0.17843 0.148 24 21 0.81143 0.73150 0.65978 0.59539 0.53755 0.48557 0.43883 0.39679 0.35894 0.32486 0.29416 0.24151 0.19866 0.16370 0.135 25 24 0.78757 0.69954 0.62172 0.55288 0.49193 0.43796 0.39012 0.34770 0.31007 0.27666 0.24698 0.19715 0.15770 0.12640 0.101 26 25 0.77977 0.68921 0.60953 0.53939 0.47761 0.42315 0.37512 0.33273 0.29530 0.26223 0.23300 0.18425 0.14602 0.11597 0.092 28 0.75684 0.65910 0.57437 0.50088 0.43708 0.38165 0.33348 0.29157 0.255090.22332 0.19563 0.15040 0.11591 0.08955 0.069 28 29 0.74934 0.64936 0.56311 0.48866 0.42435 0.36875 0.32065 0.27902 0.24295 0.21168 0.18456 0.14056 0.10733 0.08215 0.063 29 30 0.74192 0.63976 0.55207 0.47674 0.41199 0.35628 0.30832 0.26700 0.23138 0.20064 0.17411 0.13137 0.09938 0.07537 0.057 30 31 0.73458 0.63031 0.54125 0.46511 0.39999 0.34423 0.29646 0.25550 0.22036 0.19018 0.16425 0.12277 0.09202 0.06915 0.052 31 40 0.67165 0.55126 0.45289 0.37243 0.30656 0.25257 0.20829 0.17193 0.14205 0.11746 0.09722 0.06678 0.04603 0.03184 0.022 A1 TABLE 2 V U V H I J K L M N O P Q R S 1 2 3 3.50% 4.00% 4.50% 5.00% 5.50% 6.00% 7.00% 8.00% 9.00% 10.00% 11.00% 12.00% 20.00% 4 0.96618 0.96154 0.95694 0.95238 0.94787 0.94340 0.93458 0.92593 0.91743 0.90909 0.90090 0.89286 0.83333 5 0.93351 0.92456 0.91573 0.90703 0.89845 0.89000 0.87344 0.85734 0.84168 0.82645 0.81162 0.79719 0.69444 6 0.90194 0.88900 0.87630 0.86384 0.85161 0.83962 0.81630 0.79383 0.77218 0.75131 0.73119 0.71178 0.57870 7 0.87144 0.85480 0.83856 0.82270 0.80722 0.79209 0.76290 0.73503 0.70843 0.68301 0.65873 0.63552 0.48225 8 0.84197 0.82193 0.80245 0.78353 0.76513 0.74726 0.71299 0.68058 0.64993 0.62092 0.59345 0.56743 0.40188 9 0.81350 0.79031 0.76790 0.74622 0.72525 0.70496 0.66634 0.63017 0.59627 0.56447 0.53464 0.50663 0.33490 10 0.78599 0.75992 0.73483 0.71068 0.68744 0.66506 0.62275 0.58349 0.54703 0.51316 0.48166 0.45235 0.27908 11 0.75941 0.73069 0.70319 0.67684 0.65160 0.62741 0.58201 0.54027 0.50187 0.46651 0.43393 0.40388 0.23257 12 0.73373 0.70259 0.67290 0.64461 0.61763 0.59190 0.54393 0.50025 0.46043 0.42410 0.39092 0.36061 0.19381 13 0.70892 0.67556 0.64393 0.61391 0.58543 0.55839 0.50835 0.46319 0.42241 0.38554 0.35218 0.32197 0.16151 14 0.68495 0.64958 0.61620 0.58468 0.55491 0.52679 0.47509 0.42888 0.38753 0.35049 0.31728 0.28748 0.13459 15 0.66178 0.62460 0.58966 0.55684 0.52598 0.49697 0.44401 0.39711 0.35553 0.31863 0.28584 0.25668 0.11216 16 0.63940 0.60057 0.56427 0.53032 0.49856 0.46884 0.41496 0.36770 0.32618 0.28966 0.25751 0.22917 0.09346 17 0.61778 0.57748 0.53997 0.50507 0.47257 0.44230 0.38782 0.34046 0.29925 0.26333 0.23199 0.20462 0.07789 18 0.59689 0.55526 0.51672 0.48102 0.44793 0.41727 0.36245 0.31524 0.27454 0.23939 0.20900 0.18270 0.06491 19 0.57671 0.53391 0.49447 0.45811 0.42458 0.39365 0.33873 0.29189 0.25187 0.21763 0.18829 0.16312 0.05409 20 0.55720 0.51337 0.47318 0.43630 0.40245 0.37136 0.31657 0.27027 0.23107 0.19784 0.16963 0.14564 0.04507 21 0.53836 0.49363 0.45280 0.41552 0.38147 0.35034 0.29586 0.25025 0.21199 0.17986 0.15282 0.13004 0.03756 22 0.52016 0.47464 0.43330 0.39573 0.36158 0.33051 0.27651 0.23171 0.19449 0.16351 0.13768 0.11611 0.03130 23 0.50257 0.45639 0.41464 0.37689 0.34273 0.31180 0.25842 0.21455 0.17843 0.14864 0.12403 0.10367 0.02608 24 0.48557 0.43883 0.39679 0.35894 0.32486 0.29416 0.24151 0.19866 0.16370 0.13513 0.11174 0.09256 0.02174 25 0.43796 0.39012 0.34770 0.31007 0.27666 0.24698 0.19715 0.15770 0.12640 0.10153 0.08170 0.06588 0.01258 26 0.42315 0.37512 0.33273 0.29530 0.26223 0.23300 0.18425 0.14602 0.11597 0.09230 0.07361 0.05882 0.01048 27 0.38165 0.33348 0.29157 0.25509 0.22332 0.19563 0.15040 0.11591 0.08955 0.06934 0.05382 0.04187 0.00607 28 0.36875 0.32065 0.27902 0.24295 0.21168 0.18456 0.14056 0.10733 0.08215 0.06304 0.04849 0.03738 0.00506 29 0.35628 0.30832 0.26700 0.23138 0.20064 0.17411 0.13137 0.09938 0.07537 0.05731 0.04368 0.03338 0.00421 30 0.34423 0.29646 0.25550 0.22036 0.19018 0.16425 0.12277 0.09202 0.06915 0.05210 0.03935 0.02980 0.00351 31 0.25257 0.20829 0.17193 0.14205 0.11746 0.09722 0.06678 0.04603 0.03184 0.02209 0.01538 0.01075 0.00068 32 A1 TABLE 3 K L M N O 9 4 A B D E F G H I J 1 TABLE 3 Future Value of an Ordinary Annuity of $1 2 3 Periods 1.00% 1.50% 2.00% 2.50% 3.00% 3.50% 4.00% 4.50% 4 1 1.0000 1.0000 1.0000 1.0000 1.0000 1.0000 1.0000 1.0000 5 2 2.0100 2.0150 2.0200 2.0250 2.0300 2.0350 2.0400 2.0450 6 3 3.0301 3.0452 3.0604 3.0756 3.0909 3.1062 3.1216 3.1370 7 4 4.0604 4.0909 4.1216 4.1525 4.1836 4.2149 4.2465 4.2782 8 5 5.1010 5.1523 5.2040 5.2563 5.3091 5.3625 5.4163 5.4707 9 6 6.1520 6.2296 6.3081 6.3877 6.4684 6.5502 6.6330 6.7169 10 7 7.2135 7.3230 7.4343 7.5474 7.6625 7.7794 7.8983 8.0192 11 8 8.2857 8.4328 8.5830 8.7361 8.8923 9.0517 9.2142 9.3800 12 9 9.3685 9.5593 9.7546 9.9545 10.1591 10.3685 10.5828 10.8021 13 10 10.4622 10.7027 10.9497 11.2034 11.4639 11.7314 12.0061 12.2882 14 11 11.5668 11.8633 12.1687 12.4835 12.8078 13.1420 13.4864 13.8412 15 12 12.6825 13.0412 13.4121 13.7956 14.1920 14.6020 15.0258 15.4640 16 13 13.8093 14.2368 14.6803 15.1404 15.6178 16.1130 16.6268 17.1599 17 14 14.9474 15.4504 15.9739 16.5190 17.0863 17.6770 18.2919 18.9321 18 15 16.0969 16.6821 17.2934 17.9319 18.5989 19.2957 20.0236 20.7841 19 16 17.2579 17.9324 18.6393 19.3802 20.1569 20.9710 21.8245 22.7193 20 17 18.4304 19.2014 20.0121 20.8647 21.7616 22.7050 23.6975 24.7417 21 18 19.6147 20.4894 21.4123 22.3863 23.4144 24.4997 25.6454 26.8551 22 19 20.8109 21.7967 22.8406 23.9460 25.1169 26.3572 27.6712 29.0636 23 20 22.0190 23.1237 24.2974 25.5447 26.8704 28.2797 29.7781 31.3714 24 21 23.2392 24.4705 25.7833 27.1833 28.6765 30.2695 31.9692 33.7831 25 30 34.7849 37.5387 40.5681 43.9027 47.5754 51.6227 56.084961.0071 26 40 48.8864 54.2679 60.4020 67.4026 75.4013 84.5503 95.0255 107.0303 27 28 5.00% 1.0000 2.0500 3.1525 4.3101 5.5256 6.8019 8.1420 9.5491 11.0266 12.5779 14.2068 15.9171 17.7130 19.5986 21.5786 23.6575 25.8404 28.1324 30.5390 33.0660 35.7193 66.4388 120.7998 5.50% 1.0000 2.0550 3.1680 4.3423 5.5811 6.8881 8.2669 9.7216 11.2563 12.8754 14.5835 16.3856 18.2868 20.2926 22.4087 24.6411 26.9964 29.4812 32.1027 34.8683 37.7861 72.4355 136.6056 6.00% 1.0000 2.0600 3.1836 4.3746 5.6371 6.9753 8.3938 9.8975 11.4913 13.1808 14.9716 16.8699 18.8821 21.0151 23.2760 25.6725 28.2129 30.9057 33.7600 36.7856 39.9927 79.0582 154.7620 7.00% 1.0000 2.0700 3.2149 4.4399 5.7507 7.1533 8.6540 10.2598 11.9780 13.8164 15.7836 17.8885 20.1406 22.5505 25.1290 27.8881 30.8402 33.9990 37.3790 40.9955 44.8652 94.4608 199.6351 8.00% 1.0000 2.0800 3.2464 4.5061 5.8666 7.3359 8.9228 10.6366 12.4876 14.4866 16.6455 18.9771 21.4953 24.2149 27.1521 30.3243 33.7502 37.4502 41.4463 45.7620 50.4229 113.2832 259.0565 NNNNPP 4 un A1 X TABLE 3 J K L M N O P Q R S T U 1 2 3 4.50% 4 1.0000 5 2.0450 6 3.1370 7 4.2782 8 5.4707 9 6.7169 10 8.0192 11 9.3800 12 10.8021 13 12.2882 14 13.8412 15 15.4640 16 17.1599 17 18.9321 18 20.7841 19 22.7193 20 24.7417 21 26.8551 22 29.0636 23 31.3714 24 33.7831 25 61.0071 26 107.0303 5.00% 1.0000 2.0500 3.1525 4.3101 5.5256 6.8019 8.1420 9.5491 11.0266 12.5779 14.2068 15.9171 17.7130 19.5986 21.5786 23.6575 25.8404 28.1324 30.5390 33.0660 35.7193 66.4388 120.7998 5.50% 1.0000 2.0550 3.1680 4.3423 5.5811 6.8881 8.2669 9.7216 11.2563 12.8754 14.5835 16.3856 18.2868 20.2926 22.4087 24.6411 26.9964 29.4812 32.1027 34.8683 37.7861 72.4355 136.6056 6.00% 1.0000 2.0600 3.1836 4.3746 5.6371 6.9753 8.3938 9.8975 11.4913 13.1808 14.9716 16.8699 18.8821 21.0151 23.2760 25.6725 28.2129 30.9057 33.7600 36.7856 39.9927 79.0582 154.7620 7.00% 1.0000 2.0700 3.2149 4.4399 5.7507 7.1533 8.6540 10.2598 11.9780 13.8164 15.7836 17.8885 20.1406 22.5505 25.1290 27.8881 30.8402 33.9990 37.3790 40.9955 44.8652 94.4608 199.6351 8.00% 1.0000 2.0800 3.2464 4.5061 5.8666 7.3359 8.9228 10.6366 12.4876 14.4866 16.6455 18.9771 21.4953 24.2149 27.1521 30.3243 33.7502 37.4502 41.4463 45.7620 50.4229 113.2832 259.0565 9.00% 1.0000 2.0900 3.2781 4.5731 5.9847 7.5233 9.2004 11.0285 13.0210 15.1929 17.5603 20.1407 22.9534 26.0192 29.3609 33.0034 36.9737 41.3013 46.0185 51.1601 56.7645 136.3075 337.8824 10.00% 1.0000 2.1000 3.3100 4.6410 6.1051 7.7156 9.4872 11.4359 13.5795 15.9374 18.5312 21.3843 24.5227 27.9750 31.7725 35.9497 40.5447 45.5992 51.1591 57.2750 64.0025 164.4940 442.5926 11.00% 1.0000 2.1100 3.3421 4.7097 6.2278 7.9129 9.7833 11.8594 14.1640 16.7220 19.5614 22.7132 26.2116 30.0949 34.4054 39.1899 44.5008 50.3959 56.9395 64.2028 72.2651 199.0209 581.8261 12.00% 1.0000 2.1200 3.3744 4.7793 6.3528 8.1152 10.0890 12.2997 14.7757 17.5487 20.6546 24.1331 28.0291 32.3926 37.2797 42.7533 48.8837 55.7497 63.4397 72.0524 81.6987 241.3327 767.0914 20.00% 1.0000 2.2000 3.6400 5.3680 7.4416 9.9299 12.9159 16.4991 20.7989 25.9587 32.1504 39.5805 48.4966 59.1959 72.0351 87.4421 105.9306 128.1167 154.7400 186.6880 225.0256 1181.8816 7343.8578 27 A1 foc TABLE 4 L M N O P Q R S T U V 1 2 3 5.50% 4 0.94787 5 1.84632 6 2.69793 7 3.50515 8 4.27028 9 4.99553 10 5.68297 11 6.33457 12 6.95220 13 7.53763 14 8.09254 15 8.61852 16 9.11708 17 9.58965 18 10.03758 19 10.46216 20 10.86461 21 11.24607 22 11.60765 23 11.95038 24 12.27524 25 13.41393 26 14.53375 27 16.04612 28 29 6.00% 0.94340 1.83339 2.67301 3.46511 4.21236 4.91732 5.58238 6.20979 6.80169 7.36009 7.88687 8.38384 8.85268 9.29498 9.71225 10.10590 10.47726 10.82760 11.15812 11.46992 11.76408 12.78336 13.76483 15.04630 7.00% 0.93458 1.80802 2.62432 3.38721 4.10020 4.76654 5.38929 5.97130 6.51523 7.02358 7.49867 7.94269 8.35765 8.74547 9.10791 9.44665 9.76322 10.05909 10.33560 10.59401 10.83553 11.65358 12.40904 13.33171 8.00% 0.92593 1.78326 2.57710 3.31213 3.99271 4.62288 5.20637 5.74664 6.24689 6.71008 7.13896 7.53608 7.90378 8.24424 8.55948 8.85137 9.12164 9.37189 9.60360 9.81815 10.01680 10.67478 11.25778 11.92461 9.00% 0.91743 1.75911 2.53129 3.23972 3.88965 4.48592 5.03295 5.53482 5.99525 6.41766 6.80519 7.16073 7.48690 7.78615 8.06069 8.31256 8.54363 8.75563 8.95011 9.12855 9.29224 9.82258 10.27365 10.75736 10.00% 0.90909 1.73554 2.48685 3.16987 3.79079 4.35526 4.86842 5.33493 5.75902 6.14457 6.49506 6.81369 7.10336 7.36669 7.60608 7.82371 8.02155 8.20141 8.36492 8.51356 8.64869 9.07704 9.42691 9.77905 11.00% 0.90090 1.71252 2.44371 3.10245 3.69590 4.23054 4.71220 5.14612 5.53705 5.88923 6.20652 6.49236 6.74987 6.98187 7.19087 7.37916 7.54879 7.70162 7.83929 7.96333 8.07507 8.42174 8.69379 8.95105 12.00% 0.89286 1.69005 2.40183 3.03735 3.60478 4.11141 4.56376 4.96764 5.32825 5.65022 5.93770 6.19437 6.42355 6.62817 6.81086 6.97399 7.11963 7.24967 7.36578 7.46944 7.56200 7.84314 8.05518 8.24378 20.00% 0.83333 1.52778 2.10648 2.58873 2.99061 3.32551 3.60459 3.83716 4.03097 4.19247 4.32706 4.43922 4.53268 4.61057 4.67547 4.72956 4.77463 4.81219 4.84350 4.86958 4.89132 4.94759 4.97894 4.99660 A1 TABLE 1 D E F G H I J K L M N O P B TABLE 1 Future Value of $1 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 periods 1.00% 1.50% 2.00% 2.50% 3.00% 3.50% 4.00% 4.50% 5.00% 5.50% 6.00% 1 1.01000 1.01500 1.02000 1.02500 1.03000 1.03500 1.04000 1.04500 1.05000 1.05500 1.06000 2 1.02010 1.03022 1.04040 1.05063 1.06090 1.07123 1.08160 1.09203 1.10250 1.11303 1.12360 3 1.03030 1.04568 1.06121 1.07689 1.09273 1.10872 1.12486 1.14117 1.15763 1.17424 1.19102 4 1.04060 1.06136 1.08243 1.10381 1.12551 1.14752 1.16986 1.19252 1.21551 1.23882 1.26248 5 1.05101 1.07728 1.10408 1.13141 1.15927 1.18769 1.21665 1.24618 1.27628 1.30696 1.33823 6 1.06152 1.09344 1.12616 1.15969 1.19405 1.22926 1.26532 1.30226 1.34010 1.37884 1.41852 7 1.07214 1.10984 1.14869 1.18869 1.22987 1.27228 1.31593 1.36086 1.40710 1.45468 1.50363 8 1.08286 1.12649 1.17166 1.21840 1.26677 1.31681 1.36857 1.42210 1.47746 1.53469 1.59385 9 1.09369 1.14339 1.19509 1.24886 1.30477 1.36290 1.42331 1.48610 1.55133 1.61909 1.68948 10 1.10462 1.16054 1.21899 1.28008 1.34392 1.41060 1.48024 1.55297 1.62889 1.70814 1.79085 11 1.11567 1.17795 1.24337 1.31209 1.38423 1.45997 1.53945 1.62285 1.71034 1.80209 1.89830 12 1.12683 1.19562 1.26824 1.34489 1.42576 1.51107 1.60103 1.69588 1.79586 1.90121 2.01220 13 1.13809 1.21355 1.29361 1.37851 1.46853 1.56396 1.66507 1.77220 1.88565 2.00577 2.13293 14 1.14947 1.23176 1. 1.31948 1.41297 1.51259 1.61869 1.73168 1.85194 1.97993 2.11609 2.26090 15 1.16097 1.25023 1.34587 1.44830 1.55797 1.67535 1.80094 1.93528 2.07893 2.23248 2.39656 16 1.17258 1.26899 1.37279 1.48451 1.60471 1.73399 1.87298 2.02237 2.18287 2.35526 2.54035 17 1.18430 1.28802 1.40024 1.52162 1.65285 1.79468 1.94790 2.11338 2.29202 2.48480 2.69277 18 1.19615 1.30734 1.42825 1.55966 1.70243 1.85749 2.02582 2.20848 2.40662 2.62147 2.85434 19 1.20811 1.32695 1.45681 1.59865 1.75351 1.92250 2.10685 2.30786 2.52695 2.76565 3.02560 20 1.22019 1.34686 1.48595 1.63862 1.80611 1.98979 2.19112 2.41171 2.65330 2.91776 3.20714 21 1.23239 1.36706 1.51567 1.67958 1.86029 2.05943 2.27877 2.52024 2.78596 3.07823 3.39956 25 1.28243 1.45095 1. 1.64061 1.85394 2.09378 2.36324 2.66584 3.00543 3.38635 3.81339 4.29187 30 1.34785 1.56308 1.81136 2.09757 2.42726 2.80679 3.24340 3.74532 4.32194 4.98395 5.74349 40 1.48886 1.81402 2.20804 2.68506 3.26204 3.95926 4.80102 5.81636 7.03999 8.51331 10.28572 7.00% 1.07000 1.14490 1.22504 1.31080 1.40255 1.50073 1.60578 1.71819 1.83846 1.96715 2.10485 2.25219 2.40985 2.57853 2.75903 2.95216 3.15882 3.37993 3.61653 3.86968 4.14056 5.42743 7.61226 14.97446 8.00% 1.08000 1.16640 1.25971 1.36049 1.46933 1.58687 1.71382 1.85093 1.99900 2.15892 2.33164 2.51817 2.71962 2.93719 3.17217 3.42594 3.70002 3.99602 4.31570 4.66096 5.03383 6.84848 10.06266 21.72452 9.00% 1.09000 1.18810 1.29503 1.41158 1.53862 1.67710 1.82804 1.99256 2.17189 2.36736 2.58043 2.81266 3.06580 3.34173 3.64248 3.97031 4.32763 4.71712 5.14166 5.60441 6.10881 8.62308 13.2676 31.40941 19 20 21 22 23 24 25 26 27 28 29 A1 X TABLE 1 J K L M N 0 P Q R S T U V 1 2 3 4.50% 5.00% 5.50% 6.00% 4 1.04500 1.05000 1.05500 1.06000 5 1.09203 1.10250 1.11303 1.12360 6 1.14117 1.15763 1.17424 1.19102 7 1.19252 1.21551 1.23882 1.26248 8 1.24618 1.27628 1.30696 1.33823 9 1.30226 1.34010 1.37884 1.41852 10 1.36086 1.40710 1.45468 1.50363 11 1.42210 1.47746 1.53469 1.59385 12 1.48610 1.55133 1.61909 1.68948 13 1.55297 1.62889 1.70814 1.79085 14 1.62285 1.71034 1.80209 1.89830 15 1.69588 1.79586 1.90121 2.01220 16 1.77220 1.88565 2.00577 2.13293 17 1.85194 1.97993 2.11609 2.26090 18 1.93528 2.07893 2.23248 2.39656 19 2.02237 2.18287 2.35526 2.54035 20 2.11338 2.29202 2.48480 2.69277 21 2.20848 2.40662 2.62147 2.85434 22 2.30786 2.52695 2.76565 3.02560 23 2.41171 2.65330 2.91776 3.20714 24 2.52024 2.78596 3.07823 3.39956 25 3.00543 3.38635 3.81339 4.29187 26 3.74532 4.32194 4.98395 5.74349 27 5.81636 7.03999 8.51331 10.28572 28 29 30 7.00% 1.07000 1.14490 1.22504 1.31080 1.40255 1.50073 1.60578 1.71819 1.83846 1.96715 2.10485 2.25219 2.40985 2.57853 2.75903 2.95216 3.15882 3.37993 3.61653 3.86968 4.14056 5.42743 7.61226 14.97446 8.00% 1.08000 1.16640 1.25971 1.36049 1.46933 1.58687 1.71382 1.85093 1.99900 2.15892 2.33164 2.51817 2.71962 2.93719 3.17217 3.42594 3.70002 3.99602 4.31570 4.66096 5.03383 6.84848 10.06266 21.72452 9.00% 1.09000 1.18810 1.29503 1.41158 1.53862 1.67710 1.82804 1.99256 2.17189 2.36736 2.58043 2.81266 3.06580 3.34173 3.64248 3.97031 4.32763 4.71712 5.14166 5.60441 6.10881 8.62308 13.26768 31.40942 10.00% 1.10000 1.21000 1.33100 1.46410 1.61051 1.77156 1.94872 2.14359 2.35795 2.59374 2.85312 3.13843 3.45227 3.79750 4.17725 4.59497 5.05447 5.55992 6.11591 6.72750 7.40025 10.83471 17.44940 45.25926 11.00% 1.11000 1.23210 1.36763 1.51807 1.68506 1.87041 2.07616 2.30454 2.55804 2.83942 3.15176 3.49845 3.88328 4.31044 4.78459 5.31089 5.89509 6.54355 7.26334 8.06231 8.94917 13.58546 22.89230 65.00087 12.00% 1.12000 1.25440 1.40493 1.57352 1.76234 1.97382 2.21068 2.47596 2.77308 3.10585 3.47855 3.89598 4.36349 4.88711 5.47357 6.13039 6.86604 7.68997 8.61276 9.64629 10.80385 17.00006 29.95992 93.05097 20.00% 1.20000 1.44000 1.72800 2.07360 2.48832 2.98598 3.58318 4.29982 5.15978 6.19174 7.43008 8.91610 10.69932 12.83918 15.40702 18.48843 22.18611 26.62333 31.94800 38.33760 46.00512 95.39622 237.37631 1469.77160 31 The Chestnut Street Company plans to issue a bond semiannually on March 31st and September 30th. The Controller has asked you to calculate information about the bond assuming two different market interest rates in the Excel Simulation below. The present value factor tables are included in the first four tabs of the Excel Simulation. Use the information included in the Excel Simulation and the Excel functions described below to complete the task. . Cell Reference: Allows you to refer to data from another cell in the worksheet. From the Excel Simulation below, if in a blank cell, =C6 was entered, the formula would output the result from cell C6, or 10 in this example. Multi-Tab Cell Reference: Allows you to refer to data from another cell in a separate tab in the worksheet. When using the multi-tab cell reference, type the equal sign first, then click on the other tab and then click on the cell you want to reference. The syntax of a multi-tab cell reference looks different than a normal cell reference, since it includes the tab name surrounded by apostrophes and also an exclamation point before the cell location. From the Excel Simulation below, if in a blank cell on the Sheet1 tab ='Future Value of $1'!C13" was entered, the formula would output the result from cell C13 in the Future Value of $1 tab, or 1.10462 in this example. Basic Math functions: Allows you to use the basic math symbols to perform mathematical functions. You can use the following keys: + (plus sign to add), - (minus sign to subtract), * (asterisk sign to multiply), and / (forward slash to divide). From the Excel Simulation below, if in a blank cell =C6+C8 was entered, the formula would add the values from those cells and output the result, or 12 in this example. If using the other math symbols the result would output an appropriate answer for its function. IF function: Allows you to test a condition and return a specific value is the result is true and different value if the result is false. The syntax of the IF function is =IF(test_condition,value_if_true,value_if_false)" and specific considerations need to be made when using this function. The test_condition argument is an evaluation of the status of a cell, such as if the value of a cell is greater than, less than, or equal to another number or cell. The value_if_true and value_if_false arguments will return any specific result for each option, such as another cell reference, a value, or text. Throughout the entire equation, if text is being used in the test_condition, value_if_true, or value_if_false arguments then the text itself should be entered in quotations so that Excel will recognize the text as a string of text instead of another function. From the Excel Simulation below, if in a blank cell =IF(C6>2,"Long-Term Bond,Short-Term Bond) was entered, the formula would output the result of the value_if_true since the test_condition would be result as true, or in this case the text Long-Term Bond. Excel processes the IF function by separating it out into separate parts. First the test_condition Excel thinks, find cell C6 and determine if the value is greater than 2. Once Excel determines if the result of that test_condition is TRUE or FALSE, it will return the value_if_true or value_if_false. VLOOKUP Function: Allows you find a value inside of a sorted data table by referencing the column and row labels. The syntax of the VLOOKUP function is =VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) and results in a value found from a data table. The lookup_value argument is the value to be found in the first column of the table. The table_array is the cell reference for the data table, usually shown as a range. The col_index_num argument is the column number in the data table (table_array) where the matching value should be found. The range_lookup argument is a logical value of TRUE or FALSE, where TRUE represents the value found in the first column should be a closest match, and FALSE represents the value found in the first column should be an exact match. From the Excel Simulation below, if in a blank cell "=VLOOKUP(C6,'Future Value of $1'!B3:T27,2,FALSE) was entered, the formula would output the result of 1.10462 in this example. Excel processes the VLOOKUP function by using each argument to find the cross-section of the column and row reference in the data table. In the example, Excel looked at the first column of the table_array on the Future Value of $1 tab cells B3:T27 and found the lookup_value of the Sheet1 cell C6 reference, or 10 periods in this example. That position in the first column is stored in Excel to know what row the final result will be found in. Remember that the first column used is the first column of the table_array and not the first column of the worksheet tab. Then the col_index_num is used by Excel to determine what column of the table_array the final result is included, or in this case the second column. Excel then finds the cross-section of those two values using the row and column references and outputs the final result from the data table. X Bond Pricing - Excel ? FILE HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW Sign In Connections Clear Properties A1 A2 Sort From From Access Web From From Other Text Sources Existing Connections Refresh All- Filter Reapply Advanced Outline Edit Links Data Tools Get External Data Connections Sort & Filter A1 fac The Chestnut Street Company plans to issue $825,000, 10-year bonds that pay 7 F G H B D E 1 The Chestnut Street Company plans to issue $825,000, 10-year bonds that pay 7 percent 2 semiannually on March 31st and September 30th. 3 4 Information relating to this bond is found below: 5 Face Value: 825,000 6 Number of Years: 10 7 Stated Interest Rate: 7% Number of Payments per 8 Year: 2 9 10 Required: 11 Calculate or provide the information requested using a formula or cell reference unless 12 you are instructed to use a specific function: 13 14 1) Assume the Market Interest Rate is: 8% 15 16 a. How many total payments or periods will this bond pay interest? 17 Bond Pricing - Excel ? 5 FILE HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW Sign In Clear 41 AA At Sort Reapply From From Access Web From From Other Text Sources 2 Connections e Properties Refresh All- Edit Links Connections Existing Connections Filter Data Outline Advanced Tools Get External Data Sort & Filter A1 The Chestnut Street Company plans to issue $825,000, 10-year bonds that pay 7 A B D E F G H 17 18 b. 19 When calculating the bond selling price, show the factor from the appropriate future or present value table (found in worksheets included in this workbook) that would be used to calculate the bond interest payments. 20 21 22 23 c. 24 Repeat question b. but now use the VLOOKUP function. For the Lookup_value argument, your function should reference the result you calculated in question a. Your function should look for an exact match to the value used in the Lookup_value argument. 25 26 27 28 d. Calculate the amount of interest that will be paid on March 31st. 29 ILITO 30 e. Calculate the value of the interest payments that would be used when determining the bond selling price. 31 32 33 f. Calculate the selling price of this bond: 34 X Bond Pricing - Excel ? FILE HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW Sign In 9 Connections Clear 21 ] From From Access Web From From Other Text Sources Existing Connections Refresh All- Properties Edit Links AL Sort Filter Reapply Advanced Data Outline Tools Get External Data Connections Sort & Filter A1 The Chestnut Street Company plans to issue $825,000, 10-year bonds that pay 7 A B D E G H 34 35 g Using the IF function, show the word "Premium" or "Discount" based on the selling price you calculated in letter f. 36 37 38 2) Assume the Market Interest Rate is: 6% 39 40 a. 41 When calculating the bond selling price, show the factor from the appropriate future or present value table (found in worksheets included in this workbook) that would be used to calculate the bond interest payments. 42 43 44 45 b. 46 Repeat question a. but now use the VLOOKUP function. Your function should reference the result you calculated in question Number 1 letter a.) Your function should look for an exact match to the value used in the Lookup_value argument. 47 48 49 50 c. Calculate the amount of interest that will be paid on March 31st. 51 51 52 d. Calculate the value of the interest payments that would be used when determining the bond selling price. 53 54 55 e. Calculate the selling price of this bond: IND 56 57 f. Using the IF function, show the word "Premium" or "Discount" based on the selling price you calculated in letter e. 58 59 60 61 62 1 Present Value of Annuity of $1 Bond Pricing

Answer & Explanation Solved by verified expert

See Answer