Solar Panels For Homes

The NPER function calculates the term, or number of regular payments, on a loan or for

an investment annuity given its interest rate, the payments, present value (or loan balance),

future value (or balloon payment), and, optionally, the type-of-annuity switch. If you set the

type-of-annuity switch to 1, Excel assumes payments occur at the beginning of the period,

following the annuity due convention. If you set the annuity switch to 0 or you omit the

argument, Excel assumes payments occur at the end of the period following the ordinary

annuity convention.

The function uses the following syntax:

NPER (rate, pmt, pv, fv, type)

For example, to calculate the number of $1,000 monthly payments required to pay off a 9%

mortgage that still has a $100,000 mortgage balance, you use the following formula:

=NPER (.09/12,-1000,100000,0,0)

The function returns the value 185.53, representing roughly 185 payments and then another

roughly half payment. Notice that to convert the 9% annual interest to a period interest, the

formula divides the annual interest rate by 12. Notice, too, that the payment amount, as a

cash outflow, shows as a negative value while the loan balance, as an implicit cash inflow,

shows as a positive value.

NOTE The NPER function rarely returns an integer, or whole-number result. As in the preceding

example, it commonly returns a fractional value, indicating that after the last

regular payment, an additional fractional payment will also need to be made.

You can also use the NPER function to calculate investment terms. In this case, you calculate

the number of payments that need to be made in order to reach some future value.

Suppose, for example, that you want to calculate how many years a customer needs to contribute

$2,000 to an Individual Retirement Account in order to amass a $1,000,000 portfolio.

If you assume the customer will earn 9% annually and will make payments at the

beginning of the year, you use the following formula to make this estimate:

=NPER (.09,-2000,0,1000000,1)

The function returns the value 43.45, indicating the $2,000 payments will need to be made

for slightly more than 43 years. Notice that the type switch is 1, which means that the function

returns the amount that must be paid at the beginning of the year. If you instead want

to calculate the amount that would need to be paid at the beginning of each year, you would

use the following formula to make this estimate:

=NPER (.09,-2000,0,1000000,0)

This formula returns the value 44.43. This value is slightly more than the annuity due value

because by making payments at year-end, the customer loses interest.

If you wanted to make the same calculation but recognize the added fact that the customer

already has $5,000 in his IRA account, you would use the formula:

=NPER (.09,-2000,-5000,1000000,0)

This formula returns the value 42.07.

Stephen L. Nelson is a Redmond, Washington CPA and the author of many bestselling books including the MBA’s Guide to Microsoft Excel from which this article is adapted. Nelson also edits the forming an s corp, the incorporating a small business and the forming an LLC web sites.

Bond Pricing, Valuation, Formulas, and Functions in Excel