Question

Problem 2. Consider the following series of cash flows:
	Cumulative
Month	Amount	0	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20
CF (1,000s of $)	$400.00	-700	1200	600	300	-1000	-1200	-400	-300	-1000	1200	400	300	1000	-1200	-400	-300	1000	1200	400	300	-1000
	What is the NPV if the MARR yields 15%? Compute your solution by two (2) methods as follows:
	Example.	Compute the PV month-by-month and sum those monthly results to find the NPV. I've done this for you as an example.
	A.	Read through the example provided in the "Example" tab. Explicitly state that you reviewed the example and understand what is happening.
	B.	Then, separately, use Excel's NPV function.
	C.	Now recompute the NPV at a MARR of 65%. I know that is awfully high, but see what happens. Comment about the difference.
	D.	Compute the FV at the end of month 20 at a MARR of 15%. Note — I do not know of an Excel function that performs this calculation.
Example Þ
n =	20	months
iA =	15%	% per year = MARRAnnual — This is an effective annual interest rate.
iM =	1.171%	% per month = MARRMonthly — This is an effective monthly interest rate.
Month =	Cumulative	0	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20
Undiscounted CF =	400	-700	1200	600	300	-1000	-1200	-400	-300	-1000	1200	400	300	1000	-1200	-400	-300	1000	1200	400	300	-1000
Discount (or PW) Factors =	— —	1	0.988421	0.976976	0.965663	0.954481	0.943429	0.932505	0.921707	0.911034	0.900485	0.890058	0.879752	0.869565	0.859496	0.849544	0.839707	0.829984	0.820373	0.810874	0.801484	0.792204
Discounted CFs =	— —	-700	1186.105	586.1853	289.6989	-954.481	-1132.11	-373.002	-276.512	-911.034	1080.582	356.0233	263.9256	869.5652	-1031.4	-339.818	-251.912	829.9837	984.4477	324.3495	240.4453	-792.204

Answer 1

A. Initial Investment = -700

Effective MARR Annualy iA =15% = Effective Monthly Rate im = (1 +iA)^(1/12) - 1 =1.171%

In excel Scrren Shot = (((1+B4)^(1/12))-1)

Discount for PW factors = 1/(1+im)ⁿ , n =Months, for n = 12 = 1/(1.01171)¹² = 0.869565

Discounted CFs = Undiscounted CF * Discount (or PW) Factors

B. NPV in Excel = NPV( iM , Undiscounted Cash Flows from month 1 to 20) +Initial Investment

In Scree shot = NPV(B5,D7:W7)+C7 = 248.838

C. Similarly for Effective Annual MARR of 65%:

NPV = -8.147

D. NPV is negative. The project should be rejected with Effective Annual MARR of 65%

But with 15% it is Positive.

At Efeective Annual MARR of 62.37% NPV = 0.