Question

1.) Projects A and B both have 5 years of life. The initial investments and cash flows from these two projects are shown below.

Year	0	1	2	3	4	5
Project A	-12,300	4,500	4,000	3,500	3,000	2,500
Project B	-15,000	6,000	5,000	4,000	3,200	2,400

A.) Use Data Table tool to find the NPVs of both projects when the discount rate is 10%.

B.) Use Data Table tool to find the IRRs of both projects

C.) Plot the NPV profiles for both projects on the same graph.

D.) Use Goal Seek to find the crossover rate of these two projects. (Note that the solution of this part must include an image of the Goal Seek dialog showing all input values. The image can be shown in a non-Excel file.)

Please show all cells like the given picture (same format if possible). I need to know which cells to use and input. Please show the entire excel file.

Draw Page Layout Formulas Data Review View Kutools TM Kutools Plus Help Tell me what you want to do Share AutoSum - Az Commen O Calibri === D. General File Home Insert X Cut L6 Copy - Paste & Format Painter Clipboard 11 - A - A- Wrap Text 3 Merge & Center - E Insert Delete Format BI U - 9 P2 D Conditional Format as Cell Formatting Table Styles Styles ¢ , $ - %688 , 40.00 Clear Sort & Find & Filter - Select Editing Font Alignment Number Cells A1 - X for Data A B C D G H I J K L M N O P Q R S T U V W X 1 Data Year E NPV Profiles Discount Rate NPV (A) NPV (B) 1 Project A -12,300 4,500 4,000 3,500 3,000 2,500 Project B -15,000 6,000 5,000 4,000 3,200 2,400 0% 1% 2% NPV Profiles of Projects A and B 10 Cost of Captia 10% 5% 6% 11 12 NPV 13 IRR 50.60 $0.40 $0.20 9% 10% 11% 12% 13% 14% 15% 16% 17% 18% 19% 20% $0.00 096 596 10% 1595 20% Discount Rate — NPV (A) - NPV (8) Crossover Rate NPV NPV difference Sheet1 Ready @ J -— H

Answer 1

	A	B	A - B
	-12,300	-15,000	-2,700
	4,500	6,000	1,500
	4,000	5,000	1,000
	3,500	4,000	500
	3,000	3,200	200
	2,500	2,400	-100
NPV	$1,327.64	$1,267.89
IRR	14.61%	13.84%	8.53%

NPV can be calculated using the NPV function in excel. NPV = NPV(rate, CF1...CF5) + CF0

IRR can be calculated using the IRR function in excel. IRR = IRR(CF0...CF5)

Crossover rate is the rate at which NPV of both projects are equal, which is the IRR of the difference in the cash flows. Hence, the crossover rate is 8.53%

NPV Profile
Rate	NPV (A)	NPV(B)
0%	$ 5,200.00	$   5,600.00
1%	$ 4,735.30	$   5,083.09
2%	$ 4,290.43	$   4,587.55
3%	$ 3,864.29	$   4,112.21
4%	$ 3,455.87	$   3,656.00
5%	$ 3,064.19	$   3,217.89
6%	$ 2,688.36	$   2,796.96
7%	$ 2,327.56	$   2,392.29
8%	$ 1,980.98	$   2,003.07
9%	$ 1,647.91	$   1,628.52
10%	$ 1,327.64	$   1,267.89
11%	$ 1,019.53	$      920.51
12%	$    722.98	$      585.72
13%	$    437.42	$      262.91
14%	$    162.30	-$       48.48
15%	-$   102.87	-$     348.99
16%	-$   358.58	-$     639.14
17%	-$   605.27	-$     919.40
18%	-$   843.36	-$ 1,190.22
19%	-$1,073.24	-$ 1,452.04
20%	-$1,295.31	-$ 1,705.25