I need the formulas for each cell

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: 79 Number of Payments per Year: 8 9 10 Required: 11 Calculate or provide the information requested using a formula or cell reference unless you are instructed to use a specific function: Assume the Market Interest Rate is: 8% How many total payments or periods will this bond pay interest? 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 12 13 14 1) IS 16 a. 17 18 b 19 20 21 22 Danant action h hutnatha Y VID antin 1 Present Value of annid bond interest payments. 2 4 15 26 27 28 d. 29 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. Calculate the amount of interest that will be paid on March 31st. Calculate the value of the interest payments that would be used when determining the bond selling price. 30 e. 31 32 33 34 35 Calculate the selling price of this bond: M Using the IF function, show the word "Premium" or "Discount" based on the selling price you calculated in letter f. 37 2) Assume the Market Interest Rate is: 6% 40 a 41 42 43 44 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 Present Value of Annuity of $1 Bond Pricing Assume the Market Interest Rate is: 6% 38 2) 39 40 a. 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 47 48 49 50 C. 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. Calculate the amount of interest that will be paid on March 31st, Calculate the value of the interest payments that would be used when determining the bond selling price. Calculate the selling price of this bond: 51 52 d. 53 54 55 56 57 58 59 60 Using the IF function, show the word "Premium" or "Discount" based on the selling price you calculated in lettere. Procent E G H 1 Future Value of S1 K L 1 TABLE 2 3 4 5 6 SOOK 2 8 9 10 11 12 13 14 15 16 12 periods 1.00% 1.50% 2.00% 2.50% 3.00% 3.50% 4.00% 4.50% 5.00% 5.50% 1 1.01000 1.01500 1.02000 102500 103000 1.03500 1.04000 1.04500 105000 1.05500 LC 2 1.02010 1.03022 104040 1.05063 1.06090 1.07123 108160109203 1.10250 1.11303 11 3 1.03030 1.04568 1.06121 1.07689 1.09273 1.10872 1.12486 1.14117 1.15763 1.17424 1.1 4 1.04000 1.06136 1.08243 1.10381 1.12551 1.14752 1.16986 1.19252 1.21551 1.23882 1 5 1.05101 107728 1.10408 1.13141 1.15927 1.18769 1.21665 1.24618 127628 130696 1. 6 106152 109344 1.12616 115969 1.19405 1.22926 1.26532 1.30226 1.34010 1.37884 14 7 1.07214 1.10984 1.14869 1.18869 1.22987 1.27228 1.31593 1.36086 140710 145468 13 8 108286 1.12649 1.17166 1.21840 1.26677 1.31681 1.36857 1.42210 1.47746 153469 13 9 1.09309 1.143.19 1.19509 1.24886 130477 1.36290 142331 1.48610 1.55133 1.61909 1.4 10 1.10462 1.16054 1.21899 1.28008 1.34392 141060 148024 1.55292 1.62889 1.70814 1.1 11 1.11567 1.17795 1.24337 1.31209 1.38423 1.45997 1.33945 1.62285 1.71034 L10209 1 12 1.12683 1.19562 1.26824 1.34489 1.42576 1.51107 1.60103 1.69588 1.79586 1.901212 13 1.13809 1.21355 1.29361 1.37851 1.46853 1.56396 1.66507 1.77220 1.88565 2.00577 2.1 14 1.14947 1.23176 1.31948 141297 1.51259 1.61869 1.73168 185194 197993 2.11609 2.2 15 1.16097 1.25023 1.34587 1.44830 1.55797 167535 1.80094 1.93528 2,07893 2.23248 2.1 16 1.17258 1.26899 137279 1.48451 1.60471 1.73399 1.87298 202237 2.18287 2.35526 2.5 17 1184 1.28802140024 1.52162 1.68285 1.794168 1.94790 2.11338 2.29202 2.48480 2.6 18 1.19615 1.30734 142325 1.55966 1.20243 185749 2.02582 2.20848 2.40562 2.62147 2.3 19 1.20811 132695 145681 159865 1.75351 192250 2.10683 230786 2.52695 2.76505 38 20 1.22019 1.34686 1.48595 169862 180611 1.98979 2.19112 2.41171 2.65 291776 3. 21 123239 1.36706 1.51567 1.67958 1.86029 2.0593 227877 252024 27596 3.07823 31 25 1.28243 1.45095 161061 1.85194 2.09378 2.36324 266554 3.00543 3.38635 381339 Future Value of $1 Present Value of st Hut 19 20 21 22 25 TABLE 2 . es B C D E F G 1 K L M Present Value of SI 2 3 periods 1.00% 1.50% 2.00% 2.50% 3.00% 3.50% 4.00% 4.50% 5.00 5504 6.00 7 4 1 0.99010 0.98522 098039 0.97561 0.97087 0.96618 0.96154 0.95694 0.95238 0.94787 0.94340 0.9 52 0.98030 0.97066 0.96117 0.95181 0.94260 093351 0.92456 0.91573 0.90703 0.89845 0.89000 0.8 63 097059 0.95632 0.94232 0.92860 0.91514 0.90194 0.88900 0.87630 0.86384 0.85161 0.83962 0.8 74 0.96098 0.94218 0.92385 0.90595 0.88849 0.87144 0.85480 0.83856 0.82270 0.80722 0.79209 0.1 8S 0.95147 0.92826 0.90573 0.88385 0.86261 0.84197 0.82193 0.80245 0.78353 0.76513 0.74726 0.7 96 0.94205 091454 0.88797 0.86230 083748 0.81350 0.79031 0.76790 0.74622 0.72525 0.70496 0. 107 0.93272 090103 0.87056 0,84127 0.81309 0.78599 0.75992 0.73483 0.71068 0.68744 0.66506 0.C 118 0.92348 0.88771 0.85349 0.82075 0.78941 0.75941 0.73069 0.70319 0.67684 0.65160 0.62741 0. 129 0.91434 0.87459 0,83676 0.80073 0.76612 0.73373 0.70259 0.67290 0.64461 0.61763 0.59190 0.5 13 10 090529 0.86167 0.82035 0.78120 0.74409 0.70892 0.67556 0.64393 0.61391 0.58543 0.55839 03 14 11 0.89632 0.84893 0.80426 0.76214 0.72242 0.68495 0.64958 0.61620 0.58468 0.55491 0.52679 04 15 12 0.88745 0.83639 0.78849 0.74356 0.70138 0.66178 0.62460 0.58966 0.55684 0.52598 0.49697 04 16 13 0.87866 0.82403 0.77303 0.72542 068095 0.639:40 0.60057 0.56427 0.53032 0.49856 0.46884 0.4 17 14 0.86996 0.81185 0.75788 0.70773 066112 0.61778 057748 0.53997 0.50507 0.47257 0.44230 01 18 15 0.86135 0.79965 0.74301 0.62047 0.64186 0.59689 0.35526 0.51672 0.48102 0.44793 0.41727 0.1 1916 0.85282 0.78803 0.72845 0.67362 0.62317 0.57671 0.53991 049447 0.45811 0.42458 0.39365 0.1 2017 0.84438 0.77639 0.71416 065720 0.0002 0.55720 0.51337 0.47318 0.43630 0,40245 0.37136 03 21 18 0.83602 0.76491 0.7016 064117 0.58739 0.53836 049363 0.45280 0.41552 0.38147 0.15034 03 22 19 0.82774 0.75361 0.68643 062553 0.57029 0.52016 047464 0.433.10 0.39373 0.36158 0.33051 0. 23 20 0X1954 0.74247 0.67297 0.61027 0.55368 0.50257 0.45619 0.4146 0.37689 0.34271 0.11 0 24 21 081143 0.73150 0.65978 0.59519 0.53755 0.48557 0.4383 039679035896 0.32486 0.2941602 25 24 0.78757 0.69954 0.62172 0.55288 0.49193 0.43796 0.39012 0.34770 0.31007 0.27666 0,24698 01. Future Value of $1 Present Value of $1 Futur L 2 3 Periods 1.00% 1.50% 2.00% 2.50% 3.00% 3.50% 4.00% 4.50% 5.00% 5.50% 6.009 4 1 1.0000 1.0000 1.0000 1.0000 1.0000 1.0000 1.0000 1.0000 1.0000 1.0000 1.0001 52 2.0100 2.0150 2.0200 2.0250 2.0300 2.0350 2.0400 2.0450 2.0500 2.0550 2.060 63 3.0301 3.0452 3,0604 3.0756 3.0909 3.1062 3.1216 3.1370 3.1525 3.1680 3.183 74 4.0604 4.0909 4.1216 4.1525 4.1836 4.2149 4.2465 4.2782 4.3101 4.3423 4.374 85 5.1010 5.1523 5.2040 5.2563 5.3091 5.3625 5.4163 5.4707 5.5256 5.5811 5.637 96 6.1520 6.2296 6.3081 6.3877 6.4684 6.5502 6.6330 6.7169 6.8019 6.8881 6.975 107 7.2135 7.3230 7.4343 75474 7.6625 7.7794 7.8983 8.0192 8.1420 8.2669 8.393 11 8 8.2857 8.4328 8.5830 8.7361 8.8923 9.0517 9.2142 9.3800 9.5491 9.7216 9.897 12 9 9.3685 9.5593 9.7546 9.9545 10.1591 10.3685 10.5828 10.8021 11.0266 11.2563 11.49 13 10 10.4622 10.7027 10.9497 11.2034 11.4639 11.7314 12.0061 12.2882 12.5779 12.8754 13.181 14 11 11.5668 11.8633 12.1687 12.4835 12.8078 13.1420 13.4864 13.8412 14.2068 14.5835 14.97 15 12 12.6825 13.0412 13.4121 13.7956 14.1920 14.6020 15.0258 15.4640 15.9171 16.3856 16.86 16 13 13.8093 14.2368 14.6803 15.1404 15.6178 16.1130 16.6268 17.1599 17.7130 18.2868 18.88 17 14 14.9474 154504 15.9739 16.5190 17.0863 17.6770 18.2919 18.9321 19.5986 20.2926 21.01 18 15 16.0969 16.6821 17.2934 17.9319 18.5989 19.2957 20.0236 20.7841 21.5786 22.4087 23.27 19 16 17.2579 17.9324 18.6393 19.3802 20.1569 20.9710 21.8245 22.7193 23.6575 24.6411 25.67 2017 18.4304 19.2014 20.0121 20.8647 21.7616 22.7050 23.6975 24.7417 25.8404 26.9964 28.21 21 18 19.6147 20.4894 21.4123 22.3863 23.4144 24.4997 25.6454 26,8551 28.1324 29.4812 30.90 22 19 20.8109 21.7967 22.8406 23.9460 25.1169 26.3572 27.6712 29.0636 30.5390 32.1027 33.76 23 20 22.0190 23.1237 24.2974 25.5447 26.8704 28.2797 29.7781 31.3714 33.0660 34.8683 36.78 24 21 23.2392 24 4705 25.7833 27.1833 28.6765 30.2695 31.9692 33.7831 35.7193 37.7861 39.99 25 30 34.7849 37.5387 40.5681 43.9027 47.5754 51.6227 56.084961.0071 66.4388 72.4355 79.05 26 40 48.8864 54.2679 60.4020 67 4026 75 4013 84 5503 95.0255 107.0303 120.7998 136.6056 Future Value of $1 Present Value of $1 Future 0 3 periods 4 1 S2 6 3 74 85 96 10 7 11 8 12 9 13 10 14 11 15 12 16 13 17 14 18 15 19 16 2017 21 18 22 19 23 20 24 21 25 25 26 30 27 40 D E F 1.00% 1.50% 2.0090 2.50% 0.99010 0.98522 0.98039 0.97561 1.97040 1.95588 1.94156 1.92742 2.94099 2.91220 2.88388 2.85602 3.90197 3.85438 3.80773 3.76197 4.85343 4.78264 4.71346 4.64583 5.79548 5.69719 5.60143 5.50813 6.72819 6.59821 6.47199 6.34939 7.65168 7.48593 7.32548 7.17014 8.56602 8.36052 8.16224 7.97087 9.47130 9.22218 8.98259 8.75206 10.36763 10.07112 9.78685 9.51421 11.25508 10.90751 10.57534 10.25776 12.13374 11.73153 11.34837 10.98319 13.00370 12.54338 12.10625 11.69091 13.86505 13.34323 12.84926 12.38138 14.71787 14.13126 13.57771 13.05500 15.56225 14.90765 14.29187 13.71220 16.39827 15.67256 14.99203 14.35336 1722601 16.42617 15.67846 14.97889 18.04555 17.16864 16.35143 15.58916 18.85698 17.90014 17.01121 16.18455 22.02316 20.71961 19.52346 18.42438 25.80771 24.01584 22.39646 20 93029 32.83469 29.91585 27.35548 25.10278 lity of $1 Present Value of Annuity of $1 Bon G 3.00% 0.97087 1.91347 2.82861 3.71710 4.57971 5.41719 6.23028 7.01969 7.78611 8.53020 9.25262 9.95400 10.63496 11.29607 11.93794 12.56110 13.16612 13.75351 14.32380 14.87747 15.41502 17.41315 19.60044 23.11477 3.50% 0.96618 1.89969 2.80164 3.67308 4.51505 5.32855 6.11454 6.87396 7.60769 8.31661 9.00155 9.66333 10.30274 10.92052 11.51741 12.09412 12.65132 13.18968 13.70984 14.21240 14.69797 16.48151 18.39205 21.35507 1 4.00% 0.96154 1.88609 2.77509 3.62990 4.45182 5.24214 6.00205 6.73274 7.43533 8.11090 8.76048 9.38507 9.98565 10.56312 11.11839 11.65230 12.16567 12.65930 13.13394 13.59033 14.02916 15.62208 17.29203 19.79277 J 4.50% 0.95694 1.87267 2.74896 3.58753 4.38998 5.15787 5.89270 6.59589 7.26879 7.91272 8.52892 9.11858 9.68285 10.22283 10.73955 11.23402 11.70719 12.15999 12.59329 13.00794 13.40472 14.82821 16.28889 18.40158 8 9 9 1