Future Value calculation

[Pat Jennings]

There is a series of investments (same amount, for a specified time).
Afterwhich, there is a series of investments (same amount - but different
from the amount of the first series). How to calculate the Future Value
over the entire time-period. First calculation of the FV with a constant
interest rate over the entire combined series. Second, with an interest rate
that changes at the onset of the second investment series.

 

[Chip Pearson]

Pat,

Try using the FV function, and using an FV function at the PV
value. E.g.,

=FV(rate2,nper2,pmt2,FV(rate1,nper1,pmt1))


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Pat Jennings]

Thank you Chip. It appears to work - although, I do not know of a way to
verify.

 

[Fred Smith]

You can verify the calculation by creating a spreadsheet. Put each cash flow
in Column A, the interest rate in Column B and the term (number of periods)
in C. In Column D calculate the future value [=fv(b1,c1,0,a1)]. Sum Column
D. It should equal the result of Chip's formula

 

[Pat Jennings]

OK. However, my original posting was not stated correctly. What I am
trying to figure out is the cash flow of an annuity, wherein the second
series of flows is not investments. Rather, the second series is withdrawls
from the balance (future value) of the first series. The balance, as
withdrawls are being made, will continue to earn interest. I'm not sure
which sign (+/-) goes where.

You can verify the calculation by creating a spreadsheet. Put each cash flow
in Column A, the interest rate in Column B and the term (number of periods)
in C. In Column D calculate the future value [=fv(b1,c1,0,a1)]. Sum Column
D. It should equal the result of Chip's formula

--
Regards,
Fred
Please reply to newsgroup, not e-mail

Thank you Chip. It appears to work - although, I do not know of a way to
verify.

 

[Pat Jennings]

Amplifying my note, here are the values to be considered in the calculation:
First investment interest rate: 10.4%, annually
First series investment amount: $1,503.00, annually
First series: 20 years

Second rate: (A) 10.4%, alternate case (B) 15%
Second series withdarawls from the balance: $837, annually
Second series: 11 years
have been fighting this for some time now. Any assistance in teaching me
how to structure the formula will be greatly appreciated.
Thanks

OK. However, my original posting was not stated correctly. What I am
trying to figure out is the cash flow of an annuity, wherein the second
series of flows is not investments. Rather, the second series is withdrawls
from the balance (future value) of the first series. The balance, as
withdrawls are being made, will continue to earn interest. I'm not sure
which sign (+/-) goes where.

You can verify the calculation by creating a spreadsheet. Put each cash flow
in Column A, the interest rate in Column B and the term (number of periods)
in C. In Column D calculate the future value [=fv(b1,c1,0,a1)]. Sum Column
D. It should equal the result of Chip's formula

--
Regards,
Fred
Please reply to newsgroup, not e-mail

Thank you Chip. It appears to work - although, I do not know of a way to
verify.

Pat,

Try using the FV function, and using an FV function at the PV
value. E.g.,

=FV(rate2,nper2,pmt2,FV(rate1,nper1,pmt1))


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com





There is a series of investments (same amount, for a specified
time).
Afterwhich, there is a series of investments (same amount - but
different
from the amount of the first series). How to calculate the
Future Value
over the entire time-period. First calculation of the FV with a
constant
interest rate over the entire combined series. Second, with an
interest rate
that changes at the onset of the second investment series.

 

[Fred Smith]

While there is a convention for signs (-ve is money out of your pocket, +ve
is money into your pocket), in fact it doesn't matter as long as you are
consistent.

PV is normally positive, because you've taken money out of your pocket to
invest. Follow the same convention with PMT. If you're investing money, it's
positive; if you're receiving the money (like your withdrawals), it's
negative. If PV is positive, FV is negative, because the cash flow is in the
opposite direction.

--
Regards,
Fred
Please reply to newsgroup, not e-mail

Amplifying my note, here are the values to be considered in the calculation:
First investment interest rate: 10.4%, annually
First series investment amount: $1,503.00, annually
First series: 20 years

Second rate: (A) 10.4%, alternate case (B) 15%
Second series withdarawls from the balance: $837, annually
Second series: 11 years
have been fighting this for some time now. Any assistance in teaching me
how to structure the formula will be greatly appreciated.
Thanks

OK. However, my original posting was not stated correctly. What I am
trying to figure out is the cash flow of an annuity, wherein the second
series of flows is not investments. Rather, the second series is withdrawls
from the balance (future value) of the first series. The balance, as
withdrawls are being made, will continue to earn interest. I'm not sure
which sign (+/-) goes where.

You can verify the calculation by creating a spreadsheet. Put each

cash
flow

in Column A, the interest rate in Column B and the term (number of periods)
in C. In Column D calculate the future value [=fv(b1,c1,0,a1)]. Sum Column
D. It should equal the result of Chip's formula

--
Regards,
Fred
Please reply to newsgroup, not e-mail


