NEED MAJOR HELP! ALL ANSWERS MUST BE IN EXCEL FORMULAS!!
Ruiz Company issued bonds on January 1 and has provided the relevant information. The Controller has asked you to calculate the bond selling price given two different market interest rates using the Present Value Tables. 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. =C3 was entered the formula would output the result from cell C3, or $500,000 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 SheeritabFuture Value of $1C13* was entered the formula would output the result from cell C13 in the Future Value of Sitab. or 110462 in this example Basic Math functions: Allows you to use the basic math symbois 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, it in a blank cell-C4-C6 was entered the formula would add the values from those cells and output the resultor 31 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 Frest condition value it true value_faise 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 the value of a cells greater than less than or equal to another number or cell The value true and value. Ifalse 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 rest condition value frue, or value baise 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. Fin a blank cell -F03250000 Cash is great. Cash is bad") was entered the formula would output the result of the value true since the test condition would be result as true, or in this case the textCash is great". Excel processes the If function by separating it out into separate parts. First the test.condition Excel thinks, find cel C3 and determine if the value is greater man 250000 Once Excel determines if the result of that test condition is TRUE or FALSE, W return the valve rue or lease A1 On January 1, Ruiz Company issued bonds as follows: A 6 D E 1 In Janualy 1, Ruiz Company issued bonds as follows. 2 3 4 Face Value: $ Number of Years: Stated Interest Rate: Interest payments per year (Note: the bonds pay interest annually.) 500,000 30 794 5 6 8 tequired: 20 ) Calculate the bond selling price given the two market interest rates below. Jse formulas that reference data from this worksheet and from the appropriate future or present value tables (found by clicking the tabs at the bottom of this worksheet). 1.3 lote: Rounding is not required 14 Market interest Rate 94 35 16 17 Annual interest Payment PV of Face Value - PV of Interest Payments Bond Selling Price 10 5 25,000.00 =C16/5C55/8-024 SC54) --PV/C14 5C54.CIG 5 397 253.46 39 20 Market Interest Rate 5.5 22 20 Annual interest Payment 25.000.00 y of si Present Value of Annuity of 51 Sheet1 READ Paste BTU Alignment Number Cells Conditional Formatas Weit Formatting Table 5tes Styles Clitoard G Foot A1 TABLE 3 5 A B C D F G H K E TABLE Future Value of an Ordinary Azmaity of SI 3 Periods 1.00% 1.50% 2.00% 2.50% 3.009 3.50% 4.0096 4 1 1.0000 1.0000 1.0000 L.0000 10000 10000 10000 2 2.0100 2.0150 20200 20250 20300 2:0350 20400 3 30301 3.0452 3:0604 3:0756 3.0909 3 1062 3.1216 4 4.0604 4.0909 41216 4 1525 4.1836 42149 42465 5 5 1010 5.1523 5 2040 5.2553 5309 5.3625 5.4163 9 6 6.1520 62296 6 3081 6.3877 64634 6.5502 6 6330 10 7 7:2135 73230 74343 7.5474 76525 7.7794 7.8983 3 8.2857 84328 8.5930 8.7361 38923 9.0527 9.2142 12 9 9.3685 9.5593 97545 9.9545 10.459 10.358510 5928 13 10 20 4622 10.7027 10.9497 11 2034 11 453911334 33.0061 14 115653 11 3633 12.1957 52.4835 32 5075 13.1420 23 4554 15 12 12 5525 13.0412 13.4121237955 14 3920 14.6020 15 0255 16 13 3093 14.2369 146503 15 3404 5.5178 16 130 15 6253 149474 15.4504 159739355390120553 12.6770 16 2919 15 16.0969 15 5521 17 2934 379319 18 5959 19.1957 200236 19 16 17 257912.932418 639319.3302203569 209710215245 20 17 15.4304 39 2014 20 012120.8647 217515 32.7050 13.5975 21 18 19.5247 20 4594 214129323563 23.444 24 4997 25.6454 19 2013109 21 7967 22 5405 33 9450 25169 26 3572 27.6712 23 22 0190231237 24397435 547 25 5704 25.292 2935 23 23. 2291 24 70525753215328.5745 30 2695 29692 25 25 25 2432 30 063032010234157 36.459338.9499414459 uity of 51 Present Value of Annuity of 51 Sheet READY Attempt(s) 4.5096 5.00% 5.50 1.0000 10000 11.01 2.0450 210500 209 3.1370 3.1525 3.10 42782 43101 4:34 5.4707 5.5256 5.58 6.7469 6.8019 6.8 8.0192 82420 32 9 3900 9.5491 974 10 8021 310266 1121 12.2882 $2$779 13.3412 14.2068 14 SE 459171 1638 1711599 17730 18:20 18.9323 19.5985 20.25 20.7841 21 5756 22.40 22.7193 23.6575 24.54 24.7412 25.5404 25.99 26.555 28.1324 29.4 29 0636 305390 3210 31 3714 33 0660 3484 33.7831 357593 37.73 4565 4727 53 13 4640 + lo Paste BIUS A Alignment Number Cells Lonational romatas Cell Formatting Table Styles Styles Gipboard font A1 7 TABLE4 . C G G TABLE4 Present Value of an Ordinary Annuity of S1 4.00% 4 5 3 10 11 12 periods 1.009 1.5096 2.00% 2.50% 3.0096 2 0.99010 0.98522 0.98039 0.97551 0.97087 97040 195588 94156 192742 91347 294099 2.91220 299393 285602 292561 3 90197 3.55438 3.50773 376197 371710 435343 4.78264 458345 4 54593 457971 579548 5 6971 550.43 5.500 5.41719 6.72519 $59821 6.47599 6.34939 623028 755168 745593 732545 717014 701969 0.56502 536052 0.36224 797057 776521 10 949530 522215 8.90359 3.75206 353020 11 10.36753 10.07.12 9.73655 9 $1421 925252 12 1225505 10 90753 10 57534 10.25776 9.95400 12 12374 1173153 1234537 10.98333 1063496 14 1300270 1254338 110525 1163091 1129607 15 13.36505 334327 34925 1235135 21.93794 1471752 1413125 23.5772 305500 12 56110 15 56225 14 90765 2220 13.16512 18 1519327 35 57255 14 99203 35335 75353 19 12201 35.436.2 15.57945 349755932350 20 15.04555 5715564 1535143 15 5595 1457747 2. 2355595 7900:- 170.121 16 15455 15 $1902 25 220315 20795 2952345 15:42433 1741315 y of s: Present Value of Annuity of 51 Sheet 3.50% 0.96518 1.89969 2.80164 3,67305 4.51505 532855 612454 5.87396 750759 8.31661 9 00155 9.56333 10.30274 1092052 115174 12.09412 12.65132 3.16965 13.70954 1431340 1459797 15 45.5 15 16 17 0.96154 188609 2.77509 3 62990 445182 5.24214 600205 6.73234 743533 BAH1090 8.75048 9.38507 9.98555 10 56312 11 11839 1155230 12.16567 12.55930 13.13394 13 59083 2402915 15.62233 19 20 21 22 20 25 READY Ruiz Company issued bonds on January 1 and has provided the relevant information. The Controller has asked you to calculate the bond selling price given two different market interest rates using the Present Value Tables. 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. =C3 was entered the formula would output the result from cell C3, or $500,000 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 SheeritabFuture Value of $1C13* was entered the formula would output the result from cell C13 in the Future Value of Sitab. or 110462 in this example Basic Math functions: Allows you to use the basic math symbois 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, it in a blank cell-C4-C6 was entered the formula would add the values from those cells and output the resultor 31 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 Frest condition value it true value_faise 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 the value of a cells greater than less than or equal to another number or cell The value true and value. Ifalse 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 rest condition value frue, or value baise 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. Fin a blank cell -F03250000 Cash is great. Cash is bad") was entered the formula would output the result of the value true since the test condition would be result as true, or in this case the textCash is great". Excel processes the If function by separating it out into separate parts. First the test.condition Excel thinks, find cel C3 and determine if the value is greater man 250000 Once Excel determines if the result of that test condition is TRUE or FALSE, W return the valve rue or lease A1 On January 1, Ruiz Company issued bonds as follows: A 6 D E 1 In Janualy 1, Ruiz Company issued bonds as follows. 2 3 4 Face Value: $ Number of Years: Stated Interest Rate: Interest payments per year (Note: the bonds pay interest annually.) 500,000 30 794 5 6 8 tequired: 20 ) Calculate the bond selling price given the two market interest rates below. Jse formulas that reference data from this worksheet and from the appropriate future or present value tables (found by clicking the tabs at the bottom of this worksheet). 1.3 lote: Rounding is not required 14 Market interest Rate 94 35 16 17 Annual interest Payment PV of Face Value - PV of Interest Payments Bond Selling Price 10 5 25,000.00 =C16/5C55/8-024 SC54) --PV/C14 5C54.CIG 5 397 253.46 39 20 Market Interest Rate 5.5 22 20 Annual interest Payment 25.000.00 y of si Present Value of Annuity of 51 Sheet1 READ Paste BTU Alignment Number Cells Conditional Formatas Weit Formatting Table 5tes Styles Clitoard G Foot A1 TABLE 3 5 A B C D F G H K E TABLE Future Value of an Ordinary Azmaity of SI 3 Periods 1.00% 1.50% 2.00% 2.50% 3.009 3.50% 4.0096 4 1 1.0000 1.0000 1.0000 L.0000 10000 10000 10000 2 2.0100 2.0150 20200 20250 20300 2:0350 20400 3 30301 3.0452 3:0604 3:0756 3.0909 3 1062 3.1216 4 4.0604 4.0909 41216 4 1525 4.1836 42149 42465 5 5 1010 5.1523 5 2040 5.2553 5309 5.3625 5.4163 9 6 6.1520 62296 6 3081 6.3877 64634 6.5502 6 6330 10 7 7:2135 73230 74343 7.5474 76525 7.7794 7.8983 3 8.2857 84328 8.5930 8.7361 38923 9.0527 9.2142 12 9 9.3685 9.5593 97545 9.9545 10.459 10.358510 5928 13 10 20 4622 10.7027 10.9497 11 2034 11 453911334 33.0061 14 115653 11 3633 12.1957 52.4835 32 5075 13.1420 23 4554 15 12 12 5525 13.0412 13.4121237955 14 3920 14.6020 15 0255 16 13 3093 14.2369 146503 15 3404 5.5178 16 130 15 6253 149474 15.4504 159739355390120553 12.6770 16 2919 15 16.0969 15 5521 17 2934 379319 18 5959 19.1957 200236 19 16 17 257912.932418 639319.3302203569 209710215245 20 17 15.4304 39 2014 20 012120.8647 217515 32.7050 13.5975 21 18 19.5247 20 4594 214129323563 23.444 24 4997 25.6454 19 2013109 21 7967 22 5405 33 9450 25169 26 3572 27.6712 23 22 0190231237 24397435 547 25 5704 25.292 2935 23 23. 2291 24 70525753215328.5745 30 2695 29692 25 25 25 2432 30 063032010234157 36.459338.9499414459 uity of 51 Present Value of Annuity of 51 Sheet READY Attempt(s) 4.5096 5.00% 5.50 1.0000 10000 11.01 2.0450 210500 209 3.1370 3.1525 3.10 42782 43101 4:34 5.4707 5.5256 5.58 6.7469 6.8019 6.8 8.0192 82420 32 9 3900 9.5491 974 10 8021 310266 1121 12.2882 $2$779 13.3412 14.2068 14 SE 459171 1638 1711599 17730 18:20 18.9323 19.5985 20.25 20.7841 21 5756 22.40 22.7193 23.6575 24.54 24.7412 25.5404 25.99 26.555 28.1324 29.4 29 0636 305390 3210 31 3714 33 0660 3484 33.7831 357593 37.73 4565 4727 53 13 4640 + lo Paste BIUS A Alignment Number Cells Lonational romatas Cell Formatting Table Styles Styles Gipboard font A1 7 TABLE4 . C G G TABLE4 Present Value of an Ordinary Annuity of S1 4.00% 4 5 3 10 11 12 periods 1.009 1.5096 2.00% 2.50% 3.0096 2 0.99010 0.98522 0.98039 0.97551 0.97087 97040 195588 94156 192742 91347 294099 2.91220 299393 285602 292561 3 90197 3.55438 3.50773 376197 371710 435343 4.78264 458345 4 54593 457971 579548 5 6971 550.43 5.500 5.41719 6.72519 $59821 6.47599 6.34939 623028 755168 745593 732545 717014 701969 0.56502 536052 0.36224 797057 776521 10 949530 522215 8.90359 3.75206 353020 11 10.36753 10.07.12 9.73655 9 $1421 925252 12 1225505 10 90753 10 57534 10.25776 9.95400 12 12374 1173153 1234537 10.98333 1063496 14 1300270 1254338 110525 1163091 1129607 15 13.36505 334327 34925 1235135 21.93794 1471752 1413125 23.5772 305500 12 56110 15 56225 14 90765 2220 13.16512 18 1519327 35 57255 14 99203 35335 75353 19 12201 35.436.2 15.57945 349755932350 20 15.04555 5715564 1535143 15 5595 1457747 2. 2355595 7900:- 170.121 16 15455 15 $1902 25 220315 20795 2952345 15:42433 1741315 y of s: Present Value of Annuity of 51 Sheet 3.50% 0.96518 1.89969 2.80164 3,67305 4.51505 532855 612454 5.87396 750759 8.31661 9 00155 9.56333 10.30274 1092052 115174 12.09412 12.65132 3.16965 13.70954 1431340 1459797 15 45.5 15 16 17 0.96154 188609 2.77509 3 62990 445182 5.24214 600205 6.73234 743533 BAH1090 8.75048 9.38507 9.98555 10 56312 11 11839 1155230 12.16567 12.55930 13.13394 13 59083 2402915 15.62233 19 20 21 22 20 25 READY
