Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / New Users / December 2005

Tip: Looking for answers? Try searching our database.

yearly investment growth with 2 different variables

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Gretchen_Ross - 28 Dec 2005 19:14 GMT
I am attempting to create a template file that I will use for
illustration purposes.

I need help creating a formula that will calculate the investment
growth of a monthly deposit that compounds annually. Secondly I need
create a second column that will calculate the investment growth with a
yearly bonus added to it. This will illustrate investment returns inside
an RRSP, which is a Canadian government sponsered investment program.

Column G & H on sheet #2 are the keys to this project. Column G should
show straight investment growth while column H should do the same but
with the value from column  D added to it.

I would like to use 7.25% as my illustrated interest rate, but that is
not all that importan because even I, with my limited skills can modify
that in the future.

Thanks for any help

Ryan

+-------------------------------------------------------------------+
|Filename: Illustration One.zip                                     |
|Download: http://www.excelforum.com/attachment.php?postid=4157     |
+-------------------------------------------------------------------+

Signature

Gretchen_Ross

Don Guillett - 28 Dec 2005 20:33 GMT
Sounds more like a project instead of a question.
Projects are what many of us do for a living.

Signature

Don Guillett
SalesAid Software
dguillett1@austin.rr.com

>
> I am attempting to create a template file that I will use for
[quoted text clipped - 22 lines]
> |Download: http://www.excelforum.com/attachment.php?postid=4157     |
> +-------------------------------------------------------------------+
Gretchen_Ross - 28 Dec 2005 22:46 GMT
If the required formula is so advanced as to require that I pay someon
for the info. I am sorry that I posted it here.

I have many other ways of going about this
joeu2004@hotmail.com - 29 Dec 2005 23:09 GMT
[Looks like my previous response got lost.  This is a repost.]

> If the required formula is so advanced as to require that I
> pay someone for the info. I am sorry that I posted it here.

I don't think so.  But I am skeptical of my interpretation of
your request.

You wrote previously:
> I need help creating a formula that will calculate the
> investment growth of a monthly deposit that compounds
> annually.

That seems unusual, which makes me skeptical of my
interpretation.  Usually, monthly "deposits" would compound
at least monthly, not annually.  Do you really mean that you
make monthly investments (which compound monthly), but
you only have the annual growth rate?

> Secondly I need create a second column that will calculate
> the investment growth with a yearly bonus added to it.
> This will illustrate investment returns inside an RRSP, which
> is a Canadian government sponsered investment program.

I am not familiar with Canadian RRSPs, other than what I
read on the internet (not much!).  But I wonder:  are you
really asking for a formula to compute the tax deduction
("bonus") based on the contribution, given an tax rate?

> Column G & H on sheet #2 are the keys to this project.
> Column G should show straight investment growth while
> column H should do the same but with the value from
> column  D added to it.

The computation of H seems very basic.  Nothing wrong
with a basic question.  But again, I am skeptical of my
interpretation because of the basic nature of the question.

> I would like to use 7.25% as my illustrated interest rate

If A1 is the rate of return (7.25%), B1 is the number of
years for the investment, C1 is the monthly contribution,
and D1 is the annual "bonus", as you wrote, then:

G1:  =FV(A1, B1, -12*C1, 0)
H1:  =G1 + D1

If that is missing the point, please clarify your requirement.
Gretchen_Ross - 30 Dec 2005 05:13 GMT
I incorrectly posted that the investment should compound annually. I
should of coarse compound monthly.

Regarding the tax deduction, I am assuming with this example that th
rate at which taxes are credit is 36.9% as in column D
joeu2004@hotmail.com - 30 Dec 2005 09:03 GMT
> I incorrectly posted that the investment should compound
> annually. It should of coarse compound monthly.
> Regarding the tax deduction, I am assuming with this
> example that the rate at which taxes are credit is 36.9%
> as in column D.

That changes things significantly.  But please bear in mind
that the solution is still subject to interpretation.  I have only
a remedial understanding of Canadian RRSPs and what you
are trying to calculate.

You wrote previously:
> Column G should show straight investment growth while
> column H should do the same but with the value from
> column D added to it.
> I would like to use 7.25% as my illustrated interest rate

If A1 is the annual rate of return (7.25%), B1 is the number
of months (not years) for the investment, C1 is the monthly
contribution, and D1 is the tax credit rate (36.9%) for the
contribution:

G1:  =FV(A1/12, B1, -C1,, 1)
H1:  =G1 + B1*C1*D1

Of course, if you want B1 to be in years (as I wrote previously),
replace "B1" with "12*B1" in both instances.

Does that meet your needs?
Gretchen_Ross - 30 Dec 2005 17:28 GMT
The formulas you provided in that last post are the perfect start.

Thanks

Just to give you a better explanation of what I am attempting to do
here; what I want to find out is how much better off I will be in terms
of dollar value of investment when my "tax rebate" is always directed
back into the investment.

Tax law is irrelevant because I am making the assumption that I will
always be in the same tax bracket. Which is not true but those
calculations/variables can wait. In this example I will always have a
tax credit of 36.9% of the contribution. I simply want to compare the
different values that column G and H will present.

