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

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

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
news:ua$ei53FGHA.524@TK2MSFTNGP09.phx.gbl...
>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
>
>
>
>

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



"Bernard Liengme" <bliengme@stfx.TRUENORTH.ca> wrote in message
news:u3HXW%233FGHA.376@TK2MSFTNGP12.phx.gbl...
> 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
> news:ua$ei53FGHA.524@TK2MSFTNGP09.phx.gbl...
>>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
>>
>>
>>
>>
>
>

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

"Rick B" <Anonymous> wrote in message
news:eL3XpD4FGHA.3728@tk2msftngp13.phx.gbl...
> 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
>
>
>
> "Bernard Liengme" <bliengme@stfx.TRUENORTH.ca> wrote in message
> news:u3HXW%233FGHA.376@TK2MSFTNGP12.phx.gbl...
>> 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
>> news:ua$ei53FGHA.524@TK2MSFTNGP09.phx.gbl...
>>>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



"Bernard Liengme" <bliengme@stfx.TRUENORTH.ca> wrote in message
news:%23PhClQ4FGHA.1028@TK2MSFTNGP11.phx.gbl...
> 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
>
> "Rick B" <Anonymous> wrote in message
> news:eL3XpD4FGHA.3728@tk2msftngp13.phx.gbl...
>> 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
>>
>>
>>
>> "Bernard Liengme" <bliengme@stfx.TRUENORTH.ca> wrote in message
>> news:u3HXW%233FGHA.376@TK2MSFTNGP12.phx.gbl...
>>> 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
>>> news:ua$ei53FGHA.524@TK2MSFTNGP09.phx.gbl...
>>>>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
>>>>
>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>