I need help finding the NPV (I need the formula and what numbers go where in that formula) so that when using the Goal Seek I can get the Cash Flows for Year 5,6, and 7 to equal $10,742,127.

Invest cash in different installments over seven years.

Renovation costs in current dollars$14,500,000

Renovation cost inflation rate3.5%

Year Cash Flows

0 $15,000,000

1 $ 5,000,000

2 $ 5,000,000

3 $ 5,000,000

4 $ 5,000,000

5 $ 0

6 $ 0

7 $ 0

8 ($19,093,731)

9 ($19,762,012)

10 ($20,453,682)

11 ($21,169,561)

NPV

How to use Goal Seek to find the Years 5, 6, and 7 cash flows that make the NPV = 0:

Note the formulae in cells B125, B126, and B127, the years for which we need to calculate the annualcash flows.

In cell B141 below, insert a zero..

$0

Go to Data / What-If Analysis / Goal Seek, input the following values, and push ok:

Set cell:B133

To value:0

By changing:B141

The values in cells B125-127 changes to $xx,xxx,xxx and cell B133 changes to $0. In this way, Goal Seekfinds the values of the cash flows in Years 5, 6, and 7 that make NPV=$0.