Loan Amortization in Office 365: What It Can Do

Office 365—now generally called Microsoft 365—does not have a dedicated loan-amortization calculator, but Excel can calculate fixed monthly payments, total interest, and an estimated balance over time. Enter the loan amount, annual APR, and term to see the monthly payment and total interest; the estimate assumes a fixed rate and regular monthly payments.

Formula

The calculator uses the standard level-payment formula for a fixed-rate loan. It calculates the unrounded monthly payment as M = P × r / [1 − (1 + r)⁻ⁿ], where P is principal in dollars, r is the monthly interest rate as a decimal, and n is the number of monthly payments. Here, r = APR / 1200 and n = years × 12. The payment is displayed to the nearest cent. Total interest is calculated as the displayed monthly payment times the number of payments, minus the original principal.

For example, an APR of 8% means a monthly rate of 8 / 1200, or about 0.006667 (0.6667%). APR is entered as a percentage, not a decimal: enter 8 for 8%, not 0.08.

Worked Example

Using the calculator’s defaults—$20,000 principal, 8% APR, and a 5-year term—the number of monthly payments is 5 × 12 = 60, and the monthly rate is 8 / 1200 = 0.0066667.

1. Substitute into the payment formula: M = 20,000 × 0.0066667 / [1 − (1.0066667)⁻⁶⁰].
2. The unrounded payment is approximately $405.53, so the displayed monthly payment is $405.53.
3. Total interest uses the displayed payment: $405.53 × 60 − $20,000 = $4,331.80.

Assumptions

• The interest rate stays fixed for the full term.
• There are 12 equal monthly payments per year, paid at regular monthly intervals; the model treats interest as accruing monthly at APR divided by 12.
• The calculation excludes fees, taxes, insurance, PMI, escrow, and other charges because they are not inputs.
• The calculation uses a payment rounded to cents for its total-interest result. A lender’s exact schedule may handle rounding or payment dates differently.

What Loan Amortization Means

Amortization is the process of repaying a loan through scheduled payments that cover interest and reduce principal over time. With a standard fixed-rate, fully amortizing loan, the scheduled payment is level, but its components change: early payments generally devote more to interest, while later payments devote more to principal.

In a spreadsheet, an amortization schedule makes that changing split visible. Each row can show the payment number, opening balance, payment, interest for the period, principal repaid, and closing balance. A basic monthly schedule calculates interest as opening balance × monthly rate, then calculates principal as payment − interest; closing balance is opening balance − principal. The last payment may need adjustment for rounding so the balance reaches zero rather than becoming slightly negative.

Using the Result in Excel

Excel in Microsoft 365 can calculate a payment with its PMT function. For instance, with a monthly rate in B2, 60 payments in B3, and a $20,000 present value in B1, the formula is =PMT(B2,B3,-B1). The minus sign makes the returned payment positive under Excel’s cash-flow sign convention. Enter the rate per payment period and the total number of periods: for monthly payments, use APR/12 and years×12.

For a schedule, create one row per payment and use formulas referencing the previous row’s closing balance. Excel’s IPMT and PPMT functions can also separate interest and principal for a particular period, provided the rate, period number, and total periods use matching units. Function names and separators can vary with spreadsheet locale. Format money cells as currency, but remember that formatting alone does not change the underlying precision.

When This Estimate Helps—and Its Limits

This calculation is useful for understanding the payment implied by a stated principal, APR, and term, or for checking the broad shape of a repayment schedule. It does not determine whether a loan is available, how a lender will approve an application, or what a borrower can afford. Use the actual figures supplied by the lender for any real comparison or planning.

The formula is not a fit for every debt arrangement. A variable rate can change the payment or payoff path. Interest-only periods, balloon payments, irregular payment dates, daily interest accrual, deferred interest, and fees financed into the balance require additional inputs or a different schedule. If a payment is late, skipped, or changed, the actual balance can differ from a regular-payment estimate.

Common Spreadsheet Mistakes

• Mixing annual and monthly units: divide the annual APR by 12 for a monthly rate, and multiply years by 12 for the number of payments.
• Entering APR as a decimal: the formula expects 8 to represent 8%, then divides by 1200; entering 0.08 would represent 0.08%.
• Using inconsistent cash-flow signs: Excel’s PMT commonly returns a negative value if the present value is entered as positive. Reverse one sign if you want a positive displayed payment.
• Rounding every intermediate calculation: rounding monthly interest and principal to cents on each row can cause small schedule differences. Keep full precision in formulas and round displayed values; align the final payment to the remaining balance.
• Assuming total interest includes every borrowing cost: this formula counts scheduled interest only. It does not add fees, insurance, taxes, or other charges.

Frequently Asked Questions

Does Office 365 include a loan amortization calculator?
There is no dedicated loan-amortization calculator in the Office suite, but Excel can calculate payments and build a schedule using formulas or functions such as PMT, IPMT, and PPMT.

How do I calculate a monthly loan payment in Excel?
Use =PMT(APR/12, years*12, -loan_amount). For example, use an APR expressed as a decimal in the formula; if a cell contains 8 as a percentage number, divide it by 1200 instead.

Why does Excel show a negative payment?
Excel represents money paid out and money received with opposite signs. If the loan amount is positive, PMT may return a negative payment; enter the loan amount with the opposite sign or negate the PMT result for display.

Does this estimate include fees or insurance?
No. It estimates principal and interest from the inputs only. Include other costs separately if they apply, and use lender-provided disclosures for exact figures.

Why might my spreadsheet differ from a lender’s schedule?
Differences can result from payment dates, rate changes, fees, rounding, the lender’s interest-accrual method, or payment timing. The calculation is a standard monthly estimate, not a reproduction of a specific loan contract.

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

Related

• Free Loan Amortization Schedule Calculator
• Daily Loan Amortization Schedule Calculator
• Loan Amortization: How Payments Reduce Your Balance

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