Trying to calculate how long a loan will take to repay? Microsoft Excel can help. Excel's NPER, or number of periods, function lets you easily calculate how long repaying a loan will take given the loan interest rate, its balance, and the payment made.
The NPER function calculates the term, or number of regular payments, on a loan or for an investment annuity given its interest rate, the payments, present value (or loan balance), future value (or balloon payment), and, optionally, the type-of-annuity switch. If you set the type-of-annuity switch to 1, Excel assumes payments occur at the beginning of the period, following the annuity due convention. If you set the annuity switch to 0 or you omit the argument, Excel assumes payments occur at the end of the period following the ordinary annuity convention.
The function uses the following syntax:
NPER (rate, pmt, pv, fv, type)
For example, to calculate the number of $1,000 monthly payments required to pay off a 9% mortgage that still has a $100,000 mortgage balance, you use the following formula:
=NPER (.09/12,-1000,100000,0,0)
The function returns the value 185.53, representing roughly 185 payments and then another roughly half payment. Notice that to convert the 9% annual interest to a period interest, the formula divides the annual interest rate by 12. Notice, too, that the payment amount, as a cash outflow, shows as a negative value while the loan balance, as an implicit cash inflow, shows as a positive value.
NOTE The NPER function rarely returns an integer, or whole-number result. As in the preceding example, it commonly returns a fractional value, indicating that after the last regular payment, an additional fractional payment will also need to be made.
You can also use the NPER function to calculate investment terms. In this case, you calculate the number of payments that need to be made in order to reach some future value. Suppose, for example, that you want to calculate how many years a customer needs to contribute $2,000 to an Individual Retirement Account in order to amass a $1,000,000 portfolio. If you assume the customer will earn 9% annually and will make payments at the beginning of the year, you use the following formula to make this estimate:
=NPER (.09,-2000,0,1000000,1)
The function returns the value 43.45, indicating the $2,000 payments will need to be made for slightly more than 43 years. Notice that the type switch is 1, which means that the function returns the amount that must be paid at the beginning of the year. If you instead want to calculate the amount that would need to be paid at the beginning of each year, you would use the following formula to make this estimate:
=NPER (.09,-2000,0,1000000,0)
This formula returns the value 44.43. This value is slightly more than the annuity due value because by making payments at year-end, the customer loses interest. If you wanted to make the same calculation but recognize the added fact that the customer already has $5,000 in his IRA account, you would use the formula:
=NPER (.09,-2000,-5000,1000000,0)
This formula returns the value 42.07.
Mastering Accrued Interest Calculations with Microsoft Excel
Microsoft Excel is not just a spreadsheet program; it's a powerful tool for financial calculations, including the computation of accrued interest for various securities. Excel's built-in functions, ACCRINT and ACCRINTM, are specifically designed to simplify the process of calculating accrued interest for securities that pay periodic interest and those that pay at maturity, respectively. This article delves into the nuances of these functions, providing a detailed guide on how to use them effectively, along with common pitfalls to avoid.
Using the Bond Duration Add-in Functions
Making bond duration calculations? Microsoft Excel can help. Excel provides two functions that help you with bond duration calculations.
Using Excel's Xirr and Xnpv Add-in Functions
Making internal rate of return or net present value calculations with Microsoft Excel? Make sure you aren't unnecessarily limiting your options. In addition to the well-know IRR and NPV financial functions, Microsoft Excel also supplies two powerful add-in functions, XIRR and XNPV, that can expand your analytical possibilities.
