Loan Amortization Schedule Excel: South Africa Guide

To make a loan amortization schedule in Excel for a South African loan, enter the amount borrowed, annual interest rate and term, calculate the regular instalment with PMT, then split each instalment into interest and principal. The schedule below uses a clearly illustrative R1,000,000 loan at 11% a year over 20 years; your lender’s rate, calculation method, fees and payment dates determine the real figures.

What an amortization schedule shows

An amortization schedule, also called a repayment schedule, tracks a loan from its opening balance to its final payment. Each period shows the starting balance, payment, interest charged, amount that reduces the debt and closing balance. It helps explain why the balance falls slowly at first on a long loan: when the balance is high, a larger share of the payment goes towards interest.

For a standard repayment loan with a fixed rate and equal monthly payments, the payment is calculated so that the balance reaches zero after the agreed number of periods. The model is useful for understanding the mechanics and comparing assumptions. It is not a substitute for a lender’s repayment quotation or account statement, particularly when the rate can change or interest is calculated daily.

Information to collect before building the workbook

Use the loan offer or current lender statement rather than relying on an online estimate. Check that the figures you enter refer to the same loan and that you know whether costs are included in the amount financed. A practical input list is:

Principal: the amount actually borrowed, or the financed balance if eligible costs have been added to the loan.
Interest rate: the annual rate and whether it is quoted as nominal or effective, fixed or variable.
Term and frequency: for example, 20 years with monthly payments, which gives 240 monthly periods.
Payment timing: whether instalments are due at the beginning or end of each period. The example assumes end-of-month payments.
Other charges: any fees, insurance premiums or other amounts that affect the payment or total cost. These may be charged separately rather than included in the loan balance.

For a South African home loan, ask the lender how its quoted annual rate is applied, how rate changes affect the instalment, and whether interest is based on the daily outstanding balance. The answers matter because a simple monthly model may not reproduce the lender’s exact cents or statement dates.

Set up the Excel inputs and calculate the payment

In a blank worksheet, enter labels in column A and values in column B. For this example, use B2 for principal (1000000), B3 for nominal annual interest rate (11%), B4 for term in years (20), and B5 for the calculated monthly payment. Format the amount cells as South African rand if you want the sheet to display R values; number formatting changes presentation, not the underlying calculation.

With principal in B2, the nominal annual rate in B3, years in B4 and the payment result in B5, enter =-PMT(B3/12,B4*12,B2) in B5. The minus sign makes the payment display as a positive amount because Excel treats money paid out and money received as opposite cash-flow directions. For the stated assumptions, the result is approximately R10,321.76 per month. The calculation uses 11% divided by 12 and 20 multiplied by 12, or 240 monthly payments.

That payment is an estimate from the stated assumptions, not a promise that a South African lender will debit exactly R10,321.76. A quoted rate may be applied differently, the payment date may affect interest, and charges may be separate. Keep full precision in the calculation and display two decimal places; rounding every intermediate value can create a residual balance near the end.

If the lender quotes an effective annual rate rather than a nominal annual rate, dividing it by 12 does not give the equivalent monthly rate. With the effective annual rate in B3 and a monthly-rate result in B6, enter =(1+B3)^(1/12)-1 in B6. At an 11% effective annual rate, this returns approximately 0.8735% per month. Use the rate convention specified in the loan documents, and do not substitute this conversion unless it applies to the quoted rate.

Build the monthly amortization table

Starting in row 7, create these headers: A7 Period, B7 Opening balance, C7 Payment, D7 Interest, E7 Principal repaid and F7 Closing balance. Row 8 is the first payment. With the original loan amount in B2, annual nominal rate in B3 and payment in B5, enter each formula below in the stated cell:

With the first-period opening balance in B8 and the principal in B2, enter =B2 in B8. It returns R1,000,000.00.
With the first-period payment in C8 and the fixed payment in B5, enter =$B$5 in C8. It returns approximately R10,321.76.
With opening balance in B8 and nominal annual rate in B3, enter =B8*$B$3/12 in D8. It returns approximately R9,166.67 in first-month interest.
With payment in C8 and interest in D8, enter =C8-D8 in E8. It returns approximately R1,155.09 of principal repaid.
With opening balance in B8 and principal repaid in E8, enter =B8-E8 in F8. It returns approximately R998,844.91 as the closing balance.

For the next row, with the previous closing balance in F8, enter =F8 in B9; it returns approximately R998,844.91 as the next opening balance. With B9 as the second-period opening balance and B3 as the nominal annual rate, enter =B9*$B$3/12 in D9; it returns approximately R9,156.08 of interest. Copy the payment, principal and closing-balance formulas from row 8 into row 9, then fill the schedule down for 240 periods. Excel adjusts relative references such as B8 to B9 while the dollar signs keep the payment reference fixed.

Enter the period number in A8, then continue the sequence down to 240, or type the period numbers manually. You can check the first row: R9,166.67 interest plus R1,155.09 principal is approximately R10,321.76; subtracting the principal repaid from R1,000,000 gives approximately R998,844.91. Small differences of a cent can arise from displayed rounding.

Understand the South African rate and cost details

Many South African borrowers encounter a variable home-loan rate linked to a lender’s benchmark, while other loans may have fixed or differently structured rates. If a variable rate changes, the original schedule stops being a forecast of actual payments unless you update the rate and recalculate. Depending on the loan terms, a lender may change the instalment, the remaining repayment period, or both. Confirm the lender’s method rather than assuming the existing payment remains unchanged.

