B~O~B said:

I have a design estimate for material of $80M, over

5 year period, and the $80M included a 3% inflation

rate factor per year (for years 2 threw 5).

I have been ask to estimated the material cost over

4 years, but, I am having a problem calculating the

inflation rate cost for the 5 year, so I can obtain

my new material estimate cost.

I think you want to know: what is the cost in the first year such that when

the cost is inflated at 3% each year, the total after 5 years is $80M?

That is equivalent to asking: what regular amount must I save annually at

3% so that the total is $80M after 5 years?

The answer: =PMT(3%,5,0,-80). Note that the result is in millions because

I expressed the future value in millions (80).

Then the second of your question is: if I make the same regular investment

annually 3% for only 4 years, what will the total be?

If the PMT formula is in A1, the answer is: =FV(3%,4,-A1)

To convince yourself that those formulas are correct, set up the following

as a check.

B1: =A1

B2: =B1*(1+3%)

Copy B2 into B3:B5

=SUM(B1:B5) is the cost over 5 years. =SUM(B1:B4) is the cost over 4 years.

Compare the latter with the result of the FV formula.