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:
- Convert the annual rate to a monthly rate: 8% / 12 = 0.666666…% per month, or 0.08 / 12 = 0.006666666… as a decimal.
- Calculate monthly interest: $20,000 × 0.08 / 12 = $133.333…; rounded to cents, the monthly interest payment is $133.33.
- Calculate the term in months: 5 × 12 = 60 months.
- Multiply the displayed monthly payment by the number of months: $133.33 × 60 = $7,999.80 total interest.
- 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
- Y-12 Auto Loan Rates and Monthly Payment Calculator$405.53
- Y12 Auto Loan Rates and Monthly Payment Calculator$405.53
- Shorter Auto Loan Term: Payments, Interest and Trade-Offs$405.53
- Automobile Loan Calculator: Payments and Total Interest$405.53
- Long-Term Loan: Meaning, Terms, and Repayment$405.53
- Vehicle Loan Payment Calculator and How It Works$405.53