Which Finance Function should i use to solve this

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

patele@gmail.com - 19 Jun 2006 21:55 GMT

Work four summers, starting at age 16

Save the income in a Roth IRA account

Invest it in a simple, low-cost equity portfolio

Simmer slowly for 47 years

Serve ungarnished (and untaxed) at age 67

If your money is invested in common stocks and you achieve the average
compound annual rate on large-capitalization U.S. stocks, 10.7%, your
account will grow to $9,378 at the end of the fourth year. You will be
20 years old. Invested in the same way, with no additional savings, the
account will grow to:
$25,917 by the time you are 30

$71,625 by the time you are 40

$197,943 by the time you are 50

$547,037 by the time you are 60

And $1,114,423 by the time you are 67

is there a function that will give me this answer without using
multiple rows and columns to calculate this.

Reply to this Message

Dana DeLouis - 19 Jun 2006 22:19 GMT

> account will grow to $9,378 at the end of the fourth year.
> You will be 20 years old.
> $25,917 by the time you are 30
> $71,625 by the time you are 40

Hi. We first need to figure out what you invested at age 16. (Here in A1).

[A1] =PV(10.7%,4,,9378)
($6,244.82)

Check you figures when we are 30 (14 years later)

=FV(10.7%,14,,A1)
$25,917.10

And 40...
=FV(10.7%,24,,A1)
$71,624.67

All values seem to check...
-
HTH. :>)
Dana DeLouis
Windows XP, Office 2003

> Work four summers, starting at age 16
>
[quoted text clipped - 23 lines]
> is there a function that will give me this answer without using
> multiple rows and columns to calculate this.

Reply to this Message

Dana DeLouis - 19 Jun 2006 22:39 GMT

Or perhaps if a cell/cells have the range name "Age", then ...

=9378*POWER(1+10.7%, Age-20)
or...
=FV(10.7%,Age-20,,-9378)

Signature

HTH. :>)
Dana DeLouis
Windows XP, Office 2003

>> account will grow to $9,378 at the end of the fourth year.
>> You will be 20 years old.
[quoted text clipped - 49 lines]
>> is there a function that will give me this answer without using
>> multiple rows and columns to calculate this.

Reply to this Message

patele@gmail.com - 20 Jun 2006 15:03 GMT

Investment made at 16 was 2000

> > account will grow to $9,378 at the end of the fourth year.
> > You will be 20 years old.
[quoted text clipped - 48 lines]
> > is there a function that will give me this answer without using
> > multiple rows and columns to calculate this.

Reply to this Message

joeu2004@hotmail.com - 20 Jun 2006 15:24 GMT

pat...@gmail.com wrote:

> Investment made at 16 was 2000

Although that is not relevant to the question you asked because you
assumed that you (will) have $9378 by age 20 somehow, if you are going
into the 2nd summer of work now, you will need to invest an average of
$1728 after each of the remaining 3 summers in order to accumulate
$9378 in assets at an average growth of 10.7%. This is given by the
following formula:

=pmt(10.7%, 3, -2000*(1+10.7%), 9378, 1)

Reply to this Message

joeu2004@hotmail.com - 19 Jun 2006 22:44 GMT

> is there a function that will give me this answer without using
> multiple rows and columns to calculate this.

See examples below.

> Work four summers, starting at age 16
> Save the income in a Roth IRA account
[quoted text clipped - 5 lines]
> compound annual rate on large-capitalization U.S. stocks, 10.7%, your
> account will grow to $9,378 at the end of the fourth year.

To achieve that, you need to invest about $1807 per year, computed by:

=pmt(10.7%, 4, 0, 9378, 1)

> You will be 20 years old. Invested in the same way, with no additional
> savings, the account will grow to:
[quoted text clipped - 3 lines]
> $547,037 by the time you are 60
> And $1,114,423 by the time you are 67

If the age (30, 40, etc) is in A1, then:

=fv(10.7%, A1-20, 0, -9378)

My answers are a little different: $197,942 at 50, $574,035 at 60 and
$1,114,419 at 67. The differences could be due to round-off in some of
the input numbers.

Reply to this Message