Thank you Chip. It appears to work - although, I do not know of a

way

to

 

[Pat Jennings]

Therefore, FV is a "loss" if it shows a negative sign when calculated using
the FV function?

While there is a convention for signs (-ve is money out of your pocket, +ve
is money into your pocket), in fact it doesn't matter as long as you are
consistent.

PV is normally positive, because you've taken money out of your pocket to
invest. Follow the same convention with PMT. If you're investing money, it's
positive; if you're receiving the money (like your withdrawals), it's
negative. If PV is positive, FV is negative, because the cash flow is in the
opposite direction.

--
Regards,
Fred
Please reply to newsgroup, not e-mail

Amplifying my note, here are the values to be considered in the calculation:
First investment interest rate: 10.4%, annually
First series investment amount: $1,503.00, annually
First series: 20 years

Second rate: (A) 10.4%, alternate case (B) 15%
Second series withdarawls from the balance: $837, annually
Second series: 11 years
have been fighting this for some time now. Any assistance in teaching me
how to structure the formula will be greatly appreciated.
Thanks

OK. However, my original posting was not stated correctly. What I am
trying to figure out is the cash flow of an annuity, wherein the second
series of flows is not investments. Rather, the second series is withdrawls
from the balance (future value) of the first series. The balance, as
withdrawls are being made, will continue to earn interest. I'm not sure
which sign (+/-) goes where.

You can verify the calculation by creating a spreadsheet. Put each cash
flow
in Column A, the interest rate in Column B and the term (number of
periods)
in C. In Column D calculate the future value [=fv(b1,c1,0,a1)]. Sum Column
D. It should equal the result of Chip's formula

--
Regards,
Fred
Please reply to newsgroup, not e-mail


Thank you Chip. It appears to work - although, I do not know of a

way

to
verify.

Pat,

Try using the FV function, and using an FV function at the PV
value. E.g.,

=FV(rate2,nper2,pmt2,FV(rate1,nper1,pmt1))


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com





There is a series of investments (same amount, for a specified
time).
Afterwhich, there is a series of investments (same amount - but
different
from the amount of the first series). How to calculate the
Future Value
over the entire time-period. First calculation of the FV with a
constant
interest rate over the entire combined series. Second, with an
interest rate
that changes at the onset of the second investment series.

 

[Myrna Larson]

Hi, Fred:

I'm confused by your examples. You say (ordinarily) negative is money out of
your pocket, positive is money into your pocket. That's also the way I usually
think of it, too.

But then you say if you are investing money, it's positive. Let's say we're
buying a CD at the bank. I would use a negative sign for this. I'm taking
money out of my pocket and paying it to the bank. And if I get money back from
the bank (a withdrawal), I put it in my pocket, so from the viewpoint of my
pocket, it's positive. i.e. money going TO the bank is negative, coming FROM
the bank is positive.

 

[hgrove]

Fred Smith wrote...

While there is a convention for signs (-ve is money out of your
pocket, +ve is money into your pocket), in fact it doesn't matter
as long as you are consistent.

Sign convention is an abomination foisted upon students in some bu
mercifully not all business courses. There's no need to use a sig
convention except in explicit period-by-period cashflows. PV, FV an
PMT don't need signs as long as they're defined in terms of each othe
on opposite sides of an equation.

PV is normally positive, because you've taken money out of your
pocket to invest. . . .

You're now contradicting what you wrote in the previous paragraph, thu
illustrating the foolishness of using sign conventions.

. . . Follow the same convention with PMT. If you're investing
money, it's positive; if you're receiving the money (like your
withdrawals), it's negative. If PV is positive, FV is negative,
because the cash flow is in the opposite direction.

Nope. If PV is X, and the discounted value of N payments of Y each wer
0.9 * X, then FV would represent another 'payment' (you'd need to pa
off the balance of the loan of X at the end of the series of N payment
of Y). However, if the discounted value of the N payments of Y each wer
1.05 * X, then FV would represent a return of the cumulativ
overpayment. In the first instance, FV would have the same sign as PM
and opposite sign as PV. In the second, FV would have the same sign a
PV and opposite sign as PMT. When there are no payments, FV has th
opposite sign as PV since the discounted value of zero payments i
zero, and the discounted value of zero is zero and less then th
absolute value of PV.

Using either sign convention, all that's required is that one of th
three terms, PV, FV or PMT, has one sign and the other two have th
opposite sign. For example, PV and PMT both > 0 represents an initia
lump sum investment plus periodic additional investments. FV <
represents the cash-out value.

Sign convention follows from defining PV, FV and PMT as

PV + discounted FV + discounted N payments of PMT = 0

These terms can just as well be defined using

PV = discounted FV + discounted N payments of PMT

and indeed Lotus 123 and other spreadsheets not slavishly followin
Excel's specs implicitly use the latter identity. For that matter, th
latter could be rewritten as

PV - discounted FV - discounted N payments of PMT = 0

