Microsoft Office Tutorials and References

In Depth Information

**Using PMT to Calculate the Monthly Payment on an Automobile Loan**

The PDURATION function calculates the number of periods for an investment

to reach a certain value given an interest rate. This function takes the fol-

lowing arguments:

•
rate

rate
—
This is the assumed annual interest rate that the investment

will earn.

•
ppv
—
This is the current value of the investment.

•
ffv
—
This is the target ending value of the investment account.

Refer to
Figure 13.1
for an example. An IRA with $175,000 earning 1.29% interest

will require 135.7 years to reach $1,000,000 in value.

Using

Using
PMT

PMT
to Calculate the Monthly Payment on an Automobile Loan

to Calculate the Monthly Payment on an Automobile Loan

Buying a car is one of the most exciting purchases. Whether the car is brand

new or just new to you, nothing attracts attention in your neighborhood like

a new car pulling into the driveway.

Before shopping for a car, you should take a 5-minute spin through Excel to

calculate potential car payments. Knowing the price that will get you to the

desired car payment will enable you to haggle with the sales rep from a po-

sition of knowledge.

Syntax

PMT(rate,nper,pv,fv,type)

The PMT function calculates the payment for a loan based on constant pay-

ments and a constant interest rate. This function takes the following argu-

ments:

•
rate

rate
—
This is the interest rate for the loan. Note that interest rate

is often expressed as an annual rate. To calculate a monthly pay-

ment, you have to divide that rate by 12.

•
nper

nper
—
This is the term, or the total number of payments for the loan.

•
ppv
—
This is the present value, or the loan amount; it is also known

as the principal.

•
ffv
—
This is an optional future value, or a cash balance you want

to attain after the last payment is made. For a car payment calcula-

tion, this should be 0. If fvis omitted, it is assumed to be 0; that is, the

future value of a loan is zero.