Calculate the project NPV using the XNPV formula

Reference no: EM131964452

Natural Resource and Energy Economics Problem

Note: This problem set was significantly shortened due to the cancelled classes. You should have the knowledge to complete both of these problems.

Please do your write-up in Word, pasting relevant Excel tables or graphs into your document. Do not turn in Excel worksheets. Make your HW look as professional as possible.

1. Suppose the inverse demand function for a depletable resource is given by P = 10 - Q and the marginal cost of supplying it is $2.00. Assume the discount rate is 10%.

a) If 10 units are to be allocated between two periods, in a dynamic efficient allocation, how much would be allocated in each period?

b) What would the efficient price be in the two periods?

c) What would the marginal user cost be in each period?

d) If there were 20 units available, what would the marginal user cost be in each period?

2. The goal of this problem is to develop a more complex cash flow model using Excel. Your goal is to create an Excel sheet that gives you the same answers as the attached.

Assume initially:

Costs of $10,000 in year 1, growing at 3% per year.
Income (revenue) of $9000 in year 1, increasing at 6% each year
10-year project, with payments due on January 31 of each year
Discount rate = 10%

a) Calculate the project's NPV using the XNPV formula (calculates NPV that includes dates of actual costs and income).

b) Calculate the project's IRR using the XIRR formula (uses dates as above)

c) Create a 1-way data table varying discount rate

d) Create a 2-way data table varying cost growth rates on vertical axis and revenue growth rates on the horizontal axes. Use conditional formatting to cases in which NPV>0. Explain what this table shows you about the financial viability of this project. Be specific.

e) Using the goal seeking function (under What-if Analysis), find the discount rate at which the NPV equals zero.

f) Prepare a 2-way data table that calculates the IRR, varying costs and revenues. Use conditional formatting to highlight IRR's greater than 20%.

g) Using your original assumptions above, how does the NPV and IRR change if you assume all payments and costs happen on December 1 of each year? Why did they change?

Your basic model should look something like this. You might want to use color coding to highlight assumptions.

Attachment:- Assignment File.rar

Reference no: EM131964452

Questions Cloud

What arguments most influenced your decision : What arguments most influenced your decision? How would you explain your position to someone who disagrees with you

What price will cc choose as a function of your price : If you build a large plant, what price will you choose for your cement (and why)? What price will CC choose as a function of your price (and why)?

How many years will it take to reach goal : You have $64,633.35 in a brokerage account, How many years will it take to reach your goal?

At what amount should fly-bynight record the lease liability : Generally accepted accounting principles require this lease to be recorded as a liability for the present value of scheduled payments.

Calculate the project NPV using the XNPV formula : Econ 348: Natural Resource and Energy Economics Problem. Calculate the project's NPV using the XNPV formula (calculates NPV that includes dates of actual costs)

Which bank should you choose in given case : Your current bank is paying a 6.25% annual simple interest rate with monthly compounding. You can move your money to Harris Bank that pays 6.25% annual compound

How did royal government expand in power : What changed in the rural economy between ca. 950 and ca. 1300? How did Royal government expand in power between 1150 and 1300?

Analyze key elements of training and development : Analyze key elements of training and development geared toward improving the performance of the specific small business for which you are consulting

What is the risk tolerance level of the crusoes family : The Crusoes family currently has a saving of $1, 415,000 and is thinking about retiring in 4 years. The family right now has an after tax income of $135,000.

User Account

All Pages