An interest-only loan amortization Excel sheet can calculate the monthly interest payment, the remaining principal, and the larger payment due when principal repayment begins. Enter the loan amount, annual interest rate, and interest-only period to estimate these figures; this calculator shows the monthly interest-only payment and the principal balance at the end of that period.
Formula
For a fully drawn, fixed-rate interest-only loan with monthly payments, the monthly interest payment is the principal multiplied by the annual percentage rate divided by 12. The principal stays unchanged during the interest-only period when no principal is repaid.
- p = loan principal, in dollars.
- apr = annual percentage rate, entered as a percentage per year.
- r = monthly rate, calculated as apr / 1200.
- pmt = monthly interest-only payment, in dollars: p × r.
- yrs = interest-only period, in years.
- bal = principal balance after the interest-only period, in dollars: p.
- int = interest paid over the period, in dollars: pmt × yrs × 12.
This is the calculation to put in an Excel cell if the principal is in A1 and the annual rate in A2 as a percentage number such as 8 for 8%: =A1*A2/1200. If the rate cell contains Excel’s percentage value, such as 8%, use =A1*A2/12 instead. The two conventions differ because Excel stores 8% as 0.08.
Worked Example
Using the calculator’s default inputs—$20,000 principal, 8% annual rate, and five years interest-only—the monthly rate is 8 / 1200 = 0.0066667, or about 0.66667%.
- Monthly interest payment: $20,000 × 0.0066667 = $133.333…, rounded to $133.33.
- Principal balance after five years: no principal is paid in this model, so it remains $20,000.00.
- Estimated interest across 60 monthly payments: $133.33 × 60 = $7,999.80, using the rounded monthly payment.
The $0.20 difference from $8,000 results from rounding each monthly payment to cents. Actual lender calculations may use unrounded daily interest or another payment convention.
What an Interest-Only Amortization Schedule Shows
“Amortization” generally refers to paying down principal through scheduled payments. During an interest-only phase, payments cover interest but do not reduce principal, so a schedule may show repeated interest payments alongside a flat balance. It is still useful to call this an amortization schedule because it can also show what happens after the interest-only phase, when principal payments start.
For a simple monthly schedule in Excel, create columns for payment number, beginning balance, payment, interest, principal paid, and ending balance. During the interest-only period, calculate interest as beginning balance × monthly rate, set principal paid to zero, and carry the balance forward. If the loan is not fully drawn at the start or allows additional borrowing, this simplified balance path does not apply without extra inputs.
How to Build the Excel Schedule
For an interest-only loan that remains fully drawn and has a fixed rate, a basic spreadsheet can use these columns and formulas. For example, put principal in B1, APR as a percentage number in B2, and the monthly rate in B3 using =B2/1200.
- Beginning balance in the first row:
=$B$1. - Monthly interest:
=beginning_balance*$B$3. - Payment during the interest-only phase: equal to that row’s interest, unless the loan terms specify a different amount.
- Principal paid during that phase:
=payment-interest, which is zero when the payment exactly covers interest. - Ending balance:
=beginning_balance-principal_paid.
For later rows, link the next beginning balance to the previous ending balance. To model the repayment phase, you need its length and the repayment terms. For a fixed-rate, level-payment repayment schedule, Excel’s PMT function can calculate a payment when you supply the monthly rate, number of remaining monthly payments, and balance at conversion. Confirm the function’s sign convention: Excel commonly returns a negative value for cash paid out when the loan amount is entered as positive.
What Happens When Principal Repayment Starts
An interest-only payment is not the same as a fully amortizing payment. If a balance of $20,000 begins repayment at 8% over 10 years, the monthly principal-and-interest payment calculated at the start of that phase would be about $242.66, assuming a fixed rate and 120 equal monthly payments. That is higher than the roughly $133.33 interest-only payment because the later payment must cover both interest and principal.
This example is separate from the calculator’s five-year interest-only default: it illustrates a possible repayment phase, not a prediction of a loan’s terms. A real payment can differ if the balance changes, the rate adjusts, the repayment period differs, or the contract includes other payment rules. Check the loan disclosure for the actual schedule and any payment changes.
Common Excel Errors and Model Limits
- Mixing percentage formats: If APR is entered as 8, divide by 1200 for the monthly rate. If entered as 8%, divide the stored value by 12. Dividing 8% by 1200 understates interest by a factor of 100.
- Assuming the balance falls: Interest-only payments ordinarily do not reduce principal in this simplified model. A balance declines only if payments exceed interest or principal is paid separately.
- Rounding too early: Rounding each month can create a small difference in total interest. Keep extra decimal places for intermediate calculations and round displayed dollar amounts to cents, unless reproducing a lender’s posted schedule.
- Ignoring payment timing: This estimate assumes monthly interest based on a constant balance and a monthly rate of APR / 12. A lender may calculate interest by day, use different dates, or apply payments differently.
- Leaving out other amounts: Fees, taxes, insurance, escrow, payment caps, variable-rate changes, and optional principal payments are not included. The calculator is not a complete loan disclosure.
Assumptions
- The interest rate is fixed for the modeled interest-only period.
- Payments are made monthly, with one payment per month.
- Interest is modeled monthly at APR / 12; no daily-balance adjustment is included.
- The full loan amount is outstanding for the entire period, and no principal is paid.
- Fees, taxes, insurance, PMI, and escrow are not included.
Frequently Asked Questions
How do I calculate an interest-only payment in Excel?
For a principal in A1 and an APR entered as a number such as 8 in A2, use =A1*A2/1200. If A2 is formatted as 8%, use =A1*A2/12.
Does an interest-only payment reduce the loan balance?
Not when the payment covers only the interest. The principal remains the same until principal payments begin or extra principal is paid.
How do I calculate total interest for the interest-only period?
With a constant balance and monthly payment, multiply the monthly interest payment by the number of payments. For changing balances or rates, calculate each period’s interest separately and add the amounts.
How do I calculate the payment after the interest-only period?
Use the outstanding principal at conversion, the repayment-phase rate, and the number of remaining monthly payments in a level-payment formula such as Excel’s PMT. The result depends on those repayment terms and is not calculated by the interest-only payment alone.
Why does my Excel result differ from the lender’s figure?
Possible causes include percentage formatting, daily rather than monthly interest calculations, payment dates, rounding, fees, a changing rate, or a balance that differs from the assumed starting principal. Compare the spreadsheet’s assumptions with the lender’s disclosed terms.
This result is an estimate, and the lender’s disclosure gives the exact figures.
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-only payment | $ | p * apr / 1200 | 2 out bal | Principal due after interest-only term | $ | p | 2 out int | Total interest during interest-only term | $ | pmt * yrs * 12 | 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
- Y12 Auto Loan Rates and Monthly Payment Calculator$405.53
- Y-12 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