Question

# Calendar Year 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012 2013 2014 2015...

 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.

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 )