Could someone please show me how to work these out in Excel? I am having a bit of trouble with the formulas and how to get the correct answers. Cell references would be appreciated. Thanks
Home Insert Draw Page Layout Formulas Data Review View Tell me L Calibri (Body) v 11 = = Wrap Text General H PLIV H HAVD Insert X Delete v 28 O- Paste U Y A = = = + + Merge & Center S%) Conditional Format Formatting as Table Cell Styles Format v Sort & Filter Find & Select . M N N 0 P Q R S 2 points 2 points 2 points G18 fix A A B B C D D F G H J K L 1 Instructions: Complete all cells shaded green. All answers must be determined using EXCEL formulae. 2 Question 1 3 A Company defaulted on a $110,000 loan that was due on December 31, 2021. 4 The bank has agreed to allow the Company to repay the loan by making a series of equal annual payments beginning on December 31, 2022. 5 6 6 1. Using the PMT function, calculate the required annual payment if the bank's interest rate is 10% and four annual payments are to be made. 7 ($34,701.79) $ 8 9 2. Using the PMT function, calculate the required annual payment if the bank's interest rate is 8% and five annual payments are to be made. 10 ($27,550.21) ) 11 12 3. If the bank's interest rate is 10%, using the NPER function, how many annual payments of $23,700 would be required to repay the debt? 13 6.545712703 14 15 4. If three payments of $45,014 are to be made, using the IRR function, what interest rate is the bank charging the Company? 16 The Series of Cash Flows for the loan are: 17 PV 18 PMT1 (stated as a negative) 19 PMTZ (stated as a negative) 20 PMT 3 (stated as a negative) 21 22 (stote answer as a percentage) 23 Note: points will only be assigned where the answer is determine using EXCEL formulae or cell references. 25 26 27 28 29 30 1 point 1 point 1 point 1 point 2 points 24 Home Insert Draw Page Layout Formulas Data Review View Tell me L Calibri (Body) H v 12 Al = = Wrap Text General H PLIV H HAVDU Insert v X Delete v 28 O- Paste U Y A === + E Merge & Center S% Conditional Format Formatting as Table Cell Styles Format v Sort & Filter Find & Select H15 . a fix A A I J L M N 0 P R 1 3 B C D E F G H Instructions: Complete all cells shaded green. All answers must be determined using EXCEL formulae. Question 2 On January 1, 2021 a Company issued the following Bonds: Face amount $500,000 Number of years 15 Stated interest rate 8% The bonds pay interest semi-annually. 6 8 Part 1 Using the PV function and assuming the annual market interest rate is 6% determine the price of the bonds issued on January 1, 2021. 2 points Number of interest periods Semi-annual interest payment Face amount (Future Value) Market interest rate per semi-annual period Price of bonds (Present Value) 30 ($28,915.05) $500,000 3% 2 points 2 2 points 2 points (Use PV Fx function) 2 points 9 10 11 12 13 14 4 15 16 17 18 19 20 21 22 23 24 25 26 27 28 Price of bond as an absolute amount 2 points Accounting Number Format Part 2 Use the IF function to answer either 'Premium" or "Discount" to the following question: 2 points The bond in Part 1 is sold at a: a Compare the price of the bond as an absolute amount to the face amount Note: points will only be assigned where the answer is determine using EXCEL formulae or cell references. Home Insert Draw Page Layout Formulas Data Review View Tell me L Calibri (Body) v 11 = = Wrap Text General HH PLIV H HAVD Insert X Delete v 28 O- Paste ! U Y Av === + + Merge & Center $ % ) Conditional Format Formatting as Table Cell Styles Format v Sort & Filter Find & Select 121 - A K L M N 0 P Q R S 1 2 fix B B C D E E F G H 1 Instructions: Complete all cells shaded green. All answers must be determined using EXCEL formulae. Question 3 On January 1, 2021 a Company acquired machinery and issued a noninterest bearing note: Face amount $825,000 Payable in 10 years Market interest rate 8% for similar borrowings 3 5 6 7 8 Answer the following questions: 9 10 1) What are the total number of interest periods? 20 2 points 2) What is the market interest rate per semi-annual interest period? 4% 2 points 11 12 13 14 14 15 16 3) What is the face amount payable in 10 years? $825,000 2 points Accounting Number Format 4) 4) Using the VLOOKUP function, identify the present value factor for the calculation of the present value of the face amount from the sheet 'PV of $1'. . 17 2 points 5) Calculate the present value of the face amount at January 1, 2021, using the PV factor from (4) above, ) 2 points Note:points will only be assigned where the answer is determine using EXCEL formulae or cell references. 18 19 20 21 22 23 24 25 26 27 28 29 Home Insert Draw Page Layout Formulas Data Review View Tell me L Calibri (Body) v 11 ~ = = Wrap Text General H LOV H HAVDU Insert v X Delete v 28-0 O- Paste BIU Y A === + E Merge & Center $ %) Conditional Format Formatting as Table Cell Styles B! Format Sort & Filter Find & Select L17 X fix D E F G H L M N D P R S T U V W X 11 i 2 2 3 4 4 5 6 7 8 A B B C 18% 29% 1 0.9901 0.9804 2. 0.9803 0.9612 3 0.9706 0.9423 4 0.961 0.9239 5 0.9515 0.9057 6 0.9421 0.888 7 0.9327 0.8706 8 0.9235 0.8585 9 0.9143 0.836B 10 0.9053 0.8204 11 0.8963 0.8043 12 0.8875 0.7885 13 0.8787 0.773 14 0.87 0.7679 15 0.8614 0.743 16 0.8528 0.7285 17 0.7142 18 0.836 0.7002 19 0.8277 0.6864 20 0.8195 0.673 21 0.8114 0.859B 22 0.8034 0.846B 23 22 0.7954 0.8342 24 0.7876 0.6217 25 0.7798 0.6095 0.9709 0.9428 0.9151 D.BBB5 0.8626 0.8375 0.8131 0.7894 0.7664 0.7441 0.7224 0.7014 0.881 0.6611 0.6419 0.6292 0.605 0.5874 0.5703 0.5537 53 0.9815 0.9524 0.9246 0.907 0.889 0.8638 0.8548 0.8227 0.8219 0.7835 0.7903 0.7462 0.7599 0.7107 0.7307 0.6768 0.7026 0.5446 0.6756 0.6139 0.6496 0.5847 0.6246 0.5568 0.8006 0.5303 0.5775 0.5061 0.5553 0.481 0.5333 0.4581 0.5134 0.4368 0.4936 0.4155 0.4746 0.3957 0.4564 0.3769 0.4388 0.3589 0.422 0.3419 0.4057 0.3266 0.3901 0.3101 0.3751 0.2953 0.9434 0.89 0.8396 0.7921 0.7473 0.705 0.6651 0.6274 0.5919 0.5584 0.5268 0.497 0.46BB 0.4423 0.4173 0.3937 0.3714 0.3503 0.3305 0.311B 0.2942 0.2775 0.2618 0.247 0.233 1095 0.9259 0.9091 0.8573 0.8286 0.7938 0.7513 0.735 0.683 0.6806 0.6209 0.6302 0.5645 0.5835 0.5132 0.5403 0.4565 0.5003 0.4241 0.4632 0.3855 0.4289 0.3505 0.3971 0.3186 0.3677 0.2897 0.3405 0.2833 0.3152 0.2394 0.2919 0.2176 0.2703 0.1978 0.2503 0.1799 0.2317 0.1635 0.2148 0.1486 0.1987 0.1351 0.1839 0.1229 0.1703 0.1119 0.1577 0.1016 0.146 0.0923 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 12% 0.8929 0.7972 0.711B 0.6355 0.5674 0.5065 0.4524 0.4039 0.3606 0.322 0.2875 0.2567 0.2292 0.2046 0.1827 0.1631 0.1456 0.13 0.1161 0.1037 0.0926 0.0826 0.0738 0.0669 0.05BB 9 0.8444 Number Format 0.5378 0.5219 0.5067 0.4919 0.4776
