Loan Amortization in Power BI: Formula and Example

Loan amortization in Power BI means calculating each scheduled payment’s interest and principal, then tracking the remaining balance over time. This calculator estimates the level monthly payment and total interest from a loan amount, APR, and term; Power BI can use the same formula to build a month-by-month schedule.

Formula

The monthly payment uses the standard level-payment formula: M = P × r / [1 − (1 + r)⁻ⁿ]. Here, M is the monthly payment in dollars, P is the original loan principal in dollars, r is the monthly interest rate as a decimal, and n is the number of monthly payments. Convert an APR stated as a percentage to a monthly decimal with r = APR / 1200; convert years to payments with n = years × 12.

For this calculator, total interest is estimated as I = M × n − P, where I is dollars of interest paid over the full scheduled term. The calculator carries the payment formula’s unrounded value into that total. A real payment schedule may differ slightly because lenders can round each payment and interest posting to cents, and actual payment dates can affect accrued interest.

Worked Example

Using the calculator’s defaults—$20,000 principal, 8% APR, and a five-year term:

1. Monthly rate: 8 / 1200 = 0.0066667.
2. Number of payments: 5 × 12 = 60.
3. Monthly payment: 20,000 × 0.0066667 / [1 − (1 + 0.0066667)⁻⁶⁰] = $405.53 when rounded to cents.
4. Total interest using the calculator’s unrounded payment: approximately $4,331.77.

The displayed payment is rounded to cents, while the total-interest output uses the unrounded formula result. Therefore, multiplying the displayed $405.53 by 60 and subtracting $20,000 gives $4,331.80, a three-cent difference. This is a rounding effect, not a separate fee. A schedule that rounds each payment and interest entry can show a slightly different final installment.

Building an Amortization Schedule in Power BI

The calculator gives the fixed monthly payment and whole-term interest estimate; an amortization schedule adds one row per payment period. In Power BI, a common approach is to create a month-number table and calculate opening balance, interest, principal paid, and closing balance for every month.

• Opening balance: the original principal in month one; thereafter, the prior month’s closing balance.
• Interest: opening balance multiplied by the monthly rate.
• Principal paid: scheduled payment minus that period’s interest.
• Closing balance: opening balance minus principal paid.

For example, in the first month of the sample loan, interest is about $133.33 ($20,000 × 8% / 12). The remainder of the $405.53 payment—about $272.20—reduces principal. In a later month, the balance is lower, so interest is lower and more of the unchanged payment goes toward principal. This changing split is the central feature of amortization.

For a reusable report, loan amount, APR, and term can be represented as user-controlled parameters or fields in a loan table. A month table can be related to each loan using a loan identifier and period number. DAX measures are useful for values that should respond to report filters; calculated columns or a prepared schedule table can be easier when each period’s balance depends on the prior period. Avoid assuming that a measure can simply refer to the prior row without defining the period context and loan grouping.

When to Use It and How to Read the Results

Use a payment estimate to understand the arithmetic of a fixed-rate, level-payment loan, or use a period-by-period schedule to visualize how principal and interest change. In a Power BI report, useful views include a monthly balance line, stacked principal-and-interest columns, and cards for payment and estimated interest. Label the APR, term, payment frequency, and calculation assumptions beside the visuals so readers know what the numbers represent.

The headline payment is not necessarily the complete amount due on a billing statement. The calculator models principal and interest only. It does not calculate fees, taxes, insurance, mortgage insurance, escrow, or other charges. It also assumes regular monthly payments and a fixed rate; variable rates, irregular dates, skipped payments, and extra payments need additional inputs and logic.

Common Power BI and Calculation Pitfalls

• Mixing percentage formats: 8% is 0.08 as a decimal, but the calculator accepts 8 as a percentage and divides by 1200 to obtain a monthly decimal. Dividing 8 by 12 without converting percent first produces an incorrect rate.
• Using the wrong period count: a five-year monthly loan has 60 payments, not five. For a different payment frequency, both the periodic rate and the number of periods must match that frequency.
• Rounding too early: rounding the monthly rate or intermediate balance repeatedly can accumulate differences. Decide whether the model follows a mathematical estimate or a lender-style cents-rounded schedule, and document that choice.
• Incorrect row context: in a multi-loan report, a balance calculation must stay within the correct loan and be ordered by payment period. Otherwise, one loan’s prior balance may be carried into another loan.
• Floating-point residue: formulas may produce a tiny nonzero balance after the final period. A presentation can format near-zero values as zero, but do not hide a material remaining balance.

Assumptions

• The interest rate is fixed for the full term.
• Payments are equal, made monthly, and assumed to occur at regular period ends.
• Interest is compounded monthly using APR / 12.
• Fees, taxes, insurance, PMI, escrow, late charges, and extra payments are not included.

Frequently Asked Questions

Can Power BI create a loan amortization schedule?
Yes. Model one record per loan and payment period, then calculate interest, principal paid, and closing balance in a consistent period order. DAX or a prepared schedule table can be used, depending on how the report needs to respond to filters.

What is the difference between a payment calculator and an amortization schedule?
A payment calculator estimates the regular payment and often total interest. An amortization schedule shows how each payment is split between interest and principal and how the outstanding balance changes over time.

Why does the final balance in Power BI not equal exactly zero?
Common causes include rounding, a mismatched payment count, an incorrect monthly rate, or period rows that are not sorted and grouped by loan. Real contracts may also adjust a final payment to account for rounding or timing.

Does this calculation include extra payments?
No. The estimate assumes the scheduled level payment only. To model extra payments, add an extra-payment input by period and reduce the balance by that amount, while checking that the schedule does not continue past payoff.

Is APR always the same as the interest rate used in an amortization formula?
Not necessarily. This calculator treats the entered APR as the nominal annual rate divided by 12 and does not model fees or other APR components. Use the applicable rate and calculation convention supplied in the lender’s loan disclosure.

This result is an estimate; the lender’s disclosure gives the exact figures.

Related

• Term Loan Journal Entry: Record Borrowing and Payments
• Short-Term Personal Loan Payment Calculator
• Term Loan EMI Calculator in Excel: Formula and Example

in p | Loan amount | $ | 20000 | 1 | 100000000
in apr | Interest rate (APR) | % | 8 | 0.01 | 100
in yrs | Loan term | years | 5 | 0.0833 | 50
out pmt | Monthly payment | $ | p * (apr / 1200) / (1 - (1 + apr / 1200) ^ -(yrs * 12)) | 2
out int | Total interest | $ | pmt * yrs * 12 - p | 2