An interest-only amortization calculator in Excel estimates the monthly interest payment during an interest-only period and shows how payments change when principal repayment begins. Enter the loan amount, annual interest rate, and term; the calculator below gives the interest-only payment and the later fully amortizing payment under a stated repayment schedule.
Formula
This calculator models a fixed-rate loan with monthly payments and an interest-only period followed by equal monthly principal-and-interest payments. During the interest-only period, the monthly payment is the principal multiplied by the monthly rate. After that period, the remaining balance is amortized over the months left in the stated total term.
- Monthly rate: r = APR / 1200, where APR is the annual percentage rate entered as a percentage.
- Interest-only payment: I = P × r, where P is the starting principal in dollars and I is the monthly payment in dollars.
- Later amortizing payment: A = round((P × r) / (1 − (1 + r)−n), 2), where n = (T − O) × 12 months.
- Estimated total interest: I × O × 12 + A × (T − O) × 12 − P. This assumes all principal is repaid by the end of the stated term and uses the displayed, cent-rounded payments.
Here, P is loan amount ($), APR is annual interest rate (% per year), O is the interest-only period (years), T is total loan term (years), r is monthly interest rate (decimal), I and A are monthly payments ($), and n is the number of amortizing monthly payments. The input limits ensure the interest-only period is shorter than the total term, so at least one month remains for principal repayment.
Worked Example
Using the calculator’s default values—$200,000 principal, 6% APR, a 5-year interest-only period, and a 30-year total term:
- Monthly rate: 6 / 1200 = 0.005, or 0.5% per month.
- Interest-only payment: $200,000 × 0.005 = $1,000.00 per month.
- Remaining repayment term: (30 − 5) × 12 = 300 months.
- Amortizing payment: round(($200,000 × 0.005) / (1 − (1.005)−300), 2) = $1,288.60 per month.
- Balance when principal repayment begins: $200,000.00, since the model assumes interest-only payments do not reduce principal.
- Estimated total interest: $1,000 × 60 + $1,288.60 × 300 − $200,000 = $246,580.00.
Assumptions
- The rate stays fixed for the full modeled term.
- Payments are made monthly at the end of each monthly period; the first modeled payment is one month after the loan begins.
- Interest accrues monthly using APR divided by 12. This is a simplified monthly-rate convention, not a daily-interest calculation.
- The loan begins fully funded, and interest-only payments cover interest but do not reduce principal.
- Fees, taxes, insurance, mortgage insurance, escrow, late charges, payment changes, and extra principal payments are not included.
How to Build the Same Model in Excel
Excel can reproduce the calculation with cell references. For example, place the loan amount in B2, APR in B3 as a percentage such as 6%, the interest-only years in B4, and the total term in B5. The formulas below use those cells:
- Interest-only monthly payment:
=B2*B3/12 - Later monthly payment:
=ROUND(-PMT(B3/12,(B5-B4)*12,B2),2) - Starting balance at the end of the interest-only period:
=B2 - Estimated total interest:
=(B2*B3/12)*B4*12+ROUND(-PMT(B3/12,(B5-B4)*12,B2),2)*(B5-B4)*12-B2
Excel’s PMT function ordinarily returns a negative number when the loan principal is entered as a positive value, because it treats the payment as cash flowing out. The minus sign before PMT makes the displayed payment positive. If your APR cell contains the number 6 rather than a percentage-formatted 6% (stored as 0.06), use B3/1200 in place of B3/12.
When to Use It and How to Read the Results
This model is useful for understanding the payment transition in a loan that has an explicitly defined interest-only phase followed by scheduled principal repayment. The interest-only amount is not the payment needed to clear the loan: it covers modeled interest alone. The later payment is higher because it must cover interest and repay the full original principal over the shorter remaining period.
In the example, the payment rises from $1,000.00 to $1,288.60 after five years. The balance remains $200,000 at that point, so the later payment is calculated over 25 years rather than the original 30. If the rate, interest-only duration, or remaining term changes, the payment and total interest change as well.
Use the result as a cash-flow illustration, not as a complete loan quote. Check whether the actual contract uses a changing rate, a different payment frequency, a balloon payment, or a different principal schedule. Enter the actual rate and dates or figures supplied by the lender; do not treat the example defaults as a rate estimate.
Common Excel Mistakes and Model Limits
- Entering the APR in the wrong form: A cell containing 6% is stored as 0.06, while a cell containing 6 is stored as six. These require different formulas, as shown above.
- Using the full term for the second payment: The amortizing payment must use only the months after the interest-only phase. Using all 30 years in the example understates the payment after the first five years.
- Assuming interest-only means principal is deferred without consequence: The balance does not shrink under this model. The borrower still owes the original principal when scheduled amortization begins.
- Rounding too early: The monthly payment is rounded to cents before estimating total interest. Actual servicing calculations may round each period differently, so small differences can occur.
- Modeling a variable rate as fixed: A fixed APR input cannot represent future rate changes. A variable-rate loan needs a period-by-period schedule with the applicable rate for each period.
This simplified calculator does not model origination costs, payment dates, irregular first periods, daily accrual, missed payments, or contract-specific rules. It also assumes the interest-only period is followed immediately by level monthly payments and that the loan is completely repaid at term end.
Frequently Asked Questions
Does an interest-only payment reduce the loan balance?
No, not under the interest-only model here. The payment covers the period’s interest, leaving principal unchanged. A loan contract could have different terms, so check its payment schedule.
Why does the payment increase after the interest-only period?
The original principal must then be repaid over the months remaining in the term. A shorter repayment window generally requires a larger monthly principal-and-interest payment than spreading repayment across the entire term.
Can I use Excel’s PMT function for an interest-only loan?
PMT calculates a level payment that amortizes a balance over a specified number of periods. For the interest-only phase, calculate principal × monthly rate; use PMT for the later amortization phase with only the remaining months.
What if the interest-only period equals the entire term?
This calculator requires some time after the interest-only period for principal repayment. If the whole term is interest-only, it would not calculate a fully amortizing later payment; the principal would remain due unless a separate repayment or payoff arrangement applied.
Is the total interest figure exact?
No. It is an estimate based on the inputs and assumptions listed above. Contract calculations can differ because of rate changes, dates, rounding conventions, fees, or other terms. The result is an estimate, and the lender’s disclosure gives the exact figures.
Related
Formula used
in p | Loan amount | $ | 200000 | 1 | 100000000 in apr | Interest rate (APR) | % | 6 | 0.01 | 100 in io | Interest-only period | years | 5 | 0 | 49.9167 in yrs | Total loan term | years | 30 | 0.0833 | 50 out iopmt | Monthly interest-only payment | $ | p * apr / 1200 | 2 out amtpmt | Monthly payment after interest-only period | $ | round((p * apr / 1200) / (1 - (1 + apr / 1200) ^ -((yrs - io) * 12)), 2) | 2 out bal | Principal balance when repayment begins | $ | p | 2 out int | Estimated total interest over full term | $ | iopmt * io * 12 + amtpmt * (yrs - io) * 12 - 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