NPV Excel spreadsheet - The Jones Family Case

Jones model with spinners

	The Jones Family , Incorporated - Wildcat Well NPV Analysis																			REFERENCE TABLE for Dynamic Chart
	INPUT PARAMETERS																			discount rate	end-of-year npv
	Risk free rate	6.0%		60																	$2,213,087
	Market risk premium	7.0%		70																0	9,771,181
	Beta	0.80		80																1%	8,720,867
	Barrels/day	300		30																2%	7,777,398
	Probability of dry hole	30%		30																3%	6,927,844
	Inflation/yr	2.5%		25																4%	6,161,029
	Decline in production/yr	5.0%		5																5%	5,467,270
	Long-term borrowing rate	7.0%		70																6%	4,838,160
	Investment	$ 5,000,000		50																7%	4,266,384
	Price/barrel	$ 25.00		250																8%	3,745,565
	Shipping cost/barrel	$ 10.00		100																9%	3,270,131
																				10%	2,835,203
	CALCULATON OF NPV																			11%	2,436,503
	PV of REVENUES	$17,174,017	discounted at Cost of Capital			11.6%	1													12%	2,070,268
	minus PV of FIXED COSTS	$6,869,607	discounted at Cost of Capital			11.6%	1													13%	1,733,185
	= PV of net annual cash flows	$10,304,410																		14%	1,422,330
	X probability of oil	70%																		15%	1,135,120
	= weighted average PV of net annual cash flows	$7,213,087																		16%	869,264
	minus Investment	$5,000,000																		17%	622,731
	= NPV of Widlcat Well	$2,213,087	With END-of-year discounting convention																	18%	393,714
																				19%	180,604
	= NPV of Widlcat Well	$2,619,970	With MID-year discounting convention																	20%	(18,034)
																				21%	(203,484)
																				22%	(376,893)
	CALCULATON OF CASH FLOWS																			23%	(539,293)
																				24%	(691,608)
		0	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15			25%	(834,673)
	Cost of capital	11.6%																		26%	(969,237)
	Barrels/year	Barrels/day X 365	109,500	104,025	98,824	93,883	89,188	84,729	80,493	76,468	72,645	69,012	65,562	62,284	59,169	56,211	53,400			27%	(1,095,978)
																				28%	(1,215,509)
	Price/barrel	$ 25.00	25.63	26.27	26.92	27.60	28.29	28.99	29.72	30.46	31.22	32.00	32.80	33.62	34.46	35.32	36.21			29%	(1,328,386)
	REVENUE		2,805,937	2,732,282	2,660,559	2,590,720	2,522,713	2,456,492	2,392,009	2,329,219	2,268,077	2,208,540	2,150,566	2,094,113	2,039,143	1,985,615	1,933,493			30%	(1,435,111)
																				31%	(1,536,141)
	Shipping cost/barrel	$ 10.00	10.25	10.51	10.77	11.04	11.31	11.60	11.89	12.18	12.49	12.80	13.12	13.45	13.79	14.13	14.48			32%	(1,631,893)
	FIXED COSTS		1,122,375	1,092,913	1,064,224	1,036,288	1,009,085	982,597	956,804	931,688	907,231	883,416	860,226	837,645	815,657	794,246	773,397			33%	(1,722,744)
																				34%	(1,809,042)
	CASH FLOW/YEAR	(check figure)	1,683,562	1,639,369	1,596,336	1,554,432	1,513,628	1,473,895	1,435,205	1,397,531	1,360,846	1,325,124	1,290,339	1,256,468	1,223,486	1,191,369	1,160,096			35%	(1,891,101)
																				36%	(1,969,211)
																				37%	(2,043,636)
																				38%	(2,114,618)
																				39%	(2,182,381)
																				40%	(2,247,130)
																				41%	(2,309,053)
																				42%	(2,368,325)
																				43%	(2,425,106)
																				44%	(2,479,544)
																				45%	(2,531,777)
																				46%	(2,581,933)
																				47%	(2,630,128)
																				48%	(2,676,474)
																				49%	(2,721,070)
																				50%	(2,764,013)

Jones model with spinners

"Dynamic Chart" shows real-time reaction of NPV to changes in any INPUT PARAMETER (except, of course, those affecting discount rate since discount rate is the X-axis) For example, to see the influence of increasing the probability of a dry hole, click repeatedly on the UP arrow for "Probabiltiy of dry hole" and watch what happens to the Dynamic Chart.