Amortization Table Using EL-738 in Excel

An amortization table shows how each loan payment is divided between interest and principal and how the balance declines over time. The EL-738 is a financial calculator, not an Excel worksheet generator; use it to check loan-payment figures, then build the table in Excel from the loan amount, APR, and term.

Formula

This calculator estimates a fixed monthly payment and the total interest paid over the term. The payment formula is:

Payment = round(P × r / [1 − (1 + r)⁻ⁿ], 2)

• P is the starting loan principal, in dollars.
• APR is the annual percentage rate entered as a percentage, such as 8 for 8%.
• r is the monthly rate, calculated as APR ÷ 1200.
• n is the number of monthly payments: years × 12.

Total interest here is payment × number of payments − principal. It uses the payment rounded to cents, so it is a level-payment estimate; a real final payment may differ because lenders can adjust for exact dates, rounding, fees, or contract terms.

Worked Example

Using the calculator’s defaults—$20,000 principal, 8% APR, and five years:

1. Convert the annual APR to a monthly rate: 8 ÷ 1200 = 0.0066667.
2. Find the number of payments: 5 × 12 = 60.
3. Apply the payment formula: $20,000 × 0.0066667 ÷ [1 − (1.0066667)⁻⁶⁰] = approximately $405.53 per month after cent rounding.
4. Calculate estimated total interest using the displayed payment: $405.53 × 60 − $20,000 = $4,331.80.

The calculator therefore displays a monthly payment of $405.53 and estimated total interest of $4,331.80. These are the figures to use when checking the corresponding spreadsheet setup.

Assumptions

• The interest rate stays fixed throughout the stated term.
• Payments are equal monthly payments, made at the end of each monthly period.
• Interest accrues monthly using APR divided by 12.
• Fees, taxes, insurance, PMI, escrow, and any separate charges are not included.

Build the Amortization Table in Excel

First calculate the payment, then create one row per payment period. For example, enter the principal in B1, the annual APR in B2 as a percentage (8%), and the term in years in B3. In B4, calculate the monthly payment with =ROUND(-PMT(B2/12,B3*12,B1),2). Excel’s PMT function returns a negative value when the loan amount is positive under its cash-flow sign convention; the leading minus sign makes the displayed payment positive.

Set up columns for payment number, beginning balance, payment, interest, principal repaid, and ending balance. In row 7, one possible layout is:

• A7, payment number: 1; A8: =A7+1, filled down.
• B7, beginning balance: =$B$1; B8: =F7, filled down.
• C7, payment: =$B$4.
• D7, interest: =B7*$B$2/12.
• E7, principal repaid: =C7-D7.
• F7, ending balance: =B7-E7.

Copy the formulas in C7:F7 down for the full number of payments, usually the term in years multiplied by 12. Format dollar columns as currency and keep enough decimal precision in the formulas. Formatting a cell to show two decimal places does not itself round the stored value; use ROUND in the formula only when you want to model payment rounding explicitly. Depending on the lender’s schedule, the final row may need a slightly adjusted payment to bring the balance exactly to zero.

Using the EL-738 to Check Inputs

Calculator models and menus can vary by EL-738 version, so consult the manual for the exact key sequence and supported display options. In general, enter the principal, annual interest rate, and number of payment periods in the appropriate time-value-of-money fields, then calculate the payment. Confirm whether the calculator expects APR as a percent or a decimal, and whether its period setting is monthly. A mismatch between annual and monthly periods can produce a plausible-looking but incorrect answer.

Use the calculator’s payment result as a cross-check against Excel’s PMT result; do not assume the EL-738 automatically creates or exports a complete schedule. The spreadsheet supplies the period-by-period detail that a payment calculation alone does not show. You can also compare the first row’s interest with beginning balance × monthly rate, then check that principal repaid equals payment minus interest.

Common Errors and Limits

• APR entered incorrectly: If the cell contains 8 rather than 8%, divide by 1200 in formulas, not by 12. If it contains 8%, divide by 12.
• Wrong period count: A five-year monthly loan has 60 payments, not 5.
• Sign confusion: PMT uses cash-flow signs. A minus sign in front of PMT is a convenient way to show a positive payment for this layout.
• Rounding every intermediate value: Rounding interest and balance at every step can cause cumulative discrepancies. Follow the lender’s stated rounding method when matching an actual statement.
• Assuming every loan is level-payment: Variable rates, irregular payment dates, interest-only periods, fees financed into the balance, and extra payments require additional modeling.

This schedule is an estimate for a fixed-rate loan with regular monthly payments. It does not establish the exact payoff amount on a particular date, which can depend on contract terms and accrued interest.

Frequently Asked Questions

Can the EL-738 create an amortization table in Excel?
The calculator can help determine or verify loan figures, but Excel is where you create the row-by-row table. Check your EL-738 manual for the functions available on your specific model.

What Excel formula calculates the monthly loan payment?
For principal in B1, APR stored as a percentage in B2, and years in B3, use =ROUND(-PMT(B2/12,B3*12,B1),2). This assumes monthly payments and a fixed rate.

Why does Excel show a negative PMT result?
Excel represents money paid out and money received with opposite signs. If the principal is entered as positive, PMT commonly returns a negative payment; use a leading minus sign to display it as positive.

Why does my last balance not equal zero?
Small differences can result from rounding, payment timing, or formula references. The actual final payment may be adjusted, and a real lender schedule may use different rounding or accrual conventions.

Does an amortization table include fees or insurance?
Not unless you add those amounts as separate inputs or rows. The calculations here cover principal and interest only; check the lender’s disclosure for the loan’s complete payment details.

This result is an estimate for education, not financial advice; the lender’s disclosure gives the exact figures.

Related

• Income Threshold for a Qualifying Relative in 2025
• HomeReady Qualifying Income: How It Is Calculated
• HUD Qualifying Income: What Counts for a Mortgage?

in p | Loan amount | $ | 20000 | 1 | 100000000
in apr | Interest rate (APR) | % | 8 | 0.01 | 100
in yrs | Loan term | years | 5 | 0.0833 | 50
out pmt | Monthly payment | $ | round(p * (apr / 1200) / (1 - (1 + apr / 1200) ^ -(yrs * 12)), 2) | 2
out int | Total interest | $ | pmt * yrs * 12 - p | 2