Function Problems

How to Create an Excel Payment Schedule That Reduces the Balance

Huda Qurayshi Sep 28, 2026 869 views

Question details

The user needs to design a spreadsheet table that tracks payment dates and automatically deducts each payment amount from a running total balance.

How to Create an Excel Payment Schedule That Reduces the Balance

Product: Excel
Device & OS: not provided
Scenario: Creating an amortization schedule or loan tracker to monitor remaining balances after periodic payments are made.
Observed behavior: The user requires a structured method and specific formulas to accurately divide payments into interest and principal, and apply deductions to a declining balance.

Before you start

Gather your starting loan balance, annual interest rate, payment frequency, and expected payment amounts before setting up your schedule.

Solution 1Recommended

Build a Custom Payment Schedule Table

Set up standard financial columns and use basic mathematical formulas to calculate the remaining balance after every payment period.

A proper payment schedule separates each payment into an interest portion and a principal portion. Only the principal portion reduces the actual loan balance.

Set up table headers

Open your worksheet and type the following headers in row 1: Date, Total Payment, Interest, Principal, and Remaining Balance.

Enter the starting balance

In row 2 under the 'Remaining Balance' column, type your initial starting total (for example, 10000).

Input the first payment details

In row 3 under 'Date' and 'Total Payment', enter the date of the first payment and the total amount paid.

Calculate Interest and Principal

Calculate the Interest by multiplying the previous remaining balance by your periodic interest rate. Then, subtract the calculated Interest from the Total Payment to find the Principal.

Update the Remaining Balance

In the Remaining Balance column for row 3, enter a formula to subtract the Principal from the previous Remaining Balance (e.g., =E2-D3). Drag the formulas down to automatically calculate future payments.

Advanced Formula Usage: You can also use the built-in IPMT and PPMT functions in Excel to automatically calculate the interest and principal portions of a fixed payment.

Seamless Spreadsheet Management

Easily Create Payment Schedules with WPS Spreadsheet

WPS Spreadsheet provides powerful built-in financial functions and ready-to-use templates, making it incredibly easy to set up accurate payment schedules and track your running balance.

1. Open a new workbook: Launch WPS Spreadsheet and create a new blank document, or search for 'Amortization' in the template library.
2. Create your layout: Set up columns for Date, Payment, Interest, Principal, and Balance, and enter your initial loan amount.
3. Apply financial functions: Use the PPMT function to find the principal amount, and subtract it from the previous balance cell to get the new running total.
4. Drag to fill: Select the completed row of formulas and drag the fill handle down to populate the entire payment schedule.

Fully compatible with Microsoft Excel (.xlsx) financial formulas like PMT, IPMT, and PPMT.Access to a vast library of free, built-in loan tracking and amortization templates.Lightweight software with a familiar, easy-to-use interface that ensures a seamless transition.

microsoft office alternative - wps office

Frequently Asked Questions

What function calculates the interest portion of a payment?

You can use the IPMT function. It calculates the interest payment for a specific period of a loan or investment based on constant payments and a constant interest rate.

How do I calculate the principal portion of a payment?

The PPMT function returns the principal payment for a given period. This allows you to easily separate the principal from the interest in your payment schedule.

Why is my running balance not decreasing correctly?

Ensure you are subtracting only the principal amount from the previous balance, not the total payment amount (which includes interest). Also, verify that your cell references for the previous balance are correctly aligned.

Can I use a pre-made template instead of building it from scratch?

Yes. Both Microsoft Excel and WPS Office offer free built-in templates. In WPS Spreadsheet, you can click on 'Templates' and search for 'Loan Schedule' or 'Amortization' to find ready-to-use tables with formulas already set up.

Excel & Data Problems