add-in combo box date excel 2000 excel 2002 excel 97 excel XP format formatting formula grouping loan - amortization macros subtotal sum excel tutorial workbook worksheet vba
Formulas that calculate loan payments, principal, interest and more are found in the Financial category in the Paste Function dialog box. In the figure below is a list of functions and necessary syntax for loan calculations. See the formulas in the gray cells, and the syntax in rows 12-16.
Syntax for loan calculation functions
Rate
The interest rate per period.
Per
The period for which the interest rate is calculated.
Nper
The total number of payments.
Pv
The present value, the total amount that a series of future payments is worth now.
Fv
The future value or a cash balance you want to attain after the last payment is made.
Type
The timing of the payment, either at the beginning or end of the period.
Numbers 0 or 1 represent the payment date. The number 0 represents payment at the end of the period, and the number 1 represents payment at the beginning of the period.The default (empty argument) is 0. The calculation is at the end of the period.
PMT (Rate, Nper, -Loan Amount)
Calculates the payment for a loan based on constant payments and a constant interest rate.
Returns the regular monthly payment on the loan (principal + interest) when the interest for each of the monthly payments is constant.
Example: The principal of a loan is $100,000, and the term of the loan is three years. The monthly payment during the term of the loan is calculated at $3,227; see column B in the figure below.
PPMT (Rate, Which Period, Nper, -Loan Amount)
Returns the amount on the principal for a given period for a loan based on periodic, constant payments and a constant interest rate.
Returns the sum of the principal within the monthly payment (the monthly payment is comprised of the principal + interest). See various examples of calculations in column C, rows 8:10, and the formula syntax in rows 14:16.
IPMT (Rate, Which Period, Nper, -Loan Amount)
Returns the interest payment for a given period for a loan based on periodic, constant payments and a constant interest rate.
Returns the amount of the interest within the monthly payment (the monthly payment is comprised of the principal + interest). See the calculation in cell G11 and the formula syntax in G13.
NPER (Rate, Pmt, -Loan Amount)
Returns the number of loan payments with a constant interest rate. See the formula syntax in D12.
RATE (Nper, Pmt, -Loan Amount)
Returns the interest rate per period of a loan. RATE is calculated by iteration and can have zero or more solutions.
Returns the percentage of interest on the loan, when the number of payments is constant.
PV (Rate, Nper, Pmt)
PV is the present value — the total amount that a series of future payments is worth now.
Returns the current value for a series of payments with a constant interest rate.
Create an Amortization Schedule
Simple amortization schedule
See the example of an amortization schedule in the figure below:
Formulas that do not appear in the figure:
The formula in cell C17 is =LOAN (LOAN is the name of cell C4).
The formula in cell C18 is =G17; copy the formula from cell C18 to all the cells in column C, starting from C18.
Amortization schedule with a grace period
The difference between a regular amortization schedule and one with a grace period is that in the latter, the repayment of the principal is delayed. The loan agreement stipulates the month in which the repayment of principal begins. The interest on the loan is calculated, and the first interest payment starts with the first month after the month that the loan was accepted.
See the example in the figure below. Note that the formulas used are not PPMT and IPMT; these formulas are not appropriate when the calculations are not linear.
Amortization schedule for random payment
See the formulas in the figure below for calculating the sum of the principal and interest in every payment.
The dates of the loan repayment are random. The interest is calculated according to the number of interest days divided by 365 days in a year.
Table for Calculating Payments for a Loan with Two Variables
The amount of money you want to borrow depends on your ability to make regular monthly payments. The monthly repayment sum is, in turn, affected by the amount of the loan, the period of the loan, and the amount of interest.
To help you make a decision as to how much money you can safely borrow, see the table below which displays various loan repayment sum calculations when one of the three variables is constant and the others vary. The table is created by using Table, Data Table. For additional explanations about using the Table technique, see Chapter 25, What If.
The data and formula in the table:
Number of payments is constant—36 (you can change this number at will).
First variable—Amount of the loan, between $10,000 and $100,000; see row 6 in the figure.
Second variable—Interest rate, between 4% and 10%; see column B in the figure.
Formula PMT is in cell B6. This formula returns the amount of the monthly loan payment (principal plus interest). The cells that input the formula are D2:D4.
The formula in the formula bar in the table:
The formulas from a value called TABLE return the monthly payment sum. The two variables that input the formula are in the column and the row.
The formulas from a value called TABLE are automatically input into the cells by using the Table technique. The formula syntax is =TABLE (D4, D2). You cannot enter the function directly. You must follow the steps in the next section, “How to create a table with two variables.”
How to create a table with two variables
1. Enter the loan data in the sheet according to the example in the figure, in rows 1-4.
2. Enter the PMT formula =PMT(D2/12,D3,-D4) in cell B6. In the formula’s arguments, select the D2:D4 cells.
3. Enter the various interest percentages in column B under the PMT formula. Enter the various loan sums in the heading row of the table, in cells C6 until G6.
4. Select the B6:G40 range (the PMT formula in the upper corner).
5. From the Data menu, select Table. The dialog box below appears.
6. In the Row input cell box, select D4, the amount of the loan.
7. In the Column input cell box, select cell D2, the interest %.
8. Click OK.