Microsoft Office Tutorials and References

In Depth Information

**Examples of Functions for Financial Professionals**

period. A value of 1 means the payments are due at the beginning of

each period.

For all the arguments, the cash you pay out, such as deposits to savings,

is represented by negative numbers; the cash you receive, such as dividend

checks, is represented by positive numbers.

Note

Note that the FV formula uses a negative version of cells B4 and B6.

This occurs because these are amounts that leave your wallet and go

to the bank or mutual fund.

Figure 13.6
shows how to use FV for a simple savings calculator. The for-

mula in cell B8 assumes that you continue making the deposit each month

from cell B4 until you retire and that interest rates remain constant. If you

already have some amount in savings, you enter that in cell B6.

Examples of Functions for Financial Professionals

Whereas a typical consumer is interested in the amount of his or her monthly

car payment, a loan maker is interested in the month-by-month breakdown of

principal and interest. Excel offers a complete cadre of functions to perform

these calculations.

Using

Using
PPMT

PPMT
to Calculate the Principal Payment for Any Month

to Calculate the Principal Payment for Any Month

After a bank writes a car loan, the consumer makes monthly payments. To

calculate the principal portion of the payment for any period in the loan,

you use PPMT. Of course, you can use a range of these formulas
—
one for

each month
—
to build an amortization table.

Syntax

PPMT(rate,per,nper,pv,fv,type)

The PPMT function returns the payment on the principal for a given period

for an investment, based on periodic, constant payments and a constant in-

terest rate. This function takes the following arguments:

•
rate

rate
—
This is the interest rate per period.