So it all boils down to whether it makes more sense to say, fo
example,

balance = loan amount *MINUS* principal payments to date

dispensing with sign convention, or

balance = loan amount *PLUS* principal payments to date

requiring a sign convention in which loan amount and principal payment
have opposite signs. It's unfortunate there are so many irredemabl
obstinate people who hold the latter as orthodoxy, and even worse tha
some of them were among the original Excel developers (or maybe th
original Multiplan developers)

 

[hgrove]

Pat Jennings wrote...

Amplifying my note, here are the values to be considered in the
calculation:
First investment interest rate: 10.4%, annually
First series investment amount: $1,503.00, annually
First series: 20 years

Second rate: (A) 10.4%, alternate case (B) 15%
Second series withdarawls from the balance: $837, annually
Second series: 11 years

...

If the second series follows the first series immediately, and you wan
the future value at the end of the second series,

(A)
=FV(0.104,11,837,FV(0.104,20,1503))

(B)
=FV(0.15,11,837,FV(0.104,20,1503))

Thanks to the miracle of Excel's sign convention, you can represen
both the investments in the first series and the withdrawls in th
second series with the same sign. This is a wonderful example of wh
sign conventions are idiotic

 

[Fred Smith]

Hi Myrna,

You are right, I contradicted myself with my example. I realized it, as
usual, just after I hit the send button. I should have stopped at the point
where I said it really doesn't matter which way you use the signs, as long
as you're consistent.

I don't agree with Harlan that the sign convention is an "abomination". I
find it quite useful, especially when I'm dealing with PMT. With PV and FV,
the sign is simple, they just have to be the opposite of each other. With
PMT, I have to remember am I getting money (like FV), or am I putting money
in (like PV).

Excel will quite handily handle whatever signs you throw at it. You just
have to decide what system will ensure you get the right result with the
fewest number of attempts. Sticking to a particular sign convention, in my
mind, is best.

 

[Pat Jennings]

I've extracted a portion of your reply:
"you can represent both the investments ... and the withdrawls ... with the
same sign."
If that is the case, how would one know that either one is an investment or
a withdrawl - from looking at the formula?

 

[Harlan Grove]

I've extracted a portion of your reply:
"you can represent both the investments ... and the withdrawls ... with
the same sign."
If that is the case, how would one know that either one is an investment
or a withdrawl - from looking at the formula?

Why it's clear as crystal if you think in terms of Excel's sign convention.\

Yes, the only way to figure out that the 3rd argument in the inner FV call
were deposits and the 3rd argument in the outer FV call were withdrawls
would be to read the formula in light of the sign convention. I could have
written (A) as

=FV(0.104,11,-837,-FV(0.104,20,1503))

but would that have clarified that the accumulated value after the first
series (the deposits) was what most people would call a positive balance?

I don't use sign posts to point out when I'm being sarcastic. I was
sarcastic before, and I'm being so again in the preceding paragraph. I
neither like nor use a sign convention except to the extent I have to work
around Excel's. My formulas were intended to show just how stupid it is. I
agree it makes it nearly impossible to figure out unless one only thinks of
money as either debits or credits.

Lotus got this right, and Microsoft screwed it up. In 123, it's

@FV(0.104,11,-837,@FV(0.104,20,1503))

That said, Microsoft isn't to blame for originating this. Some business
schools did, and it was perpetuated by HP in their fiancial calculators.

 

[Harlan Grove]

. . . With PV and FV,
the sign is simple, they just have to be the opposite of each other. . . .

You just don't understand the math.

Look at the formula in online help for the PV function. PV, FV and PMT all
appear on the same side of the equation with zero on the other, and these
three terms and their respective dicount multipliers are *added* together.
All that's necessary is that one of the terms have the opposite sign as the
other two. That's all. PV and FV *can* have the same sign as long as PMT has
the opposite and the discounted absolute values of the payments exceeds the
absolute value of PV.

It's really basic math, and you're muddled & fuddled by trying to attach
some deeper meaning to the signs.

Excel will quite handily handle whatever signs you throw at it. You just
have to decide what system will ensure you get the right result with the
fewest number of attempts. Sticking to a particular sign convention, in my
mind, is best.

So my first solution to the OP's problem,

=FV(0.104,11,837,FV(0.104,20,1503))

is absolutely clear to you? I suppose you'd prefer

=-FV(0.104,11,-837,-FV(0.104,20,1503))

but to me this is no where near as clear as 123's

@FV(0.104,11,-837,@FV(0.104,20,1503))

This is a perverse cousin of last week's slug fest in .programming over
Excel's unary minus precedence. For some people, a - b is just a minus b.
For others it just has to be rewritten a + (-b).

 

[Harlan Grove]

=FV(0.104,11,-837,-FV(0.104,20,1503))

....

Oops. This would have the opposite sign (negative). Make that

=-FV(0.104,11,-837,-FV(0.104,20,1503))

Clearer?