Microsoft Office Tutorials and References

In Depth Information

**Figuring Loan Calculations**

2. Position the cursor in the cell where you want the results to display.

3. Enter
=NPER(
to begin the function entry.

4. Click the cell where you entered the interest rate, or just enter the

cell address.

5. Enter
/12
to divide the annual interest rate to get the monthly

interest rate.

6. Enter a comma (
,).

7. Click the cell where you entered the periodic payment amount, or just

enter the cell address.

8. Enter a comma (
,).

9. Click the cell where you entered the principal amount, or just enter

the cell address.

10. Type a
), and press Enter.

Figure 5-5 shows how I set up a worksheet with values and used the NPER

function to find out how many payments are necessary to pay off a loan.

In this example, we assume you can afford to pay $200 per month for a

loan. The amount you need is $4,000, and you’re able to get a 6 percent

interest rate.

With this set of assumptions, the NPER function returns a value of 21.12

months to pay off the loan. I don’t think anyone will mind if you round that

off to 21 months. Knowing you’ll pay off the loan in less than two years may

very well allow you to plan ahead for some other activity at that time. Did

someone say “Las Vegas”?

Figure 5-5:

The NPER

function

calculates

the number

of payments

for a loan.