How to Calculate the Term of an Investment Using Microsoft Excel

The NPER function in Excel calculates the number of payment periods required to pay off a loan based on the overall value of the loan, the payments and the interest rate. The resulting value may also be referred to as the term of an investment. The following steps explain how to use NPER in Excel.

Instructions

1. Grasp how the term of an investment is calculated. NPER uses the financial formula nper = ln(pmt/(pmt - rate * pv))/ln(1+rate) where nper is the number of payment periods, pmt is the amount of the payment, rate is the interest rate for the payment period and pv is the value of the loan.

2. Launch Excel and position the cursor over the cell you wish to calculate the term of investment for. That cell will now have a bold black outline indicating it has been selected.

3. Enter "=nper(" into the cell. A tip will appear that reads "NPER(rate,pmt,pv,[fv],[type])." This indicates the syntax for the NPER function. The parameter that Excel is expecting you to input next will be in bold. Parameters in square brackets are optional.

4. Provide the interest rate for the period, the payment amount for the period and the value of the loan.

5. Input non-default values for the optional fields. You may provide the future value which is the desired balance after the last payment. The default is zero. You may also use the type field to indicate when payments are due. A "0" means the payment is due at the end of the period and a "1" means it is due at the beginning of the period. The default for this field is also "0."

Resources

• Information on using Excel