How do I complete the table for every 5 year mark using the following information?

You are the VP of Marketing for Shrewsbury Separation Systems, Inc., a $100 million company that sells filtration equipment. The equipment comprises 2 components: the membrane filters themselves (which cost $10 each to make and are sold for $50) and the systems into which the membrane filters go (which cost ~$250,000 to make and generate 0-10% GM).

There are typically 1000 membrane filters on each system. These filters last ~5 years and must then be replaced. Currently there is only 1 competitor who also makes similar membranes that would fit into the system, and he is relatively new. Your membranes are better than his.

Unfortunately, there are several established competitors who make the systems and sell them for cut-rate prices. These are typically mom-and-pop vendors with much lower overhead than your company. These competitors are making it much harder for you to sell your systems at a profit.

You do not want to give up the systems part of the business, as selling a system makes it much more likely that a customer will buy (and continue to buy) your membranes.

What should your pricing strategy be for a) systems and b) replacement membranes? (Hint: You must calculate the NPV of the membranes)

	Cost	Price (Old)	GM (Old)	Price (New)	GM (New)	Annual Interest Rate (i)
System
Membrane
Membrane (x1000)
Year, n	GM (old)	GM (new)	Discount Factor = 1/(1+i)^n	PV (old)	PV (old)	NPV (old):	NPV (new):
0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20