NPER
The NPER function returns the number of payment periods for a loan or annuity based on a series of regular periodic cash flows (payments of a constant amount and all cash flows at constant intervals) and at a fixed interest rate.
NPER(periodic-rate, payment, present-value, future-value, when-due)
periodic-rate: A number value representing the interest rate per period. periodic-rate is entered as a decimal (for example, 0.08) or with a percent sign (for example, 8%).
payment: A number value representing the payment made or amount received each period. payment is often formatted as currency. At each period, an amount received is a positive amount and an amount invested is a negative amount. For example, it could be a monthly loan payment (negative) or the periodic payment received on an annuity (positive).
present-value: A number value representing the initial investment, or the amount of the loan or annuity. present-value is often formatted as currency. At time 0, an amount received is a positive amount and an amount invested is a negative amount. For example, it could be an amount borrowed (positive) or the initial payment made on an annuity contract (negative).
future-value: An optional number value that represents the value of the investment or remaining cash value of the annuity (positive amount), or the remaining loan balance (negative amount), after the final payment. future-value is often formatted as currency. At the end of the investment period, an amount received is a positive amount and an amount invested is a negative amount. For example, it could be the balloon payment due on a loan (negative) or the remaining value of an annuity contract (positive). If omitted, future-value is assumed to be 0.
when-due: An optional modal value that specifies whether payments are at the beginning or end of each period. Most mortgage and other loans require the first payment at the end of the first period (0), which is the default. Most lease and rent payments, and some other types of payments, are due at the beginning of each period (1).
end (0 or omitted): Payment is treated as being received or made at the end of each period.
beginning (1): Payment is treated as being received or made at the beginning of each period.
Example 1 |
|---|
Suppose you are planning for your daughter’s college education. You have $50,000 to set aside in a savings account today (present-value) and can add $200 to the account at the end of each month (payment). All are negative because they are cash outflows. The savings account is expected to earn an annual interest rate of 4.5% paid monthly (periodic-rate is 0.045/12). You believe you will need to have set aside $150,000 (future-value is positive because it is a cash inflow) by the time your daughter reaches college age. =NPER(0.045/12, -200, -50000, 150000, 1) returns approximately 180.587402472846 periods, or 15 years, 1 month. This is the number of times you would need to make the monthly deposit, in addition to the initial deposit, in order to reach the goal of $150,000. |
Example 2 |
|---|
Suppose you are planning to purchase your uncle’s mountain cabin. You have $30,000 to use as a down payment today and can afford to make a monthly payment of $1,500 (payment is a negative because it is a cash outflow). Your uncle says he is willing to lend you the difference between the cabin’s sale price of $200,000 and your down payment (present-value, the amount you borrow is a cash inflow of $170,000) at an annual rate of 7% payable monthly (periodic-rate is 0.07/12) at the beginning of each month (when-due is 1). =NPER(0.07/12, -1500, 170000, 0, 1) returns approximately 184.106221541724 periods, or 15 years, 4 months, the length of time it would take to repay your uncle’s loan. |