Given the cash flows (semi-annually) of Projects A and B in Excel with an annual discount rate of 8%. Complete the following parts.

1. Compute NPV of the two projects in Cell B19, C19 (1points)
2. Compute effective annual rate of the two projects in Cell B20, C20 (1points)
3. Use IF function in cell C22 to determine which project to choose based on NPV (1 points)
4. Fill information in cells F5-F19, to determine at which rate the two projects have the same NPV (1 points)
5. Use Data Table to complete NPV profiles of Projects A and B in Cells B26 C56 (1 points)

AutoSave OFF AHSU- Excel 1 Home Insert Draw Page Layout Formulas Data Review View Developer Share Comments Clear 21 Show Detail Lol Connections Properties A Edit Links Reply Refresh Gcography From From New Database HTML Text Query 2 Sort Filter Grana Text to Flash Columns Fill Urgroup Subtatal Remove Data Consalidate Duplicates Voldation What if Analysis Hide Detail Advanced Ha Data Analysis Check for Updates * Office Update To keep up-to-date with security updates, fixes, and improvements, choose Check for Updates. G20 x fx =getformula(F20) A B D 1 Problem 4: Two Projects 2 Annual Discount Rate 8.00% 8 3 E F G . 1 J J L M M Crossover rate Project B - Semi-Annual Periods Project A Project B 30,000 5,000 5,500 6.000 6,500 7,000 7,500 8,800 10,100 11,400 12,700 14,000 15,300 1 2 3 3 4 5 6 6 7 8 9 10 11 12 IRR 4 Semi-Annual Periods Project A 5 0 -70,000 6 1 15,000 7 2 13,500 8 3 12,000 9 4 10,500 10 5 9,000 11 6 7,500 12 7 6,000 13 R 4,500 14 9 3,000 15 10 1,500 16 11 1.000 17 12 300 18 19 Compute NPV => 20) Comuter (EAIR) > 21 NPV and IF function in cell C22 to 22 determine which project to take 23 24 25 Data table: NPV for various discount rates 26 Discount rate NPV(A) 27 28 0.0% 0.5% 30 1.0% 31 1.5% 32 2.0% 33 2.5% 34 3.0% 35 3.5% 36 4.0%