Question

BU IT needs you to analyze the impact of a new project that will cause its cash flows to increase $6,000 over last year’s, and continue to grow at a constant rate of 10% per year for the foreseeable future. The discount rate is 20%. You need to analyze this in three ways.

Calculate the NPV and IRR of this new project based on an initial investment cost of $45,000 and the change in cash flows each year, assuming the growth continues forever.

Find the NPV of this project if the project only generates cash flows for 20 years.

Use an embedded function in Excel to calculate the NPV of the project if the cash flows had zero growth, and the project only generates cash flows for 40 years.

Answer 1

	A	Initial Investment	($45,000)
	B	Cash flow increase in year 1	$6,000
	C	Growth rate of increase in cash flow=10%	0.1
	D	Discount Rate=20%	0.2
	E=B/(D-C)	Present Value of future increase of cash flows in perpetuity=	$        60,000	(6000/(0.2-0.1)
	F=E+A	Net Present value (NPV)	$        15,000
		IRR =23.33%(using excel IRR function
		Year	Cash flow	IRR(using excel IRR function)
		0	($45,000)
		1	$6,000
		2	$6,600
		3	$7,260
		4	$7,986
		5	$8,785
		6	$9,663
		7	$10,629
		8	$11,692
		9	$12,862
		10	$14,148	14.03%
		11	$15,562
		12	$17,119
		13	$18,831
		14	$20,714
		15	$22,785
		16	$25,063
		17	$27,570
		18	$30,327
		19	$33,360
		20	$36,695	21.51%
		21	$40,365
		22	$44,401
		23	$48,842
		24	$53,726
		25	$59,098
		26	$65,008
		27	$71,509
		28	$78,660
		29	$86,526
		30	$95,179	22.85%
		31	$104,696
		32	$115,166
		33	$126,683
		34	$139,351
		35	$153,286
		36	$168,615
		37	$185,476
		38	$204,024
		39	$224,426
		40	$246,869	23.19%
		41	$271,556
		42	$298,711
		43	$328,582
		44	$361,440
		45	$397,584
		46	$437,343
		47	$481,077
		48	$529,185
		49	$582,103
		50	$640,314	23.29%
		51	$704,345
		52	$774,780
		53	$852,258
		54	$937,483
		55	$1,031,232
		56	$1,134,355
		57	$1,247,790
		58	$1,372,569
		59	$1,509,826
		60	$1,660,809	23.32%
		61	$1,826,890
		62	$2,009,579
		63	$2,210,537
		64	$2,431,590
		65	$2,674,749
		66	$2,942,224
		67	$3,236,447
		68	$3,560,091
		69	$3,916,101
		70	$4,307,711
		71	$4,738,482
		72	$5,212,330
		73	$5,733,563
		74	$6,306,919
		75	$6,937,611
		76	$7,631,372
		77	$8,394,509
		78	$9,233,960
		79	$10,157,356
		80	$11,173,092	23.33%
		81	$12,290,401
		82	$13,519,441
		83	$14,871,386
		84	$16,358,524
		85	$17,994,377
		86	$19,793,814
		87	$21,773,196
		88	$23,950,515
		89	$26,345,567
		90	$28,980,123
		91	$31,878,136
		92	$35,065,949
		93	$38,572,544
		94	$42,429,799
		95	$46,672,778
		96	$51,340,056
		97	$56,474,062
		98	$62,121,468
		99	$68,333,615
		100	$75,166,976	23.33%
		If the project generate cash flow for 20 years:
		PV of cash flow=(Cashflow)/((1+i)^N)
		i=discount rate=20%, N=year of cash flow
		N	A	B=A/(1.2^N)
		Year	Cash flow	PV of Cash flow
		0	($45,000)	-45000
		1	$6,000	5000
		2	$6,600	4583.333333
		3	$7,260	4201.388889
		4	$7,986	3851.273148
		5	$8,785	3530.333719
		6	$9,663	3236.139243
		7	$10,629	2966.460972
		8	$11,692	2719.255891
		9	$12,862	2492.651234
		10	$14,148	2284.930298
		11	$15,562	2094.519439
		12	$17,119	1919.976153
		13	$18,831	1759.97814
		14	$20,714	1613.313295
		15	$22,785	1478.87052
		16	$25,063	1355.63131
		17	$27,570	1242.662035
		18	$30,327	1139.106865
		19	$33,360	1044.181293
		20	$36,695	957.1661852
			Total	4471.171963
		Net Present value (NPV)	$4,471
		If there is zero growth and cash flow for 40 years
	A	Initial investment	($45,000)
	B	Annual cash flow	$6,000
	C	Present value of annual cash flow	$29,979.59	(Using PV function ofexcel with rate=20%, Nper=40,Pmt=6000
	D=A+C	Net present Value (NPV)	($15,020.41)