Loan Calculations from First Payment to Payoff

Understanding how loan payments are structured can feel overwhelming but Excel gives us powerful tools to make these calculations quick and simple. Whether we’re evaluating monthly payments, interest breakdowns, or savings from early payoff, Excel’s financial functions (PMT, IPMT, PPMT, CUMPRINC, CUMIPMT) help us every step of the way.

This post is brought to you by Finatical Software

• QuickBooks Online users! We know how essential Excel is for creating custom reports in the exact format you need. With Finatical, you can pull live, refreshable QBO financial data directly into your favorite Excel reports, no more copy, paste, export, repeat. It’s fast, easy, and built to streamline your workflow.
• A FREE TRIAL is available, so check it out risk-free today!

Video

Calculating Loan Details in Excel

In this guide, we’ll take a methodical look at how to use these functions to compute total payment amounts, the interest portion, and the principal portion.

Calculate Monthly Payment with PMT

The PMT function returns the periodic payment amount for a loan or investment.

PMT(rate, nper, pv, [fv], [type])

• rate – Interest rate per period (e.g., annual rate ÷ 12 for monthly payments).
• nper – Total number of payment periods.
• pv – Present value (principal amount or lump sum).
• fv (optional) – Desired future value at the end of the term (defaults to 0).
• type (optional) – Payment timing: 0 = end of period (default); 1 = beginning of period.

So, let’s say we’re taking out a loan with the following terms:

• Loan Amount: $20,000
• Annual Interest Rate: 5%
• Term: 5 years

To calculate the monthly payment, we’ll use the PMT function, but with one crucial caveat: the time units must match. Since we want monthly payments, we must convert the annual rate and number of periods to monthly. We can divide the annual rate by 12 and multiple the number of years by 12 to accomplish this:

=PMT(.05/12, 5*12, 20000)

This tells Excel to calculate monthly payment given the loan terms. Excel assumes cash flow basics: money received (like a loan) is positive, and money paid out (monthly payments) is negative. We can wrap the result in ABS() to keep it positive, or add a minus sign up front.

Handling Future Balances or Balloon Payments

If we anticipate a balloon payment of $5,000, or leave a balance (future value or fv), include that in the PMT function:

=PMT(.05/12, 60, 20000, -5000)

This reduces our monthly amount because the full principal isn’t paid off during the period.

Reverse Compute Savings Goals

Excel can also help us answer: “How much do we need to save monthly to hit a specific goal?”

=PMT(.04/12, 3*12, 0, -20000)

This tells us the monthly contribution required to save $20,000 in 3 years at 4% annual growth.

Breaking Payments Into Interest & Principal

Use IPMT to Calculate Interest Portion

The IPMT function returns the interest portion of a payment for a specific period within a loan or investment schedule.

IPMT(rate, per, nper, pv, [fv], [type])

• rate – Interest rate per period (e.g., annual rate ÷ 12 for monthly payments).
• per – The period number (1 – nper) for which to calculate interest.
• nper – Total number of payment periods.
• pv – Present value (principal amount or lump sum).
• fv (optional) – Desired future value at the end of the term (defaults to 0).
• type (optional) – Payment timing: 0 = end of period (default); 1 = beginning of period.

For example, given a loan:

• Loan: $10,000
• Term: 12 months
• Interest: 5%

To get the interest portion of the total monthly payment for month 1, use:

=IPMT(.05/12, 1, 12, 10000)

As time progresses, more of the payment goes toward principal.

Calculate Principal Portion with PPMT

The PPMT function returns the principal portion of a payment for a specific period within a loan or investment schedule.

PPMT(rate, per, nper, pv, [fv], [type])

• rate – Interest rate per period (e.g., annual rate ÷ 12 for monthly payments).
• per – The period number (1 – nper) for which to calculate principal.
• nper – Total number of payment periods.
• pv – Present value (principal amount or lump sum).
• fv (optional) – Desired future value at the end of the term (defaults to 0).
• type (optional) – Payment timing: 0 = end of period (default); 1 = beginning of period.

For example, given a loan:

