calculate-loan

01 Loan payments

Excel Interest-Only Amortization Schedule Calculator

Calculator No. 01-14165 · Updated · Formula and assumptions shown below

Monthly interest payment $133.33
Total interest during term
$8,000.00
= Monthly interest payment × interest-only term × 12
Principal balance at end
$20,000.00
= Loan amount
Loan amount
$20,000
Input
You enter
Loan amount ($), Interest rate (APR) (%), Interest-only term (years)
You get
Monthly interest payment ($), Total interest during term ($), Principal balance at end ($)
Rounding
2 dp

Estimate for education, not financial advice. The result follows from the numbers you enter and the formula shown. It is not a quote or an offer, and it does not include fees, taxes or insurance unless the calculator asks for them. Ask your lender for the exact figures.

An interest-only amortization schedule shows the interest due during the interest-only period and the remaining principal balance, which usually stays unchanged until principal repayment begins. Enter the loan amount, annual interest rate, and interest-only term to calculate the monthly interest payment, total interest over that period, and ending balance.

Formula

For a simple monthly interest-only schedule, the periodic payment covers interest but does not reduce principal. The formulas are:

  • Monthly interest payment: M = P × (APR / 100) / 12, or P × APR / 1200.
  • Total interest during the period: I = M × 12 × Y.
  • Ending principal balance: B = P, assuming no principal payments, fees added to principal, or other balance changes.

P is the starting loan principal in dollars; APR is the annual percentage rate entered as a percentage; Y is the term in years; M is the monthly interest payment in dollars; I is the total interest paid during the stated term in dollars; and B is the principal balance at the end, in dollars. The calculation treats APR as a nominal annual rate divided into 12 monthly periods. It does not compound unpaid interest into the balance.

Worked Example

Using the calculator’s default values—$20,000 principal, 8% APR, and a five-year interest-only period:

  1. Convert the annual rate to a monthly rate: 8% / 12 = 0.666666…% per month, or 0.08 / 12 = 0.006666666… as a decimal.
  2. Calculate monthly interest: $20,000 × 0.08 / 12 = $133.333…; rounded to cents, the monthly interest payment is $133.33.
  3. Calculate the term in months: 5 × 12 = 60 months.
  4. Multiply the displayed monthly payment by the number of months: $133.33 × 60 = $7,999.80 total interest.
  5. With no principal paid, the ending balance remains $20,000.00.

The total uses the monthly payment rounded to the cent, so it may differ slightly from calculating with the unrounded monthly amount. A real lender’s payment and final total can also differ because of its day-count method, payment dates, rounding, or contract terms.

Assumptions

  • The interest rate is fixed throughout the entered interest-only period.
  • Payments are made monthly, with each payment covering that month’s interest.
  • Interest is calculated using monthly periods at APR / 12; unpaid interest is not added to principal.
  • No principal payments, fees, taxes, insurance, mortgage insurance, escrow, late charges, or payment-date adjustments are included.

How an Interest-Only Schedule Works in Excel

Excel can display a row-by-row schedule as well as calculate the summary figures above. A basic schedule needs columns for payment number, beginning balance, interest payment, principal payment, total payment, and ending balance. For a strictly interest-only month, principal payment is zero, interest is calculated from the beginning balance, and ending balance equals beginning balance.

For example, if the loan amount is in B1, annual APR as a percentage in B2, and the month number is in A6, a simple monthly interest formula could be =$B$1*$B$2/1200. The beginning balance column can refer to the original principal for every interest-only row, while the principal-payment column contains zero. If the schedule includes a later repayment phase, its payment and principal calculations need a separate formula based on the loan’s actual terms; this calculator does not model that phase.

Using cell references makes the sheet easier to audit: keep inputs in clearly labelled cells, use absolute references such as $B$1 for fixed inputs, and fill formulas down only for the intended number of months. Check that the last interest-only row has the expected balance and that the schedule’s total interest agrees with the summary, subject to rounding.

What the Results Mean

The monthly figure is the estimated interest portion for a full monthly period under the stated assumptions. It is not a principal-and-interest payment and does not show a payoff amount. The ending balance is important because an interest-only payment generally does not reduce the amount owed. If the contract later requires principal repayment, the payment can change when that phase begins.

Interest-only structures do not all have identical terms. A loan agreement may set a limited interest-only period, specify how payments are calculated, allow or restrict extra principal payments, or define a variable rate that can change. The calculator assumes a fixed rate and a level monthly period, so use the lender’s disclosure or account statement for exact contractual amounts.

Common Errors and Limits

  • Entering 0.08 instead of 8: The APR input is expressed as a percentage, so enter 8 for 8%, not 0.08.
  • Expecting the balance to fall: Interest-only payments cover interest; principal stays at its starting amount unless principal is separately paid.
  • Mixing up term and total loan term: Enter the length of the interest-only period, not necessarily the full time until the loan is due.
  • Ignoring rounding: Rounding each monthly payment to cents before multiplying can produce a small difference from an unrounded calculation or an actual billing schedule.
  • Assuming every billing period is exactly one-twelfth of a year: Actual interest may depend on dates, days in the period, balances, and the agreement’s calculation convention.

This simple model is not appropriate for a changing balance, variable APR, irregular payment dates, capitalized interest, or a schedule with a later amortizing phase. It also does not calculate fees or the total cost of borrowing beyond interest for the entered period. This result is an estimate; the lender’s disclosure gives the exact figures.

Frequently Asked Questions

How do I calculate an interest-only payment in Excel?
For monthly periods, multiply the principal by the annual APR and divide by 1,200. If principal is in B1 and APR as a percentage is in B2, use =B1*B2/1200. Format the result as currency.

Does an interest-only payment reduce the loan balance?
No, not when the payment covers only the interest due. The principal balance remains the same unless a separate principal payment is made or the loan terms change how unpaid interest is handled.

How do I show the balance in an Excel amortization schedule?
For each interest-only row, set principal paid to zero and carry the beginning balance forward as the ending balance. If principal payments begin later, use the repayment terms to calculate that phase rather than continuing the interest-only formula.

Why is my Excel result slightly different from my lender’s?
Possible causes include rounding at each payment, actual calendar days, payment timing, a different interest convention, rate changes, or fees and balance adjustments excluded from a basic worksheet.

What happens when the interest-only period ends?
The contract determines what follows. Principal repayment may begin, the balance may become due, or another arrangement may apply. Review the loan documents for the payment amount and schedule; this calculator does not predict them.

Related

Formula used
in p | Loan amount | $ | 20000 | 1 | 100000000
in apr | Interest rate (APR) | % | 8 | 0.01 | 100
in yrs | Interest-only term | years | 5 | 0.0833 | 50
out pmt | Monthly interest payment | $ | p * apr / 1200 | 2
out int | Total interest during term | $ | pmt * yrs * 12 | 2
out bal | Principal balance at end | $ | p | 2

Each line is one input (in, sel, list) or one result (out: label, unit, formula, decimals). How the calculators work

Method: the standard loan formula for this calculation, fixed rate and monthly compounding unless stated, rounded as each result states. Not financial advice; no lender, rate or product is recommended. See formulas and assumptions, the disclaimer or report an error.

More in this section

The whole Loan payments section →