Question

A new project will require development costs of $150 million at time zero, $225 million at the end of year 1. Revenues are expected to start at year three at $50 million per year, escalating by 8% each of years 4 through 10, then hold constant at the 10 year rate for the rest of the project, (years 10 through 15). There is a total equipment replacement cost required at the end of year 8 for $250 million and reclamation of $115 million, both occurring at the end of year 15. Assuming a 15% minimum rate of return.

-calculate dual rate of return

-calculate npv

-using "what if" analysis in excel, what does the revenue escalation rate have to be for the project to generate at 15% rate of return?

please show excel formulas.

Answer 1

Escalation Rate	8%
Year	0	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15
Development Costs	-$150.00	-$225.00
Revenues				$50.00	$54.00	$58.32	$62.99	$68.02	$73.47	$79.34	$85.69	$85.69	$85.69	$85.69	$85.69	$85.69
Equipment Replacement cost									-$250.00
Reclamation cost																-$115.00
Total	-$150.00	-$225.00	$0.00	$50.00	$54.00	$58.32	$62.99	$68.02	-$176.53	$79.34	$85.69	$85.69	$85.69	$85.69	$85.69	-$29.31
1. Dual Rate of Return
5.75%
=IRR(B8:Q8,15%)
2. NPV
-$136.70
=NPV(15%,B8:Q8)

3. Using What-if analysis:
Data-What if analysis-Goal Seek
Set Cell:	Cell having Rate of return value
To Value:	15%
By Changing Cell:	Cell having Escalation Rate
Escalation Rate	19.22%
Year	0	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15
Development Costs	-$150.00	-$225.00
Revenues				$50.00	$59.61	$71.06	$84.72	$101.00	$120.40	$143.54	$171.12	$171.12	$171.12	$171.12	$171.12	$171.12
Equipment Replacement cost									-$250.00
Reclamation cost																-$115.00
Total	-$150.00	-$225.00	$0.00	$50.00	$59.61	$71.06	$84.72	$101.00	-$129.60	$143.54	$171.12	$171.12	$171.12	$171.12	$171.12	$56.12
Rate of Return
15%