Question

Calendar Year	2002	2003	2004	2005	2006	2007	2008	2009	2010	2011	2012	2013	2014	2015	2016	2017	2018	2019	2020	2021
Project Year	0	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19
Cash Flow from Investing
Signature bonus
Exploration & Predevelopment	55.00	45.00
Upstream Development	30.00	85.00	90.00	20.00	15.00	5.00	2.00	2.00	2.00	2.00	2.00	2.00	2.00	2.00	2.00	2.00	2.00	2.00	2.00	2.00
Pipeline/FSO/Export	10.00	75.00	75.00	55.00	10.00	2.00	-	-	-	-	-	-	-	-	-	-	-	-	-	-
Total Investment
Cash Flow from Operations
Production (millions of bbls)				0.57	6.10	9.42	12.40	10.80	9.49	8.30	7.26	6.36	5.56	4.86	4.26	3.70	3.26	2.85	2.49	2.00
Oil Price ($/bbl)	20.00	20.00	20.00	20.00	20.00	20.00	20.00	20.00	20.00	20.00	20.00	20.00	20.00	20.00	20.00	20.00	20.00	20.00	20.00	20.00
Gross Revenue (millions of $)
Operating Cost ($/bbl)				6.20	2.60	2.40	2.30	2.36	2.40	2.46	2.54	2.64	2.72	2.82	2.94	3.08	3.24	3.40	3.60	3.40
Operating Expense (millions of $)
Net Operating Earnings
Net (Free) Cash Flow
NPV (millions$, discount rate: 16.2%)
IRR
this problem was given in excel. if possible please mention me the excel formulae so that i can do in excel.

Answer 1

Total Investment = signature bonus + exploration and pre development + upstream development + pipeline/fso/export

Net operating earning = (oil price - operating cost) * production * 1000000 ( Production is in Million)

Net (Free) Cash Flow = Net operating income - total investment

It will be negative for first three year

NPV = Net (free) cash flow/(1+ discount rate^Project Year)

IRR = there is direct formula for irr in excel ( type = irr and in bracket put all NPV values )