yearly investment growth with 2 different variables

[Gretchen_Ross]

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 |
+-------------------------------------------------------------------+

 

[Don Guillett]

Sounds more like a project instead of a question.
Projects are what many of us do for a living.

--
Don Guillett
SalesAid Software
(e-mail address removed)
"Gretchen_Ross" <[email protected]>
wrote in message
news:[email protected]...

 

[Gretchen_Ross]

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

 

[Guest]

[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]

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

 

[Guest]

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]

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

 

[Guest]

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

D110 = 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

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]

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.



Ryan


P.S - The Gretchen_Ross username is based on a character from one of m
favorite films: Donnie Dark