I have a spreadsheet that includes a column for year, a column for amount
deposited in a 401(k), a column for growth amount, etc. The last column is
the ending balance for that year. This spreadsheet is used to project the
balance in an account for the future. The problem is that it shows the
actual expected balance in the account for each year. Twenty years from
now, these numbers look quite impressive, but when I take $1,000 and add 3%
to it for twenty years, I find that the numbers are not very large in
today's dollars.

My spreadsheet also has a field where I entered the "assumed rate of
inflation". I currently have 3% in that field.

Is there a way to perform a calculation using the balance, then year, and
the rate of inflation that will show me the balance in "today's dollars"?

Or, even better, if I know the number of years from now (year minus current
year), the amount, and the inflation rate, can I calculate a number in
today's dollars? This would allow me to plug in those three variables and
see what a dollar value is worth.

Amount to evaluate: $2,000,000.00
Number of years from now: 20
Rate of annual inflation: 3.00%
Value in current dollars: _________ ?

The answer should be right around one million dollars. A million dollars
that increases 3% a year for the next twenty years would be 2.5 million at
that time. I can't figure out how to back into that though.


It seems like this should be relatively easy, but searching the previous
posts did not lead me to the answer I needed. Using Excel help pointed me
to the "NPV" function, but I don't think that is exactly what I want here.

Thanks for your help!

--
Rick B