Function Problems

How to Create a Horizontal Loan Amortization Table in Excel

Adam Davis Sep 30, 2026 869 views

Question details

The user wants to generate a loan amortization schedule where the payment numbers, principal, and interest calculations automatically spill horizontally across columns instead of vertically.

How to Create a Horizontal Loan Amortization Table in Excel

Product: Excel
Device & OS: not provided
Scenario: Setting up a dynamic financial model or loan tracking schedule that requires a horizontal layout for better data visualization or integration with other timeline-based financial metrics.
Observed behavior: The user needs to use dynamic array formulas to dynamically generate horizontal payment sequences and calculate corresponding amortization values across columns automatically.

Before you start

Ensure you are using Microsoft 365, Excel 2021, or a modern spreadsheet application that fully supports dynamic array formulas and the SEQUENCE function.

Solution 1Recommended

Use Dynamic Array Formulas to Build the Horizontal Table

Use the SEQUENCE function alongside PMT, IPMT, and PPMT functions with spilled array references to automatically populate your horizontal schedule.

Dynamic-array formulas allow a single formula to return values into multiple cells automatically. By generating a horizontal sequence of payment periods, you can reference this expanding array in your financial formulas to calculate the entire loan schedule instantly.

Set up the loan variables

Enter your base loan variables in a vertical list. For example, enter the Interest Rate in B1, Total Years in B2, Payments per Year in B3, and the Loan Amount in B4.

Generate the horizontal payment sequence

Select the cell where you want the payment numbers to start (e.g., B6) and enter the formula =SEQUENCE(,B2*B3). This creates a horizontal row of numbers from 1 up to the total number of payments.

Apply spilled array references in financial functions

In the row below (e.g., for Principal Payments), use the PPMT function and reference the entire sequence using the spill operator (#). For example, your period argument should be B6# instead of a standard cell reference.

Calculate interest and total payment

Similarly, use the IPMT and PMT functions in the subsequent rows, again using B6# as the period argument. The formulas will automatically calculate and spill horizontally to match your payment periods.

Use Dynamic Array Formulas to Build the Horizontal Table

Proper Referencing: When referencing fixed variables like the loan amount or interest rate, use absolute references (e.g., $B$4). For the dynamic periods, rely on the spilled range reference (e.g., B6#).

Advanced Financial Modeling

Create Dynamic Financial Models in WPS Spreadsheet

WPS Spreadsheet fully supports advanced financial functions including PMT, IPMT, and PPMT, allowing you to easily build complex loan amortization schedules. It offers seamless compatibility with Excel formulas so your dynamic models work perfectly.

1. Open a new workbook: Launch WPS Spreadsheet and create a blank workbook for your financial model.
2. Enter your variables: Input your loan amount, interest rate, and term duration into distinct, clearly labeled cells.
3. Apply financial formulas: Use WPS Spreadsheet's built-in PPMT, IPMT, and PMT functions to calculate your principal, interest, and total payments horizontally.
4. Save and share: Save your completed amortization table in .xlsx format for easy sharing and perfect cross-platform compatibility.

Free and lightweight office suite for Windows, Mac, and LinuxFully compatible with Microsoft Excel (.xlsx) formats and standard formulasComprehensive support for advanced financial and statistical functionsFamiliar interface ensures zero learning curve when switching

microsoft office alternative - wps office

Frequently Asked Questions

What does B6# mean, and why is it used instead of B$6?

The hashtag (#) is the spilled range operator. B6# refers to the entire dynamic array of values that spills outward from cell B6. You use it instead of an absolute reference (B$6) because the size of the array can change based on the loan term, and the hashtag ensures the formula always captures the complete dynamic range.

What is the purpose of B6#/B6# in these array formulas?

Since dividing a number by itself equals 1, the expression B6#/B6# generates a horizontal array of ones that perfectly matches the length of your spilled payment range. This is an advanced technique used in matrix operations or conditional arrays to force uniform calculations across the entire dynamic length.

Do the PMT, IPMT, and PPMT formulas propagate to the right automatically?

Yes, if you use a spilled array reference (like B6#) as the period argument in your PMT, IPMT, or PPMT formulas, the results will automatically propagate (or "spill") to the right, generating calculations for every single payment period generated by your SEQUENCE function.

How do relative and absolute references work with spilled arrays?

When building formulas that spill to the right, you must use absolute references (like $B$1, $B$2) for static variables such as the interest rate and loan amount so they do not shift. The dynamic part of the calculation relies entirely on the spilled reference (B6#), which inherently handles the column-by-column progression.

Excel & Data Problems