BITAND()

FV(), Payment(), PV()

These functions do computations for financial transactions. They return future value, payment amount and present value, respectively.

Usage

nFutureValue = FV( nPayment, nPeriodicInterest, nPeriods ) nPresentValue = PV( nPayment, nPeriodicInterest, nPeriods )

Parameter	Value	Meaning
nPayment	Any numeric type	The amount paid or received at the end of each period. (For example, the amount paid into a retirement account each month.)
nPeriodicInterest	Any numeric type	The interest rate for one period. If payments are made monthly, this is the monthly interest rate.
nPeriods	Any numeric type	The number of periods involved. For example, if there are monthly payments for one year, nPeriods is 12.
nFutureValue	Numeric or Currency, depending on the type of nPayment	The future value of an investment.
nPresentValue	Numeric or Currency, depending on the type of nPayment	The amount that must be invested to end up with a specified amount at the end of a specified time.

Future Value is simply compounding of interest. It says, "If I put away so many dollars (or francs or whatever) every month (or year or whatever) at this interest rate, how much will I have after so many months (or years or whatever)?" nPayment is the "so many dollars," nPeriodicInterest is "this interest rate," and nPeriods is "so many months."

A positive nPayment indicates money being socked away. A negative nPayment indicates debt being incurred. It doesn't really matter which way you do this, though, because you get the same answer, except for the sign.

Present Value is more complicated. It translates to "If I want to have so many dollars at the end of so many months (or years or whatever), and I'm getting this much interest, how much do I have to set aside?" It's complicated because the "so many dollars" is the total of nPayment over nPeriods (that is, nPayment*nPeriods). The result is the amount you'd have to invest in a series of equal payments at that interest rate to end up with the desired total; you have to divide by nPeriods to figure out how much you need each period.

For example, if you want to end up with $1,200 after 12 months, you'd set nPayment to 100. You get back the amount you need to put away at nPeriodicInterest in order to end up with your $1,200. To figure your monthly payment, divide the result by 12.

As with FV(), whether nPayment is positive or negative doesn't make much difference in the result—only in how you explain it. If nPayment is positive, PV() represents the amount you need to save to end up with the desired result. If nPayment is negative, PV() is the amount you need to pay in order to pay off a debt, if the other person is charging you the specified interest rate.

Both PV() and FV() allow you to pass negative numbers for nPeriodicInterest and nPeriods, but the results don't change. That is, FV(100, .01, 12)=FV(100, -.01, 12). Clearly this is wrong. Either these functions should reject negative interest rates and periods or they should return appropriate results. These bugs have been there since time immemorial, so we've pretty much given up hope of their getting fixed.

If .NULL. is passed in any parameter of these functions, the result is null.

Example

* $100 a month at 12% for one year ?FV(100, .01, 12)  * Future Value is 1268.25 - what you'll have  * after one year of this. ?PV(100, .01, 12)  * Present Value is 1125.51 - pay 1/12th each * month to end up with $1200.

Usage

nPayment = PAYMENT( nPrincipal, nPeriodicInterest,            nPeriods )

Parameter	Value	Meaning
nPrincipal	Any numeric type	The amount of debt. For example, the amount of a mortgage.
nPeriodicInterest	Any numeric type	The interest rate for one period. If payments are made monthly, this is the monthly interest rate.
nPeriods	Any numeric type	The number of periods involved. For example, for a 30-year mortgage paid monthly, nPeriods is 360.
nPayment	Numeric or Currency, depending on nPrincipal	The monthly payment necessary to pay off nPrincipal at nPeriodicInterest in nPeriods.

PAYMENT() is the function that lets you compute your mortgage or car payments. It tells you the monthly payment needed to amortize a specified amount (nPrincipal) at a given interest rate (nPeriodicInterest) over a specified length of time (nPeriods).

Like PV() and FV(), you can specify a negative principal. This changes the sign of the result, giving a negative payment amount. If .NULL. is passed for any parameter, the result is null.

PAYMENT() shares the bug of allowing, but ignoring, negative interest rates and number of periods.

In all these functions, don't forget to divide the annual interest rate by 12 if you're dealing with monthly periods or by 52 if you've got weeks. You'll get some really strange results otherwise. (For a $100,000 30-year loan, if you forget to divide the 8% interest rate by 12, you get to pay $8,000 a month instead of $733.76.)

Finally, be aware that these functions assume interest compounding every period. While this is common in the U.S., other places may have other customs.

Example

* $10,000 at 12% for 1 year, monthly payments ? PAYMENT(10000, .01, 12)     && Returns 888.49 * Make it weekly payments ? PAYMENT(10000, .12/52, 52)  && Returns 204.30 * Make one annual payment ? PAYMENT(10000, .12, 1)      && Returns 11200

See Also

Calculate

View Updates

Copyright © 2002 by Tamar E. Granor, Ted Roche, Doug Hennig, and Della Martin. All Rights Reserved.