Join Curt Frye for an in-depth discussion in this video NPER: Calculating the number of periods in an investment, part of Excel 2010: Financial Functions in Depth.
Managing personal and corporate finances takes careful planning. Many individuals and businesses try to save a certain amount of money whether as part of an individual retirement account or a corporate building fund. Once you determine how much you can set aside each month, you can use Excel's NPER function to see how long it will take you to reach your goal. The NPER function has five arguments. The first is the annual interest rate, self-explanatory, and then the payment each period. And in this scenario what I want to do is to invest an initial $2000 and then make monthly payments of $250 with a goal of reaching $20,000.
So I have my monthly payment of $250 and my present value or initial investment of $2000 and the payment each period and present value are expressed as negative numbers because they represent an outflow from my account. Next we have the future value and future value is the amount in this case that I want to save and that goal is $20,000. And then finally we have when the payment is due and this can either be one of two values. It can be 0, in which case the payment occurs at the end of an accounting period, in other words at the end of the month, or it can be 1 which it is in this case and that means that the payments are due at the beginning.
So normally, the way it works is that if you borrow money, then you pay at the end of an accounting period. That way interest has time to accumulate. Whereas, if you're trying to save money then you want to get the money in as quickly as you can, so that you will accumulate the interest and for that you make the payment in the beginning of the month. So we're using a savings example here, so we make our payments at the beginning of the period. So now the question we're asking is how many periods, in this case how many months, will it take us to accumulate $20,000 given the other parameters.
A 4% interest rate, payment each period of $250 and a starting value of 2000? Well, here is how we do it. We'll go down to cell c10 and type =NPER and then a left parenthesis and then we have the rate in cell c3, but because that's an annual rate and we are making monthly payments, we need to divide the rate by 12, /12, then the payment each period, so that's in C4. Then we have the present value. I typed a comma to the separate the Arguments.
Present value is in cell c5 and then we have the future value of 20,000 in c6, and the type that is when the Ppayment is due, after we type a comma, is in cell c7. Then I type a right parenthesis and check through the formula. Everything looks good and we'll press Enter. So our value of 62.9 indicates that it will take us about 63 months to save $20,000 given the parameters that we've set. But now let's see what happens if we increase our monthly payment, in other words the payment each period to 300.
So remember this value here is 62.9. If we make the payment each period 300, again a negative number because it's an outflow from your account, the number in the c10 changes from 62.9 to 53.5. So, just that extra $50 a month reduces the number of periods that we need to save by about 11, in other words getting into your goal about 1 year faster. The NPER function gives you and your business insights into how long it will take you to reach your savings goal. Remember that even small increases in your monthly contributions to an investment will get you to your goal that much faster.
- Analyzing loans, payments, and interest
- Discovering the interest rate of an annuity
- Determining depreciation using the straight line, declining balance, double-declining balance, and other methods
- Calculating the future value of an investment with variable returns
- Finding the discount rate of a security
- Converting between fractional prices and decimal prices
- Determining the yield of securities that pay interest periodically
Skill Level Intermediate
Q: Where can I learn more about Excel formulas?
A: Discover more on this topic by visiting Excel formulas on lynda.com.