Next, we need to convert the result that ROUNDUP/NPER return into a number of years and months. If you wanted to round a number to say the nearest thousand, you’d use -3 instead.įigure 2: Use the ROUNDUP function with the NPER formula to convert to months. Num_digits – in this case we’ll use zero, as we wish to round up to the next whole month.
Number – In this case the result of NPER will be our number The ROUNDUP function rounds a number up, as opposed to the commonly used ROUND function that may round numbers up or down. Loan periods will typically involve some fraction of the final month, which for our purposes we want to treat as a whole month. Let’s extend our calculations to make the output more user-friendly.įirst, as shown in Figure 2, we’ll add the ROUNDUP function to our NPER formula. I omitted the 2 optional arguments, so in this case, the PMT function assumes the loan is paid to 0 and payments are made at the end of each period.įigure 1: Use Excel’s NPER function to calculate the payment term for a loan.įor longer term loans, NPER may return a large number of months, such as 94, which can be difficult to convert to months in your head. Always be sure that the payment is shown as a negative number, otherwise NPER may show a slightly longer payment term. You may also specify 0 in this position to explicitly indicate that payments are made at the end of each period.Īs shown in Figure 1, it will take 36 months to pay back $20,000 with a monthly payment of $586.04 at an interest rate of 3.5 percent. Type – This optional argument allows you to specify if payments are made at the beginning of each period, or you can omit the argument to indicate that payments are made at the end of each period. Omitting this argument implicitly states that the loan is to be paid down to 0. Pmt – The monthly payment, which should always be shown as a negative amount.įv – This optional argument allows you to specify a future value if a balloon amount is due at the end of the loan. Rate – The interest rate for the loan expressed as a monthly rate. NPER has 3 required and 2 optional arguments: In this case, Excel’s NPER function (short for number of periods) enables you to calculate the fourth value. Sometimes, such as with a credit card balance, you’ll know the interest rate, payment, and loan amount, but not the term. You can get a refresher on the PMT and CUMIPMT functions, but any typical loan has four key values:Īs discussed in my previous article, if you have the interest rate, term, and loan amount, you can then use the PMT function to solve for the payment. This time around I’m going to use the NPER function in Excel to show you how you can determine just how long it will take you to payoff that credit card bill on which you’re making monthly payments.
Collapsing subtotal in excel 2011 for mac how to#
Previously I’ve explained how you can use worksheet functions in Excel to determine the payment for a loan, as well as how to calculate total interest in a single worksheet cell.
0 kommentar(er)
