HELOC Amortization Schedule in Excel: Formula and Example

An Excel HELOC amortization schedule estimates how a home equity line of credit balance changes over time as interest accrues and payments are made. Enter the amount drawn, annual interest rate, repayment term, and any extra payment to calculate a monthly payment and a month-by-month schedule; this estimate assumes a fixed rate and level payments.

Formula

The calculator uses a standard fixed-rate, fully amortizing payment formula. It treats the balance as a single loan amount and calculates a payment that would repay principal and interest in equal monthly installments.

Monthly payment: PMT = round(B × r / (1 − (1 + r)−n), 2)

Total interest estimate: I = PMT × n − B

• B is the modeled HELOC balance in dollars.
• APR is the annual percentage rate entered as a percent, such as 8 for 8%.
• r is the monthly rate, calculated as APR ÷ 1,200.
• n is the repayment period in months, calculated as years × 12.
• PMT is the monthly principal-and-interest payment, rounded to cents.
• I is total interest under the model, in dollars.

Excel’s equivalent payment function is =-PMT(rate/12, years*12, balance); the minus sign displays the payment as a positive number when the balance is entered as positive. Use a rate expressed as a decimal in that formula—for example, enter 8% or use 8%/12, not 8/12.

Worked Example

Using the calculator’s defaults: a $50,000 balance, 8% APR, and a 10-year repayment term.

1. Convert the APR to a monthly rate: r = 8 / 1,200 = 0.0066667.
2. Convert the term to months: n = 10 × 12 = 120.
3. Apply the payment formula: PMT = round(50,000 × 0.0066667 / (1 − (1.0066667)^−120), 2) = $606.64.
4. Calculate modeled total payments: $606.64 × 120 = $72,796.80.
5. Subtract the starting balance: $72,796.80 − $50,000 = $22,796.80 in estimated total interest.

The result is a mathematical illustration, not a payment quote. Actual schedules can differ because a HELOC’s rate, balance, payment rules, and timing may change.

Assumptions

• The entered balance remains outstanding at the start of the modeled repayment period.
• The interest rate is fixed for the whole term; the model does not adjust it over time.
• Payments are made monthly at the end of each period, with interest compounded monthly at APR ÷ 12.
• Payments are level and include principal and interest. No additional borrowing or extra payments occur.
• Fees, taxes, insurance, escrow, and lender-specific payment rules are not included.

Build a Month-by-Month Schedule in Excel

A useful spreadsheet has columns for payment number, opening balance, payment, interest, principal, and closing balance. Put the starting balance in the first opening-balance cell. If the payment amount is in a fixed cell such as $B$1 and the annual rate in $B$2, formulas for the first schedule row can be:

• Interest: =OpeningBalance*$B$2/12
• Principal paid: =Payment-Interest
• Closing balance: =OpeningBalance-PrincipalPaid
• Next opening balance: link it to the prior row’s closing balance.

For example, if the opening balance is in C6, payment in D6, interest in E6, principal in F6, and closing balance in G6, use =C6*$B$2/12 for interest, =D6-E6 for principal, and =C6-F6 for the closing balance. Copy the formulas down one row per month. The payment cell can use =-PMT($B$2/12,$B$3*12,$B$4) if APR, years, and starting balance are in B2:B4.

In a real amortization table, the final payment may need to be adjusted to clear a small residual balance caused by rounding. Do not let that last row imply that a lender will accept the same amount: use the lender’s statement or payoff information for actual payments.

HELOC Draw and Repayment Periods

A HELOC is a revolving line secured by home equity, rather than necessarily a one-time advance paid out like a conventional installment loan. The agreement may distinguish a period when borrowing is permitted from a later repayment period. During a draw period, a borrower may make additional draws, and required payments may be calculated differently from the fixed principal-and-interest payment in this calculator. When repayment begins, the balance and payment method depend on the account’s terms.

That distinction matters when making a spreadsheet. This calculator models only a chosen balance over a chosen repayment term. It does not determine when the draw period ends, how much is available to draw, whether the required payment is interest-only, or how a lender recalculates payments. Read the account documents and use the balance, rate, dates, and payment method applicable to the period being modeled.

When the Estimate Can Differ

Many HELOCs have variable rates. If the APR changes, interest for later months changes too, so a schedule built with one unchanging rate is not a forecast of the actual account. A more detailed worksheet can apply a separate rate for each month, but the future rates must be supplied as assumptions; the formula cannot predict them.

Other common sources of mismatch include new draws, payments made on dates other than month-end, daily interest calculations, fees, missed or extra payments, and rounding conventions. A lender may also define minimum payments differently from the standard amortizing payment. Compare the spreadsheet’s assumptions with the account’s disclosures before treating its output as a payment estimate.

Common Excel Mistakes

• Using APR as a whole number incorrectly: Excel’s PMT needs the rate per period. Convert an entered 8% APR to 8%/12, or convert 8 to 8/1200.
• Using years as the number of payments: For monthly payments, the period count is years × 12.
• Mixing signs: Excel treats money paid out and money received as opposite cash-flow directions. A negative present value or a leading minus sign before PMT can make the displayed payment positive.
• Rounding every intermediate value: Keep spreadsheet formulas at full precision and format cells to show cents. Rounding interest or balance at every row can create a cumulative difference.
• Assuming a static rate models a variable-rate line: A fixed-rate illustration cannot represent future rate changes or additional borrowing.

Frequently Asked Questions

What Excel formula calculates a HELOC payment?
For a fixed rate and equal monthly payments, use =-PMT(APR/12, years*12, balance). Enter APR as a decimal or percentage, not as a whole number without converting it.

Can Excel make a HELOC amortization schedule?
Yes. Calculate each month’s interest from the opening balance, subtract it from the payment to get principal paid, and subtract principal from the opening balance to get the closing balance. Repeat with the prior closing balance as the next opening balance.

Does this schedule include the HELOC draw period?
No. It models a fixed balance repaid through level monthly principal-and-interest payments. Draw-period access, payment requirements, and the transition to repayment depend on the account terms.

Why does my Excel balance not reach zero?
Small differences can result from rounding, an incorrect rate or period count, or a payment that does not match the schedule assumptions. The last payment may need a small adjustment in a mathematical schedule; check lender figures for an actual account.

Can I use this for a variable-rate HELOC?
Only as a fixed-rate scenario. To model changing rates, calculate each period using an explicitly entered rate for that period and follow the account’s payment rules; future rates are unknown inputs, not outputs of this formula.

This result is an estimate for education, and the lender’s disclosure gives the exact figures.

Related

• Amortization Schedule From a Monthly Payment
• Home Loan Amortization Schedule Calculator
• Amortization Schedule Free? Calculate Loan Payments

in bal | HELOC balance | $ | 50000 | 1 | 100000000
in apr | Interest rate (APR) | % | 8 | 0.01 | 100
in yrs | Repayment term | years | 10 | 0.0833 | 50
out pmt | Monthly payment | $ | round(bal * (apr / 1200) / (1 - (1 + apr / 1200) ^ -(yrs * 12)), 2) | 2
out int | Total interest | $ | pmt * yrs * 12 - bal | 2