Bond Pricing using Excel's VLOOKUP, IF and Basic Math Functions 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" inste 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. cel 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:127,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. TABLE Future Value of an Ordinary Annuity of $1 Periods 1.00% 1.50% 2.00% 2.50% 3.00% 3.50% 4.00% 4.50% 5.00% 5.50% 6.00967 .0096 8.00% 1.0000 1.0000 1.0000 1.000 1.0000 1.0000 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 2.0450 2.0500 2.0550 2.0600 2.0700 2.0800 3.0301 3.0452 3.0604 3.0756 3.0909 3.1062 3.1216 3.1370 3.15253.1680 3.1836 3.2149 3.2464 4.0604 4.0909 4.1216 4.1525 4.1836 4.2149 4.2465 4.2782 4.3101 4.3423 4 .3746 4.4399 4.5061 5.1010 5.1523 5.2040 5.2563 5.3091 5.3625 5.4163 5.4707 5.5256 5.5811 5.6371 5.7507 5.8666 6.1520 6.2296 6.3081 6.3877 6.4684 6.5502 6.6330 6.7169 6.8019 6.8881 6.9753 7.1533 7.3359 7.2135 7.3230 7.4343 7.5474 7.66257.7794 7.8983 8.0192 8.1420 8.2669 8.3938 8.6540 8.9228 8.2857 8.4328 8.5830 8.7361 8.89239.0517 9.2142 9.3800 9.5491 9.7216 9.8975 10.2598 10.6366 9.3685 9.5593 9.7546 9.9545 10.1591 10.3685 10.5828 10.8021 11.0266 11.2563 11.4913 11.9780 12.4876 10.4622 10.7027 10.9497 11.2034 11.4639 11.7314 12.0061 12.2882 12.5779 12.8754 13.1808 13.8164 14.4866 11.5668 11.8633 12.1687 12.4835 12.8078 13.1420 13.4864 13.8412 14.2068 14.5835 14.9716 15.7836 16.6455 12.6825 13.0412 13.4121 13.7956 14.1920 14.6020 15.0258 15.4640 15.9171 16.3856 16.8699 17.8885 18.9771 13.8093 14.2368 14.6803 15.1404 15.6178 16.1130 16.6268 17.1599 17.7130 18.2868 18.8821 20.1406 21.4953 14.9474 15.4504 15.9739 16.5190 17.0863 17.6770 18.2919 18.9321 19.5986 20.2926 21.0151 22.5505 24.2149 16.0969 16.6821 17.2934 17.9319 18.5989 19.2957 20.0236 20.7841 21.5786 22.4087 23.276 27.1521 17.2579 17.9324 18.6393 19.3802 20.1569 20.9710 21.8245 22.7193 23.6575 24.6411 25.6725 27.8881 30.3243 18.4304 19.2014 20.0121 20.8647 21.7616 22.7050 23.6975 24.7417 25.8404 26.9964 28.2129 30.8402 33.7502 19.6147 20.4894 21.4123 22.3863 23.4144 24.4997 25.6454 26.855128.1324 29.4812 30.9057 33.9990 37.4502 20.8109 21.7967 22.8406 23.9460 25.1169 26.3572 27.6712 29.0636 30.5390 32.1027 33.7600 37.3790 41.4463 22.0190 23.1237 24.2974 25.5447 26.8704 28.2797 29.7781 31.3714 33.0660 34.868336.7856 40.9955 45.7620 23.2392 24.4705 25.7833 27.1833 28.6765 30.2695 31.9692 33.7831 35.7193 37.7861 39.9927 44.8652 50.4229 34.7849 37.5387 40.5681 43.9027 47.5754 51.6227 56.0849 61.0071 66.4388 72.4355 79.0582 94.4608 113.2832 48.8864 54.2679 60.4020 67.4026 75.4013 84.5503 95.0255 107.0303 120.7998 136.6056 154.7620 199.6351 259.0565 20 40 9.00% | 10.00 11.00% 12.00% 20.00% 1.0000 1.0000 10000 1.00001.0000 2.0900 2.100 2.1100 2.1200 2.2000 3.2782 3.310 221 744 3.6400 4.5731 4.6410 ... ? 2.73 5.3660 5.9847 6.1051 5.2278 52528 7.4416 7.5233 7.71567515 2.1152 5.9299 9.2004 5.4872 5.78: 10.0890 12.9159 11.0285 11.4359 11.8594 12.2997 16.4991 13.0210 | 13.5795 14.1540 14.7757 20.7989 15.1929 15.9374 16.722017.5487 25.9587 17.5603 18.5312 19.5614 20.6545 32.1504 20.1407 213813 22.7132 24.1:31 39.5805 22.9534 24.5227 26.2116 28.0291 48.4966 26.019227.9750 30.0242 23:26 59.1959 29.3609 31.7725 | 34.4054 37.2797 72.0351 33.0034 35.997 29.1892 42.7533 27.4421 36.9737 40.5447 44.500348.3837 | 105.9306 1.3013 45.5552 50.35555.7457122.1157 46.0185 51.159156.9395 63.397154.7400 51.1601 57.2750 54.202272.0-24 185.6820 56.7645 64.0025 12.2651 3.6687 25.0256 136.3075 164.4940 199.0209 241.3327 1131.8616 337.8624 4425526 81.2261767.0-14 1343.3578 | Present Value of an Ordinary Annuity of $1 periods 5.32855 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.853434 .78264 4.71346 5.79548 5.69719 5.60143 6.72819 6.598216.47199 7.65168 7.485937 .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 13.57771 15.56225 14.90765 14.29187 16.39827 15.67256 14.99203 17.2260116.42617 15.67846 18.04555 17.16864 16.35143 18.85698 17.90014 17.01121 22.02316 20.71961 19.52346 25.80771 24.01584 22.39646 32.8346929.91585 27.35548 2.50% 3.0096 3.50% 4.00% 4.50% 5.00% 5.50% 0.97561 0.97087 0.96618 0.96154 0.95694 0.95238 0.94787 1.92742 1.91347 1.899691.886091.87267 1.859411.84632 2.85602 2.82861 2.80164 2.77509 2.74896 2.72325 2.69793 3.76197 3.71710 3.67308 3.62990 3.58753 3.54595 3.50515 4.64583 4.579714.51505 4.45182 4.38998 4.32948 4.27028 5.50813 5.41719 5.24214 5.157875 .07569 4.99553 6.349396.23028 6.11454 6.00205 5.89270 5.78637 5.68297 7.170147 .01969 6.87396 6.73274 6.595896.46321 6.33457 7.970877.786117.607697.435337.268797.10782 6.95220 8.75206 8.53020 8.31661 8.11090 7.91272 7.72173 7.53763 9.51421 9.25262 9 .00155 8.76048 8.52892 8.30641 8 .09254 10.25776 9.95400 9.663339.385079.11858 8.863258.61852 10.98319 10.63496 10.30274 9.98565 9.68285 9.39357 9.11708 11.69091 11.29607 10.92052 10.56312 10.222839.89864 9.58965 12.38138 11.93794 11.51741 11.11839 10.73955 10.37966 10.03758 13.05500 12.56110 12.09412 11.65230 11.23402 10.83777 10.46216 13.71220 13.16612 12.65132 12.16567 11.70719 11.27407 10.86461 14.35336 13.7535113.18968 12.65930 12.15999 11.68959 11.24607 14.97889 14.32380 13.70984 13.13394 12.59329 12.08532 11.60765 15.58916 14.87747 14.21240 13.59033 13.00794 12.46221 11.95038 16.18455 15.41502 14.69797 14.02916 13.40472 12.82115 12.27524 18.42438 17.4131516.48151 15.6220814.82821 14.09394 13.41393 20.93029 19.60044 18.39205 17.29203 16.28889 15.37245 14.53375 25.10278 23.1147721.35507 19.79277 18.40158 17.1590916.04612 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 .29498 9.71225 10.10590 10.47726 10.82760 11.15812 11.46992 11.76408 12.78336 13.76483 15.04630 13 9 16 18 20 30 5. : 225 7.00% 8.00% 9.00% 10.00 0.93458 0.92593 0.5176 0.-TES 1.80802 1.78326 1.75511 1.73554 2.62432 2.57710 2.5:1292 2585 | 3.38721 3.31213 3.23972 3.16987 | 4.10020 3.99271 2.88565 3.S079 | 4.76654 1.62288 4.46592 4.35526 5.38929 5.20637 4.26842 15.97130 5.74664 5.53422 5.33493 | 6.51523 6.2469 5.99525 5.75902 17.02358 6.71008 | 6.41766 6.14457 | 7.49867 7.13896 6.80519 6.49506 | 7.94269 7.53608 | 7.16073 5.31369 18.35765 7,90378 7.4860 7.10336 13.74547 8.24424 7.78615 7.36669 19.10791 8.55948 8.06069 7.60608 19.44665 8.85137 8.31256 7.82371 1976322 9.12164 3.54363 | 8.02155 | 10.05909 9.37189 8.75563 8.20141 | 10.33560 9.60360 15.95011 8.36452 | 10.59401 9.81815 9.12855 8.51355 | 10.83553 10.01680 5.25224 3.5486 | 11.65358 10.674789.82258 9.0770. | 12.40904 11.25778 10.27365 5.42651 | 13.3317111.92461 10.757369.7905 11.00% 12.00% 20.00% 0.00-0 0.89286 0.83333 1.71252 1.59005 1.52778 2.44371 2.40133 2.10612 3.10245 3.03735 2.58873 3.5-590 3.504782.99061 4.23054 4.11241 3.32552 4.71220 5475 3.60459 5.14612 A.967643.83716 5.53705 5.32825 | 4.03097 5. 88923 5.650224.19247 6.20652 5.93770 4.32706 6.45236 6. 19437 | 4.43922 6.74987 5.42355 4.53268 6.98 187 6.628174.61057 7.19087 6.81086 | 4.67547 7.37916 5.97399 4.72956 7.54879 7.11963 477463 7.7012 7.24967 | 1.31219 7.3399 7.36578 14.34350 7.95323 7.46944 14.46958 3.0/50/ 7.562004.39132 3.4217. 7.8.31. | 94759 3. 663753.05518 9789- 3.95105 3.2378 4.99660 The Chestnut Street Company plans to issue $825,000, 10-year bonds that pay 7 percent semiannually on March 31st and September 30th. Information relating to this bond is found below: Face Value: $ 825,000 Number of Years: 101 Stated Interest Rate: 796 Number of Payments per Year: Required: 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: How many total payments or periods will this bond pay interest? b. 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. 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. Calculate the selling price of this bond: Using the IF function, show the word "Premium" or "Discount" based on the selling price you calculated in letter f. Assume the Market Interest Rate is: 6% 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. b. 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: Using the IF function, show the word "Premium" or "Discount based on the selling price you calculated in lettere