Investing,Mutual Funds Formula?

[Crackles McFarly]

I was needing a formula in EXCEL to calculate the future value based
on initial investment, number of years AND regular investments of $XXX
per month during the years.


I've tried several formulas, they give neg numbers or results that do
NOT seem the same as online calulators, off by as much as 5%!

thanks for help.

 

[krcowen]

Crackles

The FV function should work and match any correct on line calculator.
The sign depends on whether you are considering the annuity as an
investment or an outflow of money. In either case the absolute value
should be okay. You did not mention and interest rate, but, usually
that is a factor, and is the first argument in the Excel FV formula.
You need to make sure the periodicity of the interest matches the
periodicity of the payments. That is if you are making monthly
payments and are using an annual percentage rate, you need to divide
the rate by 12. The second argument is the number of periods, often
months or years. The third argument is the regulear investment
amount. The fourth argument is the present value, which should
correspond to your initial investment. There is a fifth argument that
indicates whether the first, and subsequent, payments are at the
beginning or the end of the period. That could cause a minor
difference if you assume end of period and the on line calculator uses
the beginning, but, unless it is a high interest rate and very few
periods, it won't amount to 5%. So, the formula

=FV(0,12,100,0,0) would return -$1,200 since you would be investing
(or paying) $100 per period for 12 periods and have no interest, no
initial investment, and since there is no interest it would not matter
whether you paid at the beginning or end of the period. If you don't
like the fact that it returns a negative $1,200 you can make the 100 a
-100 or put a negative sign in front of the FV.

Good luck.

Ken

 

[joeu2004]

I was needing a formula in EXCEL to calculate the future value based
on initial investment, number of years AND regular investments of $XXX
per month during the years.

I've tried several formulas, they give neg numbers

The negative results might be due to the fact that the formulas
require that inflow and outflow have opposite signs; and you can
choose the signs arbitrarily (i.e. negative or positive for inflow,
and the opposite sign for outflow) so that the function result is
always positive. But in this case, FV() should return a positive
value if you use the correct sign for the input parameters from the
investor's point of view, namely negative initial investment and
negative periodic investments (payments). For example, consider an
initial investment of $10,000 and monthly investments of $100, with a
return of 1% per month compounded over 5 years.

=fv(1%, 12*5, -100, -100000)

This results in the (correctly) positive value of $189,836.64
(rounded).

or results that do
NOT seem the same as online calulators, off by as much as 5%!

There can be many explanations for such differences. One that comes
to mind is differences in determining the monthly rate of return based
on an annualized rate.

If you provide the URL for one or more of the online calculators, I
might be able to provide a more specific (and perhaps more correct)
explanation.

 

[Crackles McFarly]

The negative results might be due to the fact that the formulas
require that inflow and outflow have opposite signs; and you can
choose the signs arbitrarily (i.e. negative or positive for inflow,
and the opposite sign for outflow) so that the function result is
always positive. But in this case, FV() should return a positive
value if you use the correct sign for the input parameters from the
investor's point of view, namely negative initial investment and
negative periodic investments (payments). For example, consider an
initial investment of $10,000 and monthly investments of $100, with a
return of 1% per month compounded over 5 years.

=fv(1%, 12*5, -100, -100000)

This results in the (correctly) positive value of $189,836.64
(rounded).


There can be many explanations for such differences. One that comes
to mind is differences in determining the monthly rate of return based
on an annualized rate.

If you provide the URL for one or more of the online calculators, I
might be able to provide a more specific (and perhaps more correct)
explanation.

http://www.dinkytown.net/java/CACompoundSavings.html

THAT ONE doesn't jive with Excel's results...Don't know why either..

 

[joeu2004]

http://www.dinkytown.net/java/CACompoundSavings.html
THAT ONE doesn't jive with Excel's results...Don't know why either.

You are probably entering incorrect parameters. That calculator
matches my result above (rounded to the dollar) when I enter 12% for
"rate of return", select "compound monthly" and uncheckmark "make
deposits at the start of each period".

If you describe the parameters that you are entering, I might be able
to explain any difference that you see, if the above explanation does
not.

But I should note that jurisdiction might also be a factor. Note that
that is a calculator for Canadian savings account, whereas I am
speaking from a US perspective; and your jurisdiction might be
something else altogether, for I know.

I know that Canadian __loans__ rely on "semi-annual compounding" (a
misnomer, IMHO). I don't know if Canadian __savings__ accounts do. I
suspect not, since the calculator matches my formula, as I noted above.

 

[joeu2004]

Errata....

But I should note that jurisdiction might also be a factor.
[....]
I know that Canadian __loans__ rely on "semi-annual compounding" (a
misnomer, IMHO). I don't know if Canadian __savings__ accounts do. I
suspect not, since the calculator matches my formula, as I noted above.

