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
5. Enter /12 to divide the annual interest rate to get the monthly
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
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”?
for a loan.