
Start learning with our library of video tutorials taught by experts. Get started
In this course, author Curt Frye shows how to perform a wide range of financial calculations quickly and easily using the many financial functions found in Excel 2010. The course details dozens of functions for evaluating cash flows; calculating depreciation; determining rates of return, bond coupon dates, and security durations; and more.
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, selfexplanatory, 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.
Find answers to the most frequently asked questions about Excel 2010: Financial Functions in Depth.
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, fullscreen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.