• Loan: $10,000
• Term: 12 months
• Interest: 5%

To get principal portion of the monthly payment for month 1, use:

=PPMT(.05/12, 1, 12, 10000)

If we want, we can double-check that the interest portion plus the principal portion is equal to the total monthly payment:

=IPMT(...) + PPMT(...)

The IPMT and PPMT function compute the interest and principal portions for a single period. But, what if we wanted to compute the cumulative amount for interest or principal at any given period? Well, as you may suspect, that leads us to the next two functions.

Computing Cumulative Interest and Principal

The CUMIPMT and CUMPRINC function compute cumulative interest and principal at any given period.

Use CUMPRINC Function

The CUMPRINC function returns the total principal paid on a loan between two specified payment periods.

CUMPRINC(rate, nper, pv, start_period, end_period, type)

• rate – Interest rate per period (e.g., annual rate ÷ 12 for monthly payments).
• nper – Total number of payment periods for the loan.
• pv – Present value (principal amount borrowed).
• start_period – First period in the range for which to calculate cumulative principal (integer ≥ 1).
• end_period – Last period in the range (integer ≤ nper).
• type – Payment timing: 0 = end of period (most common); 1 = beginning of period.

For example, given a loan:

• Interest: 5%
• Loan: $10,000
• Term: 12 months

Create a formula that computes how much principal has been paid between month 1 and month 6:

=CUMPRINC(.05/12, 12, 10000, 1, 6, 0)

And we can do something similar for cumulative interest.

Use CUMIPMT Function

The CUMIPMT function returns the total interest paid on a loan between two specified payment periods.

CUMIPMT(rate, nper, pv, start_period, end_period, type)

• rate – Interest rate per period (e.g., annual rate ÷ 12 for monthly payments).
• nper – Total number of payment periods in the loan.
• pv – Present value; the principal amount borrowed.
• start_period – First period in the range for which to calculate cumulative interest (integer ≥ 1).
• end_period – Last period in the range (integer ≤ nper).
• type – Payment timing: 0 = end of period (default); 1 = beginning of period.

For example, given a loan:

• Interest: 5%
• Loan: $10,000
• Term: 12 months

Create a formula that computes how much principal has been paid between month 1 and month 6:

=CUMIPMT(.05/12, 12, 10000, 1, 6, 0)

Summary

Using Excel’s financial functions, we can:

• Calculate monthly payments with PMT
• Identify interest vs. principal using IPMT and PPMT
• Measure cumulative values with CUMPRINC and CUMIPMT

These techniques unlock a deeper understanding of loans and empower us to make better financial decisions right in Excel.

Download the Excel File

FAQs

What is the PMT function used for in Excel?

It calculates the periodic payment amount for a loan based on constant payments and constant interest rate.

How is the IPMT function different from PMT?

While PMT returns the total payment, IPMT isolates just the interest portion of that payment for a specific period.

Why is the payment returned by PMT always negative?

Excel financial functions follow cash flow conventions, where funds received are positive and funds paid out are negative.

How can I make PMT return a positive number?

Use a minus sign in front of the formula or wrap it with ABS() to display the result as positive.

What does it mean if I enter a future value in PMT?

It means you’re not fully paying off the loan during the period, leaving a remaining balance or balloon payment at the end.

What’s a spill range and how do I use it?

Functions like SEQUENCE return a dynamic array (spill range) that fills multiple cells; use # to reference it (e.g., A15#).

Can I use these functions for savings instead of loans?

Yes. Set the present value to 0 and make future value positive in PMT() to represent savings goals.

What does the type argument mean in these functions?

Type 0 means payments are made at end of each period; type 1 means payments happen at the beginning.

How can I calculate how much principal I’ve paid so far?

Use CUMPRINC() with start and end period to find the total principal paid up to that point.

 Disclosures and Notes

• 100% of sponsor proceeds fund the Excel University scholarship and financial aid programs … thank you!
• This is a sponsored post for Finatical Software. All opinions are my own. Finatical Software is not affiliated with nor endorses any other products or services mentioned.