I NEED ExCEL FORMULAS
1. Calculate the price of a bond using tables. ? . . HOME Bond Pricing - Excel FORMULAS DATA - 6 Sign In FILE INSERT PAGE LAYOUT REVIEW VIEW * Calibri 11 A * A A Paste B I U . FR- , Cells Editing Alignment Number Conditional Format as Cell - Formatting Table Styles Styles Font Clipboard A1 X fx On January 1, Ruiz Company issued bonds as follows: On January 1, Ruiz Company issued bonds as follows: $ 500,000 15 Face Value: Number of Years: Stated Interest Rate: Interest payments per year Interest Payments per year 795 9 Required: 10 1) Calculate the bond selling price given the two market interest rates below. 11 Use formulas that reference data from this worksheet and from the appropriate future or 12 present value tables (found by clicking the tabs at the bottom of this worksheet). 13 Note: Rounding is not required. 14 a) Annual Market Rate Semiannual Interest Payment: PV of Face Value: + PV of Interest Payments: 10 1) Calculate the bond selling price given the two market interest rates below. 11 Use formulas that reference data from this worksheet and from the appropriate future or 12 present value tables (found by clicking the tabs at the bottom of this worksheet). 13 Note: Rounding is not required. Annual Market Rate 9% Semiannual Interest Payment: JPV of Face Value: + PV of Interest Payments: Bond Selling Price: 21 b) Annual Market Rate 6.0% Semiannual Interest Payment: PV of Face Value: + PV of Interest Payments: = Bond Selling Price: 28 2. Use the Excel IF function to answer either "Premium" or "Discount" to the following items. The bond in (a) sold at a: ai soldata The bond in (b) sold at a: ... ty of $1 Present Value of Annuity of $1 Sheet1 ... READY A1 x fx On January 1, Ruiz Company issued bonds as follows: Bond Selling Price: Annual Market Rate 6.0% Semiannual Interest Payment: PV of Face Value: PV of Interest Payments: Bond Selling Price: 28 2. Use the Excel IF function to answer either "Premium" or "Discount" to the following items. The bond in (a) sold at a: The bond in (b) sold at a: 34 3. Use the Excel PV FUNCTION (fx) to verify the selling prices of the bonds. 35 a) Annual Market Rate / 9% Bond Selling Price Annual Market Rate Bond Selling Price Al v v fx TABLE 3 A B C D E F 1 TABLE 3 Future Value of an Ordinary Annuity of $1 G H I J K L Periods 1.00% 1.50% 2.00% 2.50% 3.00% 3.50% 4.00% 1.0000 1.0000 1.0000 1.0000 1.0000 1.0000 1.0000 2.0100 2.0150 2.0200 2.0250 2.0300 2.0350 2.0400 3.0301 3.0452 3.0604 3.0756 3.0909 3.1062 3.1216 4,0604 4.0909 4.1216 4.1525 4.1836 4.2149 4.2465 5.1010 5.1523 5.2040 5.2563 5.3091 5.3625 5.4163 6.1520 6.2296 6.3081 6.3877 6.4684 6.5502 6.6330 7.2135 7.3230 7.4343 7.5474 7.6625 7.7794 7.8983 8.2857 8.4328 8.5830 8.7361 8.8923 9.0517 9.2142 9.3685 9.5593 9.7546 9.9545 10.1591 10.3685 10.5828 104622 10.7027 10.9497 11.2034 11.4639 11.7314 12.0061 11.5668 11.8633 12.1687 12.4835 12.8078 13.1420 13.4864 12.6825 13.0412 13.4121 13.7956 14.1920 14.6020 15.0258 13.8093 14.2368 14.6803 15.1404 15.6178 16.1130 16.6268 14.9474 15.4504 15.9739 16.5190 17.0863 17.6770 18.2919 16.0969 16.6821 17.2934 17.9319 18.5989 19.2957 20.0236 17.2579 17.9324 18.6393 19.3802 20.1569 20.9710 21.8245 18.4304 19.2014 20.0121 20.8647 21.7616 22.7050 23.6975 19.6147 20.4894 21.4123 22.3863 23.4144 24.4997 25.6454 20.8109 21.7967 22.8406 23.9460 25.1169 26.3572 27.6712 22.0190 23.1237 24.2974 25.5447 26.8704 28.2797 29.7781 23.2392 24 4705 25.7833 27.1833 28.6765 30.2695 31.9692 28.2432 30.0630 32.0303 34.1578 36.4593 38.9499 41.6459 . ... Suity of $1 Present Value of Annuity of $1 Sheet ... + 4.50% 5.00% 5.505 1.0000 1.0000 1.00 2.0450 2.0500 2.05 3.1370 3.1525 3.16 4.2782 4.3101 4.34 5.4707 5.5256 5.58 6.7169 6.8019 6.88 8.0192 8.1420 8.24 9.3800 9.5491 9.72 10.8021 11.0266 11.25 12.2882 12.5779 12.87 13.8412 14.2068 14.58 15.4640 15.9171 16.38 17.1599 17.7130 18.28 18.9321 19.5986 20.29 21.5786 22.40 22.7193 23.6575 24.64 24.7417 25.8404 26.99 26.8551 28.1324 29.48 29.9636 30.539032.10 31.44 33.0660 34.86 33.7831 35.7193 37.78 44.5652 47.7271 51.15. Clipboard Font 41 B X fx TABLE 3 C D E F G H J K L 10.4622 10.7027 10.9497 11.2034 11.4639 11.7314 12.0061 12.2882 12.5779 12.87 11.5668 11.8633 12.1687 12.4835 12.8078 13.1420 13.4864 13.8412 14.2068 14.58 12.6825 13.0412 13,4121 13.7956 14.1920 14.6020 15.0258 15.4640 15.9171 16.38 13.8093 14.2368 14.6803 15.1404 15.6178 16.1130 16.6268 17.1599 17.7130 18.28 14.9474 15.4504 15.9739 16.5190 17.0863 17.6770 18.2919 18.9321 19.5986 20.29 16.0969 16.6821 17.2934 17.9319 18.5989 19.2957 20.0236 20.7841 21.5786 22.40 17.2579 17.9324 18.6393 19.3802 20.1569 20.9710 21.8245 22.7193 23.6575 24.64 18.4304 19.2014 20.0121 20.8647 21.7616 22.7050 23.6975 19.6147 20.4894 21.4123 22.3863 23.4144 24.4997 25.6454 26.8551 28.1324 20.8109 21.7967 22.8406 23.9460 25.1169 26.3572 27.6712 29.0636 30.5390 32.10 22.0190 23.1237 24.2974 25.5447 26.8704 28.2797 29.7781 31.3714 33.0660 34.80 23.2392 24.4705 25.7833 27.1833 28.6765 30.2695 31.9692 33.7831 35.7193 37.78 28.2432 30.0630 32.0303 34.1578 36.4593 38.9499 41.6459 44.5652 47.7271 51.15 34.7849 37.5387 40.5681 43.9027 47.5754 51.6227 56.0849 61.0071 66.4388 72.43 48.8864 54.2679 60.4020 67.4026 75.4013 84.5503 95.0255 107.0303 120.7998 136.60 Clipboard Font Styles A1 A B C 1 TABLE 1 Future Value of $1 D periods 1.00% 1.50% 2.00% 2.50% 3.00% 3.50% 4.00% 4.50% 5.00% 5.50% 1.01000 1.01500 1.02000 1.02500 1.03000 1.03500 1.04000 1.04500 1.05000 1.05500 1.02010 1.03022 1.04040 1.05062 1.06090 1.07123 1.08160 1.09203 1.10250 1.11302 1.03030 1.04568 1.06121 1.07689 1.09273 1.10872 1.12486 1.14117 1.15762 1.17424 1.04060 1.06136 1.08243 1.10381 1.12551 1.14752 1.16986 1.19252 1.21551 1.23882 1.05101 1.07728 1.10408 1.13141 1.15927 1.18769 1.21665 1.24618 1.27628 1.30696 1.06152 1.09344 1.12616 1.15969 1.19405 1.22926 1.26532 1.30226 1.34010 1.37884 1.07214 1.10984 1.14869 1.18869 1.22987 1.27228 1.31593 1.36086 1.40710 1.45468 1.08286 1.12649 1.17166 1.21840 1.26677 1.31681 1.36857 142210 1.47746 1.53469 1.09369 1.14339 1.19509 1.24886 1.30477 1.36290 1.42331 1.48610 1.55133 1.61909 1.10462 1.16054 1.21899 1.28008 1.34392 141060 1.48024 1.55297 1.62889 1.70814 1.11567 1.17795 1.24337 1.31209 1.38423 145997 1.53945 1.62285 1.71034 1.80209 1.12683 1.19562 1.26824 1.34489 142576 1.51107 1.60103 1.69588 1.79586 1.90121 1.13809 1.21355 1.29361 1.37851 1.46853 1.56396 1.66507 1.77220 1.88565 2.00577 1.14947 1.23176 1.31948 1.41297 1.51259 1.61869 1.73168 1.85194 1.97993 2.11609 1.16097 1.25023 1.34587 1.44830 1.55797 1.67535 1.80094 1.93528 2.07893 2.23248 1.17258 1.26899 1.37279 148451 1.60471 1.73399 1.87298 202237 2.18287 2.35526 1.18430 1.28802 140024 1.52162 1.65285 1.79468 1.94790 2.11338 2.29202 2.48480 1.19615 1.30734 1.42825 1.55966 1.70243 1.85749 2.02582 2.20848 2.40662 2.62147 19 1.20811 1.32695 1.45681 1.59865 1.75351 1.92250 2.10685 2.30786 2.52695 2.76565 1.22019 1.34686 148595 1.63862 1.80611 1.98979 2.19112 2.41171 2.65330 2.91776 1.23239 1.36706 1.51567 1.67958 1.86029 2.05943 2.27877 2.52024 2.78596 3.07823 1.28243 1.45095 1.64061 1.85394 2.09378 2.36324 2.66584 3.00543 3.38635 3.81339 ... Future Value of $1 Present Value of $1 Putur ... 18 X B C Present Value of $1 TABLE 2 E D F TABILE -no99 periods 1.00% 1.50% 2.00% 2.50% 3.00% 3,50% 4.00% 4.50% 5.00% 5.50% 6. 0.99010 0.98522 0.98039 0.97561 0.97087 0.96618 0.96154 0.95694 0.95238 0.94787 0. 0.98030 0.97066 0.96117 0.95181 0.94260 0.93351 0.92456 0.91573 0.90703 0.89845 0. 0.97059 0.95632 0.94232 0.92860 0.91514 0.90194 0.88900 0.87630 0.86384 0.85161 0. 0.96098 0.94218 0.92385 0.90595 0.88849 0.87144 0.85480 0.83856 0.82270 0.80722 0 0.95147 0.92826 0.90573 0.88385 0.86261 0.84197 0.82193 0.80245 0.78353 0.76513 0. 0.94205 0.91454 0.88797 0.86230 0.83748 0.81350 0.79031 0.76790 0.74622 0.72525 0. 0.93272 0.90103 0.87056 0.84127 0.81309 0.78599 0.75992 0.73483 0.71068 0.68744 0. 0.92348 0.88771 0.85349 0.82075 0.78941 0.75941 0.73069 0.70319 0.67684 0.65160 0. 0.91434 0.87459 0.83676 0.80073 0.76642 0.73373 0.70259 0.67290 0.64461 0.61763 0. 0.90529 0.86167 0.82035 0.78120 0.74409 0.70892 0.67556 0.64393 0.61391 0.58543 0. 0.89632 0.84893 0.80426 0.76214 0.72242 0.68495 0.64958 0.61620 0.58468 0.55491 0. 0.88745 0.83639 0.78849 0.74356 0.70138 0.66178 0.62460 0.58966 0.35684 0.52598 0. 0.87866 0.82403 0.77303 0.72542 0.68095 0.63940 0.60057 0.56427 0.53032 0.498560. 0.86996 0.81185 0.75788 0.70773 0.66112 0.61778 0.57748 0.53997 0.50507 0.47257 0. 0.86135 0.79985 0.74301 0.69047 0.64186 0.59689 0.55526 0.51672 0.48102 0.44793 0. 0.85282 0.78803 0.72845 0.67362 0.62317 0.57671 0.53391 0.49447 0.45811 0.42458 0. 0.84438 0.77639 0.71416 0.65720 0.60502 0.55720 0.51337 0.47318 0.43630 0.40245 0. 0.83602 0.76491 0.70016 0.64117 0.58739 0.53836 0.49363 045280 0.41552 0.38147 0. 0.82774 0.75361 0.68643 0.62553 0.57029 0.52016 0.47464 0.43330 0.39573 0.36158 0. 0.81954 0.74247 0.67297 0.61027 0.55368 0.50257 0.45639 0.41464 0.37689 0.34273 0. 0.81143 0.73150 0.65978 0.59539 0.53755 0.48557 0.43883 0.39679 0.35894 0.32486 0. 0.77977 0.68921 0.60953 0.53939 0.47761 0.42315 0.37512 0.33273 0.29530 0.26223 0. ... Future Value of $1 Present Value of $1 Futur ... Al X fx TABLE 4 A B C DE D 1 TABLE4 Present Value of an Ordinary Annuity of $1 periods 1.00% 1.50% 2.00% 0.99010 0.98522 0.98039 1.97040 1.95588 1.94156 2.94099 2.91220 2.88388 3.90197 3.85438 3.80773 4.85343 4.78264 4.71346 5.79548 5.69719 5.60143 6.72819 6.59821 6.47199 7.65168 7.48593 7.32548 8.56602 8.36052 8.16224 9.47130 9.22218 8.98259 10.36763 10.07112 9.78685 11.25508 10.90751 10.57534 12.13374 11.73153 11.34837 13.00370 12.54338 12.10625 13.86505 13.34323 12.84926 14.71787 14.13126 1357771 15.56225 14.90765 14.29187 16.39827 15.67256 14.99203 17.22601 16.42617 15.67846 18.04555 17.16864 16.35143 18.85698 17.90014 17.01121 22.02316 20.71961 19.52346 Present Value of Annuity of $1 Sheet 2.50% 0.97561 1.92742 2.85602 3.76197 4.64583 5.50813 6.34939 7.17014 7.97087 8.75206 9.51421 10.25776 10.98318 11.69091 12.38138 13.05500 13.71220 14.35336 14.97889 15.58916 16.18455 18 42438 ... 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 1741315 3.50% 4.00% 0.96618 0.96154 1.89969 1.88609 2.80164 2.77509 3.67308 3.62990 4.51505 4.45182 5.32855 5.24214 6.11454 6.00205 6.87396 6.73274 7.60769 7.43533 8.31661 8.11090 9.00155 8.76048 9.66333 9.38507 10.30274 9.98565 10.92052 10.56312 11.51741 11.11839 12.09412 11.65230 12.65132 12.16567 13.18968 12.65930 13.70984 13.13394 14.21240 13.59033 14.69797 14.02916 16.48151 15.62208 ... * $1