Now for a further question. How do I deal with the fact that I my
contribution amount differs from year to year. (ie - a 1% yearly
increase in monthly contribution for years 2 - 10?

What you have provided me with so far brings me a lot closer to my goal
and for that I thank you.

Ryan

Signature

Gretchen_Ross

joeu2004@hotmail.com - 31 Dec 2005 04:21 GMT
> Just to give you a better explanation of what I am
> attempting to do here; what I want to find out is
> how much better off I will be in terms of dollar
> value of investment when my "tax rebate" is always
> directed back into the investment.

Oh!  In that case, the formula for H should change.
Effectively, you are making an additional contribution
once a year in the amount of the tax refund, which then
compounds monthly.  At this point, it might be easier
to make the simplifying assumption that the investment
period, B1, is in years (again), not months.  Sorry
for the flip-flop.

Thus, if A1 is the nominal annual rate of return, B1
is the number of years of the investment, C1 is the
monthly contribution, and D1 is the tax rate, then:

G1:  =FV(A1/12, 12*B1, -C1,, 1)

H1:  =G1 + FV(EFFECT(A1,12), B1, -12*C1*D1)

EFFECT() gives the effective rate at which an annual
contribution compounds monthly.  For example, 7.50%
if the annual rate is 7.25%.

If EFFECT() does not work for you, you need to load
the Analysis ToolPak add-in.  See footnote [1] below
for alternatives.

> Tax law is irrelevant because I am making the assumption
> that I will always be in the same tax bracket. Which
> is not true but those calculations/variables can wait.

Granted.  But this brings to mind a curiosity that I
wanted to ask about RRSPs.  I think you answered it
implicitly; but I want to be sure.  Is the profit earned
within an RRSP "tax deferred" (taxed later) or truly
"tax free" (never taxed!)?

I presume it is tax deferred, like the US IRAs; that is,
I assume you will be taxed on the profit when you withdraw
it.  But the "best" web site I find that explains RRSPs
(to a minimal degree) uses the term "tax free" -- which
would mean that the profit is never taxed(!).  This is
a common terminology mistake (or misleading statement)
that US brokers often make, too.

> Now for a further question. How do I deal with the fact
> that I my contribution amount differs from year to year.
> (ie - a 1% yearly increase in monthly contribution for
> years 2 - 10?

I do not know of any Excel function that would facilitate
this.  It might exist -- perhaps some convoluted use of
SUMPRODUCT, a favorite of many respondents in these
newsgroups.  I simply am not aware of it.

I would create a row for each investment year, with
columns similar (but not exactly the same) as above --
although you could simplify it by putting some "constant"
values in other cells.  Here is how I set things up:

A1:A10 = annual return rate (7.25%)

B1:B10 = remaining number of years for investment; for
             example: B1=10, B2=9, ..., B10=1

C1:C10 = monthly contribution; for example: C1=1000,
              C2 =ROUNDDOWN(C1*(1+1%), 0), copy C2 to C3:C10

D1:D10 = tax (credit) rate (36.9%)

E1:E10 = year-end value of the year's contributions,
             compounded monthly; for example:
             E1 =FV(A1/12, 12, -C1,, 1), copy E1 to E2:E10

F1:F10 = the year's tax credit/refund due to RRSP
             contributions; for example:
             F1 =12*C1*D1, copy F1 to F2:F10

G1:G10 = future value (FV) of E1:E10 respectively,
              compounded monthly for the remaining years;
              for example:
              G1 =FV(EFFECT(A1,12), B1-1,, -E1),
              copy G1 to G2:G10

H1:H10 = G1:G10 plus FV of reinvested F1:F10
              respectively, compounded monthly for the
              remaining years; for example:
              H1 =G1 + FV(EFFECT(A1,12), B1-1,, -F1),
              copy H1 to H2:H10

In row 11, I compute the following sums:

C11 = total monthly contributions; for example:
         =12*SUM(C1:C10)

G11 = total FV of investment; for example:
         =SUM(G1:G10)

H11 = total FV of investment and reinvested tax
         credit; for example:  =SUM(H1:H10)

Of course, all that makes some simplifying assumptions
-- probably good enough for a "first approximation".

> What you have provided me with so far brings me
> a lot closer to my goal and for that I thank you.
> Ryan
> --
> Gretchen_Ross's Profile: [....]

You're welcome.  Now, let's talk about your transgender
issues, Ryan/Gretchen :-).

-----
[1] Footnote regarding EFFECT().

Alternatively, replace EFFECT() above with
FV(A1/12,12,,-1)-1.  I prefer the latter because it
saves me the trouble of getting Help on the function
when I forgot its parameters, since Excel provides
typing hints for intrinsic functions, but not for
ATP functions :-( , and because it takes less effort
than explaining to people why EFFECT() sometimes fails :-).
For similar reasons, I prefer to use FV() instead of
the geometric growth expression (1 + 7.25%)^12 - 1.

If that paragraph leaves you gasping for air, just
ignore it ;-).
Gretchen_Ross - 31 Dec 2005 16:30 GMT
At this stage in the game what you have just provided me with i
perfect. Thanks a lot. Now I have the ability to truly judge the wealt
building aspects of our RRSP program.

To answer your question regarding the Canadian RRSP program; yes yo
are correct, the growth will compond without any tax implications. Onc
an annuitant is 65 years old he/she is forced to move the funds int
what is called a RIF or Retirement Income Fund. The income from thi
fund is considered normal income is the years of withdrawal. Therefor
the individual would be taxed at whatever rate bracket their level o
RIF income puts them. It is an awesome program but as usual wit
anything related to finance, most people wait to long to really tak
advantage of it.:confused:

Ryan

P.S - The Gretchen_Ross username is based on a character from one of m
favorite films: Donnie Dark
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.