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.

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


On Aug 20, 3:50 pm, Crackles McFarly wrote:
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.

On Aug 20, 12:50 pm, Crackles McFarly wrote:
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.

On Mon, 20 Aug 2007 17:56:42 -0700, joeu2004
sayd the following:

On Aug 20, 12:50 pm, Crackles McFarly wrote:
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.


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

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

On Aug 20, 9:01 pm, Crackles McFarly wrote:
On Mon, 20 Aug 2007 17:56:42 -0700, joeu2004 wrote:
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).

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.

On Mon, 20 Aug 2007 22:47:42 -0700, joeu2004
sayd the following:

On Aug 20, 9:01 pm, Crackles McFarly wrote:
On Mon, 20 Aug 2007 17:56:42 -0700, joeu2004 wrote:
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).

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.

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

On Aug 21, 12:35 pm, Crackles McFarly wrote:
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.

"Crackles McFarly" wrote...
....
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.

Errata....

On Aug 20, 10:47 pm, I wrote:
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.

=fv(1%, 12*5, -100, -100000)
This results in the (correctly) positive value of $189,836.64 (rounded).

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.

On Aug 21, 11:22 am, I wrote:
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.