Question

Determine the best alternative among the options in the table below if the MARR is 6%.

Option	A	B	C
Initial cost	$2500	$4000	$5000
Annual net benefits	$410	$639	$700
n= 20 years.
NPV=
IRR=
NPV
i	A	B	C
0%
1%
2%
3%
4%
5%
6%
7%
8%
9%
10%
11%
12%
13%
14%
15%
16%
17%
18%
19%
20%
21%
22%
23%
24%
25%

a) Using Excel, construct a choice table for interest rates from 0% to 25% showing the NPV of each option for each interest rate using the NPV function. Based on the table, state the range of interest rates over which each option should be chosen.

b) Using Excel, plot the values for the three options for a rate interest ranging from 1% to 25%. This table will plot the NPV of each option as a function of interest rate. On the plot, label the internal rate of return for each option. Interpret the results of the graph.

c) In Excel, use the IRR function that you have to perform a rate of return analysis on the three options.The MARR=6%. Show the calculated values of each internal rate of return, expressed to 2 decimal places. Explain any decision to consider or reject an option.

Answer 1

Option	A	B	C
Initial Cost	2500	4000	5000
Annual net benefit	410	639	700
NPV calculation
Rate	A	B	C	Rate
0%	$5,700.00	$8,780.00	$9,000.00	0%
1%	$4,850.17	$7,456.54	$7,556.32	1%
2%	$4,121.65	$6,322.12	$6,319.61	2%
3%	$3,494.92	$5,346.32	$5,256.54	3%
4%	$2,953.88	$4,504.06	$4,339.64	4%
5%	$2,485.24	$3,774.62	$3,546.24	5%
6%	$2,077.99	$3,140.83	$2,857.50	6%
7%	$1,722.94	$2,588.39	$2,257.77	7%
8%	$1,412.44	$2,105.37	$1,733.98	8%
9%	$1,140.10	$1,681.78	$1,275.21	9%
10%	$900.51	$1,309.24	$872.27	10%
11%	$689.16	$980.69	$517.41	11%
12%	$502.21	$690.16	$204.12	12%
13%	$336.41	$432.58	($73.16)	13%
14%	$189.02	$203.67	($319.13)	14%
15%	$57.67	($0.25)	($537.80)	15%
16%	($59.63)	($182.30)	($732.60)	16%
17%	($164.63)	($345.18)	($906.46)	17%
18%	($258.79)	($491.18)	($1,061.93)	18%
19%	($343.40)	($622.31)	($1,201.17)	19%
20%	($419.56)	($740.28)	($1,326.08)	20%
21%	($488.23)	($846.59)	($1,438.28)	21%
22%	($550.24)	($942.53)	($1,539.19)	22%
23%	($606.32)	($1,029.24)	($1,630.06)	23%
24%	($657.09)	($1,107.70)	($1,711.95)	24%
25%	($703.13)	($1,178.77)	($1,785.83)	25%
Range between each opetion to be choosen
	A	B	C
	0% to 15%	0% to 14%	0% to 12%

IRR calculation
	15%	15%	13%
Since MARR is 6% we can accept all the project