"In Today's Dollars" help needed...

[Rick B]

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!

 

[Bernard Liengme]

If R is the annual percentage rate of inflation then after 1 year the Amount
will be Amount*(1+R)
Now this is the starting amount for next year, so after two years we have
[Amount*(1+R)]*(1+R) which equals
Amount*(!+R)^2
So after N years we would have Amount*(1+R)^N
And this can be found with formula such as =A10+(1+A1)^B2
or using the FV formula

 

[Rick B]

Thanks, that helps me find the value in "x" years, but I need to do the
opposite. I know how much I will have in 2023, and I know my rate of
inflation. How much is that amount (in 2023) worth today (in 2005)?

Thanks!


--
Rick B

If R is the annual percentage rate of inflation then after 1 year the
Amount will be Amount*(1+R)
Now this is the starting amount for next year, so after two years we have
[Amount*(1+R)]*(1+R) which equals
Amount*(!+R)^2
So after N years we would have Amount*(1+R)^N
And this can be found with formula such as =A10+(1+A1)^B2
or using the FV formula
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

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!

 

[Bernard Liengme]

My formula could be written as FutureValue = PresentValue(1+rate)^years
So if we rearrange this we get PresentValue=FutureValue/(1+rate)*years
Or use the PV function with =PV(rate, years, 0, -amount)
The zero takes account that no payments are made each year, the neg sign
because Excel's PV is for loans so money is borrowed or paid back (not
applicable here so the - overrides)
best wishes

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

Thanks, that helps me find the value in "x" years, but I need to do the
opposite. I know how much I will have in 2023, and I know my rate of
inflation. How much is that amount (in 2023) worth today (in 2005)?

Thanks!


--
Rick B

If R is the annual percentage rate of inflation then after 1 year the
Amount will be Amount*(1+R)
Now this is the starting amount for next year, so after two years we have
[Amount*(1+R)]*(1+R) which equals
Amount*(!+R)^2
So after N years we would have Amount*(1+R)^N
And this can be found with formula such as =A10+(1+A1)^B2
or using the FV formula
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

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]

Bernard:

Worked like a charm!!! Thanks!

--
Rick B

My formula could be written as FutureValue = PresentValue(1+rate)^years
So if we rearrange this we get PresentValue=FutureValue/(1+rate)*years
Or use the PV function with =PV(rate, years, 0, -amount)
The zero takes account that no payments are made each year, the neg sign
because Excel's PV is for loans so money is borrowed or paid back (not
applicable here so the - overrides)
best wishes

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

Thanks, that helps me find the value in "x" years, but I need to do the
opposite. I know how much I will have in 2023, and I know my rate of
inflation. How much is that amount (in 2023) worth today (in 2005)?

Thanks!


--
Rick B

If R is the annual percentage rate of inflation then after 1 year the
Amount will be Amount*(1+R)
Now this is the starting amount for next year, so after two years we
have [Amount*(1+R)]*(1+R) which equals
Amount*(!+R)^2
So after N years we would have Amount*(1+R)^N
And this can be found with formula such as =A10+(1+A1)^B2
or using the FV formula
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Rick B" <Anonymous> wrote in message
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!