Question

Cashflow	Payback Amount	Payback Year	Present Value of cashflows using Discount rate of 12%	13%	13.25%	14%	15%	16%
-650,000,000.00		0	-650,000,000.00	-650,000,000.00	-650,000,000.00	-650,000,000.00	-650,000,000.00	-650,000,000.00
80,000,000.00	80,000,000.00	1	$71,428,571.43	$70,796,460.18	$76,638,005.83	$70,175,438.60	$69,565,217.39	$68,965,517.24
121,000,000.00	121,000,000.00	1	$96,460,459.18	$94,760,748.69	$94,337,042.51	$93,105,570.94	$91,493,383.74	$89,922,711.06
162,000,000.00	162,000,000.00	1	$115,308,400.15	$112,274,126.29	111,521,947.37	$109,345,385.62	$106,517,629.65	$103,786,543.11
221,000,000.00	221,000,000.00	1	$140,449,495.33	$135,543,438.82	134,334,030.95	$130,849,741.30	$126,357,467.28	$122,056,332.63
210,000,000.00	138,000,000.00	0.66	$119,159,639.70	$113,979,586.56	112,709,756.39	$109,067,419.52	$104,407,114.41	$99,983,733.24
154,000,000.00			$78,021,192.66	$73,969,053.22	72,981,263.94	$70,160,328.34	$66,578,449.77	$63,208,107.22
108,000,000.00			$48,853,715.26	$45,906,549.52	45,192,134.93	$43,160,830.83	$40,601,200.31	$38,213,589.23
86,000,000.00			$34,733,957.61	$32,349,748.11	31,775,032.06	$30,148,078.72	$28,113,552.55	$26,232,189.28
-72,000,000.00			($25,963,921.80)	($23,967,708.00)	-23,489,214.07	($22,140,571.89)	($20,466,893.67)	($18,932,614.56)
	722,000,000.00	4.66	out of this spead sheet what is the formula to find the npv? I tried and im not finding the correct way to calculate to get the correct answer for NPV so I need to know what do I put together to find the NPV?

Answer 1

NPV = PV of cash inflows - PV of cash outflows

While calculating NPV using spreadsheet just sum up each year present values.

Year	0	1	2	3	4	5	6	7	8	9
Required Return	12%	12%	12%	12%	12%	12%	12%	12%	12%	12%
Cash flows	(650,000,000)	80,000,000	121,000,000	162,000,000	221,000,000	210,000,000	154,000,000	108,000,000	86,000,000	(72,000,000)
Present Value	$650,000,000.00	($71,428,571.43)	($96,460,459.18)	($115,308,400.15)	($140,449,495.33)	($119,159,639.70)	($78,021,192.66)	($48,853,715.26)	($34,733,957.61)	$25,963,921.80	($28,451,509.51)=sum(B6:K6)

Here ($28,451,509.51) is calculated using function in spread sheet. Here in my excel it is =sum(B6:K6)

The figure is within bracket. But don't be under the impression that it is negative. It is shown within bracket because PV of cash inflows are greater than PV of cash outflows. Here NPV is positive, project is acceptable.