Re: Future Value calculation

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: hgrove (hgrove.1awekx_at_excelforum-nospam.com)
Date: 08/12/04 

Date: Thu, 12 Aug 2004 18:06:35 -0500

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 but
mercifully not all business courses. There's no need to use a sign
convention except in explicit period-by-period cashflows. PV, FV and
PMT don't need signs as long as they're defined in terms of each other
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, thus
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 were
0.9 * X, then FV would represent another 'payment' (you'd need to pay
off the balance of the loan of X at the end of the series of N payments
of Y). However, if the discounted value of the N payments of Y each were
1.05 * X, then FV would represent a return of the cumulative
overpayment. In the first instance, FV would have the same sign as PMT
and opposite sign as PV. In the second, FV would have the same sign as
PV and opposite sign as PMT. When there are no payments, FV has the
opposite sign as PV since the discounted value of zero payments is
zero, and the discounted value of zero is zero and less then the
absolute value of PV.

Using either sign convention, all that's required is that one of the
three terms, PV, FV or PMT, has one sign and the other two have the
opposite sign. For example, PV and PMT both > 0 represents an initial
lump sum investment plus periodic additional investments. FV < 0
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 following
Excel's specs implicitly use the latter identity. For that matter, the
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, for
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 payments
have opposite signs. It's unfortunate there are so many irredemably
obstinate people who hold the latter as orthodoxy, and even worse that
some of them were among the original Excel developers (or maybe the
original Multiplan developers). 

---
Message posted from http://www.ExcelForum.com/
Quantcast