Calculating NPV at Acron

Acron is a large drug company. At the current time, the beginning of year 0, Acron is trying to decide whether one of its new drugs, Niagra, is worth pursuing. Niagra is in the final stages of development and will be ready to enter the market one year from now.

The final cost of development, to be incurred at the beginning of year 1, is $15 million. Acron estimates that the demand for Niagra will gradually grow and then decline over its useful lifetime of 20 years. Specifically, the company exprects its gross margin (revenue minus cost) to be $1.5 million in year 1, then to increase at an annual rate of 6% through year 8, and finally to decrease at an annual rate of 5% through year 20.

Acron wants to develop a spreadsheet model of its 20-year cash flows, assuming its cash flows, other than the initial development cost, are incurred at the end of the respective years. Using an annual discount rate of 7.5% for the purpose of calculating NPV, the drug company wants to answer the following questions:

Inputs
Development cost (millions)	15
Gross margin year 1 (millions)	1.5
Peak year for gross margin	8
Rate of increase (through peak year)	6.0%
Rate of decrease (after peak year)	5.0%
Discount rate	7.5%
Cash flows
End of year	Gross margin
1	1.50
2	1.59
3	1.69
4	1.79
5	1.89
6	2.01
7	2.13
8	2.26
9	2.14
10	2.04
11	1.93
12	1.84
13	1.75
14	1.66
15	1.58
16	1.50
17	1.42
18	1.35
19	1.28
20	1.22
NPV	$3.06
Sensitivity to peak year (peak sales)
	$3.06
3	-1.822126561
4	-0.774235853
5	0.240092187
6	1.219132713
7	2.160931003
8	3.063276261
9	3.923672382
10	4.739305323

Question 1. Which has a greater present value: the gross margin for year 1 or the gross margin for year 8 (the peak year for gross margin)?

Question 2. What is the present value of the gross margin at year 16?

Question 3. What rate of increase is needed from year 1 through year 8 to have a year 8 gross margin with a present value that is equal to the present value of the year 1 gross margin? Hint: Use goal seek.