A monthly spreadsheet that estimates interest as opening balance multiplied by annual rate divided by 12 is transparent and useful for a basic illustration. It can differ from a lender’s calculation where interest accrues daily against the outstanding balance, payment dates vary, or the rate changes during a period. Initiation or service fees, credit-life premiums and other charges can also affect the total amount paid without appearing as principal-and-interest in this table. Check the quotation and account statement to see which amounts are included.

Keep the principal-and-interest schedule separate from costs that are billed outside the loan. If a charge is financed, it may increase the starting principal; if it is paid upfront or debited separately, adding it to the amortization balance would misrepresent the loan. This distinction is especially useful when comparing a spreadsheet estimate with a lender’s monthly debit order.

Model extra payments and changing circumstances carefully

An extra payment can reduce the balance faster, but the schedule needs to represent how the lender applies it. If it is applied directly to principal, add a separate extra-principal column and subtract that amount from the closing balance. Confirm whether extra repayments are allowed, whether restrictions or charges apply, and whether the lender reduces the instalment or shortens the term. Those details depend on the contract and lender.

For a changing-rate loan, use a period-by-period rate column rather than one annual rate fixed for the whole schedule. Calculate each period’s interest using that period’s rate and the balance on which interest is charged. If payment amounts are recalculated after a rate change, use the lender’s updated instalment and remaining term; do not keep the original PMT amount and present the resulting projection as the lender’s official schedule.

It can be useful to make a second scenario sheet for a different rate or extra-payment assumption. Label it clearly as an estimate and preserve the original lender inputs. A scenario is a way to understand sensitivity—for example, how a higher rate affects the estimated payment—not a prediction of future South African interest rates or a recommendation to borrow or repay in a particular way.

Common spreadsheet errors and checks

Mixing annual and monthly units: a monthly schedule needs a monthly rate and number of monthly periods. Do not use 11 as the rate or 20 as the number of periods in the PMT calculation.
Reversing the sign: Excel’s cash-flow sign convention can make PMT show a negative amount. A leading minus sign is appropriate when you want the scheduled payment displayed as positive.
Using the wrong rate basis: nominal annual rates and effective annual rates need different conversions. Follow the lender’s stated convention.
Rounding every row: display cents but let formulas use their full stored precision. The final balance may otherwise fail to reach zero cleanly.
Forgetting the final payment adjustment: a real lender may adjust the final instalment for cents, fees, timing or its own interest calculation. A small spreadsheet remainder is a reason to inspect assumptions, not to force a false match.
Comparing unlike totals: a schedule of principal and interest is not necessarily the same as total ownership or borrowing cost. Compare fees and separate premiums on a consistent basis.

Review a few rows manually and check that payment equals interest plus principal, the closing balance equals opening balance less principal repaid, and the balance generally declines for a standard fully amortizing loan. If interest exceeds the payment in a period, the balance can rise; investigate the rate, payment amount and loan terms rather than assuming the formula is broken.

When to use a template, calculator or lender statement

A self-built Excel workbook is best when you want to see the calculation, adapt assumptions, or keep a transparent record. A lender’s statement is the appropriate source for actual posted interest, fees, payment dates and current balance. A reputable online calculator can provide a quick cross-check, but only if its rate convention, payment frequency and fee assumptions match yours.

In Microsoft 365 for Windows, the formulas described here use standard Excel functions and also work in current Excel for Mac and Excel for the web. The ribbon layout can differ slightly on Mac and in the browser, but the worksheet formulas are the same. If Excel rejects a formula separator, your regional settings may use a different list separator; the examples here use the standard comma syntax.

This article is for spreadsheet education, not financial, tax, investment or legal advice. Before acting on a payment estimate, verify the actual loan terms, fees and interest method with the lender or a qualified professional.

Frequently Asked Questions

How do I calculate a monthly loan payment in Excel?
For a fixed-rate loan with monthly repayments, use PMT with the monthly interest rate, total number of monthly payments and loan principal. For example, the illustrative setup above uses 11% divided by 12, 240 payments and R1,000,000, giving an estimated payment of about R10,321.76.

Does Excel’s schedule match a South African bank’s home-loan statement?
Not necessarily. A basic schedule may use a monthly-rate approximation, while the lender can calculate interest using daily balances, actual payment dates, rate changes and contractual charges. Treat Excel as an estimate and use the lender’s statement for posted account figures.

Should I include South African home-loan fees in the principal?
Only include a cost in the opening balance if it is actually financed as part of the loan. Upfront costs and recurring charges billed separately should be tracked separately, or the principal-and-interest schedule will not describe the same balance as the lender’s account.

How do I show an extra repayment in the schedule?
Add a distinct extra-principal amount for the relevant period and reduce the closing balance by both scheduled principal and the extra amount, if the lender applies it that way. Check the loan agreement and lender first, because repayment rules and the effect on future instalments or term can vary.

Why does my final Excel balance not equal exactly zero?
Displayed rounding, rounded formulas, a different rate basis, payment timing or a lender’s daily-interest method can leave a small residual. Keep full precision in calculations and compare your assumptions with the lender’s terms; actual final payments may be adjusted.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.