GIGO! Since I asked the calculator to compound monthly, of course it
did. That proves nothing about the compounding frequency for the
"typical" Canadian savings account. On the other hand, the calculator
does not offer the option to do semi-annual compounding. That might
be indicative of Canadian options; or it simply might be indicative of
the calculator's designer knowledge.

http://www.dinkytown.net/java/CACompoundSavings.html
THAT ONE doesn't jive with Excel's results...Don't know why either.

[....] That calculator matches my result above

The calculator also matches (within $1) my computation when
compounding daily, namely either of the following:

=fv((1+12%/365)^(365/12)-1, 12*5, -100, -100000)

=fv(fv(12%/365,365/12,0,-1)-1, 12*5, -100, -100000)

Result: $190,373.63 (rounded). Calculator result: $190,373.

But that does not make it right(!). Because (my US) banks calculate
interest on the balance after closing, I believe the following are
more correct.

Monthly compounding (result: $189,918.31):
=fv(12%/12, 12*5, -100, -100000, 1)

Daily compounding (result: $190,455.82):
=fv((1+12%/365)^(365/12)-1, 12*5, -100, -100000, 1)

In either case (type=0 or type=1), my computation does not match the
calculator's results for the following cases. I am still trying to
figure out why not.

Quarterly compounding (calculator result: $188,752):
=fv(12%/4, 4*5, -100*12/4, -100000, 0) [$188,672.24]
=fv(12%/4, 4*5, -100*12,4, -100000, 1) [$188,914.07]

Annual compounding (calculator result: $184,268):
=fv(12%, 5, -100*12, -100000, 0) [$183,857.59]
=fv(12%, 5, -100*12, -100000, 1) [$184,772.40]

Nonetheless, the type=0 formulas come within 0.04% to 0.22% of the
calculator's results. "Close enough for government work" ;-). (Then
again, the differences increases with the number of years for the
investment.)

HTH. Again, if you post your parameters, we might be able to provide
a better explanation of any disparity.

 

[Crackles McFarly]

You are probably entering incorrect parameters. That calculator
matches my result above (rounded to the dollar) when I enter 12% for
"rate of return", select "compound monthly" and uncheckmark "make
deposits at the start of each period".

If you describe the parameters that you are entering, I might be able
to explain any difference that you see, if the above explanation does
not.

OK, here is my example I am using

Initial investment of $500.00
With $100 per month, every month.
At a return rate of exactly 10%
For a period of exactly 25 years.

thank you

 

[joeu2004]

OK, here is my example I am using
Initial investment of $500.00
With $100 per month, every month.
At a return rate of exactly 10%
For a period of exactly 25 years.

=fv(10%/12, 12*25, -100, -500)

Results in $21,838.02. Calculator result: $21,838. Where's the
error you are talking about?

But you are talking about mutual funds, not savings. I would not
estimate the "total return" over 25 years in that manner.

 

[joeu2004]

But that does not make it right(!). Because (my US) banks calculate
interest on the balance after closing, I believe the following are
more correct.

Monthly compounding (result: $189,918.31):
=fv(12%/12, 12*5, -100, -100000, 1)

Ignore that. For a number of reasons, my comment above is not
relevant. Too complicated to explain.

 

[Crackles McFarly]

=fv(10%/12, 12*25, -100, -500)

Results in $21,838.02. Calculator result: $21,838. Where's the
error you are talking about?

A few of the online mutual fund investment calcs are different is what
I mean.

But you are talking about mutual funds, not savings. I would not
estimate the "total return" over 25 years in that manner.

I'm told 8-12% is a good range, got to settle on one so I picked 10%
as an avg rate of return in the mutual funds arena...

 

[Crackles McFarly]

=fv(10%/12, 12*25, -100, -500)

Results in $21,838.02. Calculator result: $21,838. Where's the
error you are talking about?

In Excel with that formula I get $138,712
What is up?

Yours Truly,
Crackles R. McFarly
It's a silly website but aren't they all?
http://cracklesmcfarly.blogspot.com/

 

[Harlan Grove]

OK, here is my example I am using

Initial investment of $500.00
With $100 per month, every month.
At a return rate of exactly 10%
For a period of exactly 25 years.

Is your 10% return an effective annual rate or nominal compounded monthly?
Are your additional investments at the beginning or end of each month? I'll
assume at the end of every month.

First assuming 10% nominal compounded monthly.

FV: =FV(10%/12,300,-100,-500) returns 138,711.81.

Next assuming 10% effective annual.

FV: =FV(NOMINAL(10%,12)/12,300,-100,-500) returns 128,749.84.

 

[Crackles McFarly]

Is your 10% return an effective annual rate or nominal compounded monthly?
Are your additional investments at the beginning or end of each month? I'll
assume at the end of every month.

First assuming 10% nominal compounded monthly.

