Microsoft Office Tutorials and References
In Depth Information
Figuring Loan Calculations
You can use two optional arguments with NPER:
Future Value: The amount you want the loan to be worth at the end of
its life. The default is 0.
Type: This tells the function whether payments are applied at the end
of the period or the beginning of the period. A value of 0 indicates the
end of the period. A value of 1 indicates the beginning of the period. The
default is 0.
These optional arguments, when used, become the respective fifth and sixth
arguments.
Calculating the number of payments
using PDURATION
This function is a twist on how to determine the number of payments. Instead
of using a periodic payment amount in the calculation, PDURATION uses the
present value of the loan (the borrowed amount) and the future value of the
loan (what you will have paid in total when the loan is paid off). This
calculation is useful if and when you know just three pieces of information:
The Loan Principal
Annual Interest Rate
The Amount Paid back (the combined Principal and Interest)
The result PDURATION gives you is the number of periods based on the
previously listed factors.
Here’s how to use the PDURATION function:
1. Enter the following into separate cells on your worksheet:
•Loanprincipal
•Annualinterestrate
•Theexpectedtotalamountyouwillhavepaidbackattheendof
the loan
2. Position the cursor in the cell where you want the results to display.
3. Enter =PDURATION( to begin the function entry.
4. Click the cell where you entered the interest rate, or just enter the cell