Future Value calculation

[Pat Jennings]

I don't know what happended to my original message and the responses on this
subject. I can not find them in the newsgroup.

My problem was understanding calculating FV at the end of First, a series of
investments, followed by withdrawls from the resulting balance at teh end of
the first period. I want to know what the balance would be at the end of
the second stream of withdrawls. There was much said about representing
both streams without needing sign (positive or negative) conventions. That
has me really confused. It would seem that the withdrawls would require a
negative sign. But, I could be wrong. The FV formula creates a
negative-signed amount for an ordinary calculation, wherein positive sign is
used for the investment amount. Given that, if one wants to know the balace
as stated above, shouldn't there be some sign differentation?

My problem variables are:
First investment period: 20 years
First interest rate: 10.4%, annually
First investment amount: $1,503, annually
Second withdrawl period: 11 years
Second interest rate: 10.4% (Case A) and 15% (Case B)
Second withdrawl amount: $837, annually
There are no investments made during the second period.

 

[Harlan Grove]

My problem variables are:
First investment period: 20 years
First interest rate: 10.4%, annually
First investment amount: $1,503, annually
Second withdrawl period: 11 years
Second interest rate: 10.4% (Case A) and 15% (Case B)
Second withdrawl amount: $837, annually
There are no investments made during the second period.

Using the sign convention as it should be,

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

 

[Pat Jennings]

Thank you. Do you know the reason(s) that Microsoft, in the "ordinary" way
the formula is used, chooses to show the calculated FV amount to be
negative?

 

[Tushar Mehta]

By convention, cash inflows and outflows have opposite signs. Doesn't
matter which is which just that they can be clearly identified. Left
to my devices, I designate outflows as negative and inflows as
positive.

So, in your case, for the first period:
=FV(0.104,20,-1503), the -1503 represents outflows, and the FV will be
an inflow. As expected, it will be a positive number.

For the 2nd period, the above amount, now the investment amount,
becomes an outflow. The periodic withdrawals are inflows. So, use
=FV(0.104,11,837,-FV(0.104,20,-1503))

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[hgrove]

Tushar Mehta wrote...

By convention, cash inflows and outflows have opposite signs.
Doesn't matter which is which just that they can be clearly
identified. . . .

Agreed with regard to cashflows defined as a set of *individual* flows
of funds at specific individual periods. The functions NPV and IRR
require such cashflows. However, none of the other built-in financial
functions (so no XNPV and XIRR quibbles) *require* a sign convention.
They have a sign convention because Microsoft chose to defined PV, PMT
and FV using the formula

PV * a + PMT * b + FV = 0

They could just as well have been defined using

PV * a = PMT * b + FV

FWIW, the annuity functions used in life insurance have always used the
latter definition, and their use in life insurance predates their use in
financial economics by centuries.

Some people (I'd bet most) think that 10 - 3 = 7 is clear, but there
are some (too many, but still a minority) who believe that this should
always be rewritten as 10 + (-3) = 7. Excel's financial functions were
designed for the latter group.