ExcelBanter - The effects of time on value

"B~O~B" wrote:
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.