FV: =FV(10%/12,300,-100,-500) returns 138,711.81.

Next assuming 10% effective annual.

FV: =FV(NOMINAL(10%,12)/12,300,-100,-500) returns 128,749.84.

That 2nd formula gives an error of "#NAME?"

And I am assuming a continual interest rate while placing exactly $100
in at the start of the month [or end if it makes this easier?]

 

[Peo Sjoblom]

Lookup NOMINAL in help, it's part of the ATP (Analysis ToolPak), it comes
with Excel/Office. Keep the CD handy and do tools>add-ins and select
Analysis Toolpak and follow the instructions


--
Regards,

Peo Sjoblom

Is your 10% return an effective annual rate or nominal compounded monthly?
Are your additional investments at the beginning or end of each month?
I'll
assume at the end of every month.

First assuming 10% nominal compounded monthly.

FV: =FV(10%/12,300,-100,-500) returns 138,711.81.

Next assuming 10% effective annual.

FV: =FV(NOMINAL(10%,12)/12,300,-100,-500) returns 128,749.84.

That 2nd formula gives an error of "#NAME?"

And I am assuming a continual interest rate while placing exactly $100
in at the start of the month [or end if it makes this easier?]

 

[joeu2004]

A few of the online mutual fund investment calcs are different is what
I mean.

When I asked you to provide a URL for an online calculator, I meant
the URL of an online calculator that was giving different results,
"off by as much as 5%" you wrote.

I'm told 8-12% is a good range, got to settle on one so I picked 10%
as an avg rate of return in the mutual funds arena.

I was not offering an opinion about your assumptions. Geesh, there
are enough pundits in these newsgroups already!

I was commenting on the methodology as it relates to estimating future
growth of mutual funds.

First, 10% is probably an APY. Therefore, the average monthly growth
rate is not simply 10%/12, which is used in the "dinkytown" savings
account calculator. Instead, it is (1+10%)^(1/12)-1 or
rate(12,0,-1,1+10%). (But sometimes RATE() has trouble determining an
answer unless we provide a "guess".)

Conversely, if you want use a simple calculator like "dinkytown", the
rate of return to use is 12*((1+10%)^(1/12)-1) -- approximately 9.57%.

Second, if mutual fund investment calculator computes "total
return" (I suspect it does), that might include an estimate of
reinvested dividends. That is the number used in prospectuses. That
complicates the computation enormously.

HTH. I'm sure I have left off some other things.

 

[Harlan Grove]

"Harlan Grove" <[email protected]> sayd the following: ....

That 2nd formula gives an error of "#NAME?"

Which means you don't have the Analysis ToolPak loaded. Fine, use

=FV(1.1^(1/12)-1,300,-100,-500)

And I am assuming a continual interest rate while placing exactly $100
in at the start of the month [or end if it makes this easier?]

Yup, 10% effective annual interest rate, assuming you mean continuOUS
COMPOUNDING since 'continual interest' has no meaning in Interest Theory,
and could only be interpreted colloquially as meaning your mutual fund
continues to pay you interest rather than becoming insolvent or taking the
money and running away.

 

[Crackles McFarly]

When I asked you to provide a URL for an online calculator, I meant
the URL of an online calculator that was giving different results,
"off by as much as 5%" you wrote.

I overstated that a bit. It was more like $1,200 out of $250K in a 25
year period which aint close to 5%..

I was not offering an opinion about your assumptions. Geesh, there
are enough pundits in these newsgroups already!

I was commenting on the methodology as it relates to estimating future
growth of mutual funds.

First, 10% is probably an APY. Therefore, the average monthly growth
rate is not simply 10%/12, which is used in the "dinkytown" savings
account calculator. Instead, it is (1+10%)^(1/12)-1 or
rate(12,0,-1,1+10%). (But sometimes RATE() has trouble determining an
answer unless we provide a "guess".)

Conversely, if you want use a simple calculator like "dinkytown", the
rate of return to use is 12*((1+10%)^(1/12)-1) -- approximately 9.57%.

Second, if mutual fund investment calculator computes "total
return" (I suspect it does), that might include an estimate of
reinvested dividends. That is the number used in prospectuses. That
complicates the computation enormously.

Yeah, none of these online calculators seem to take into account how
each month you take nothing OUT and simply grow by way of rates and
reinvesting your gains [which has to be a gigantic factor i'd think?]

HTH. I'm sure I have left off some other things.

It helped..

 

[joeu2004]

=fv(10%/12, 12*25, -100, -500)
Results in $21,838.02. Calculator result: $21,838. Where's the
error you are talking about?

In Excel with that formula I get $138,712[.] What is up?

My bad! Looks like I typed 10 instead of 25. Made the same mistake
with the calculator; but that's easy because that calculator defaults
to 10 years.