N
TruthVerse News

How do I create a loan payment schedule in Excel?

Author

Christopher Duran

Updated on February 23, 2026

How do I create a loan payment schedule in Excel?

Loan Amortization Schedule
  1. Use the PPMT function to calculate the principal part of the payment.
  2. Use the IPMT function to calculate the interest part of the payment.
  3. Update the balance.
  4. Select the range A7:E7 (first payment) and drag it down one row.
  5. Select the range A8:E8 (second payment) and drag it down to row 30.

Then, how do I create a loan repayment schedule in Excel?

Loan Amortization Schedule

  1. Use the PPMT function to calculate the principal part of the payment.
  2. Use the IPMT function to calculate the interest part of the payment.
  3. Update the balance.
  4. Select the range A7:E7 (first payment) and drag it down one row.
  5. Select the range A8:E8 (second payment) and drag it down to row 30.

One may also ask, how do I calculate a loan payment in Excel? Excel PMT Function

  1. Summary.
  2. Get the periodic payment for a loan.
  3. loan payment as a number.
  4. =PMT (rate, nper, pv, [fv], [type])
  5. rate - The interest rate for the loan.
  6. Version.
  7. The PMT function can be used to figure out the future payments for a loan, assuming constant payments and a constant interest rate.

People also ask, how do I set up a loan payment schedule?

It's relatively easy to produce a loan amortization schedule if you know what the monthly payment on the loan is. Starting in month one, take the total amount of the loan and multiply it by the interest rate on the loan. Then for a loan with monthly repayments, divide the result by 12 to get your monthly interest.

What is the formula for monthly payments in Excel?

=PMT(17%/12,2*12,5400)

the result is a monthly payment of $266.99 to pay the debt off in two years. The rate argument is the interest rate per period for the loan. For example, in this formula the 17% annual interest rate is divided by 12, the number of months in a year.

How do you calculate monthly payments?

Step 2: Understand the monthly payment formula for your loan type.
  1. A = Total loan amount.
  2. D = {[(1 + r)n] - 1} / [r(1 + r)n]
  3. Periodic Interest Rate (r) = Annual rate (converted to decimal figure) divided by number of payment periods.
  4. Number of Periodic Payments (n) = Payments per year multiplied by number of years.

How do you calculate a balloon payment in Excel?

The function that will be used here in the payment function, abbreviated by Excel as PMT. To enter this equation, find a nearby empty cell and type "=PMT(". The program will then prompt you for variables like this: =PMT(rate, nper, pv, [fv], [type]). Input your variables into the equation.

How do you figure out an interest rate?

Divide your interest rate by the number of payments you'll make in the year (interest rates are expressed annually). So, for example, if you're making monthly payments, divide by 12. 2. Multiply it by the balance of your loan, which for the first payment, will be your whole principal amount.

What is the PPMT function in Excel?

Summary. The Excel PPMT function can be used to calculate the principal portion of a given loan payment. For example, you can use PPMT to get the principal amount of a payment for the first period, the last period, or any period in between.

What is the loan repayment schedule?

An amortization schedule is a complete table of periodic loan payments, showing the amount of principal and the amount of interest that comprise each payment until the loan is paid off at the end of its term.

How do you calculate simple interest on a loan?

Simple interest is calculated by multiplying the daily interest rate by the principal, by the number of days that elapse between payments. Simple interest benefits consumers who pay their loans on time or early each month. Auto loans and short-term personal loans are usually simple interest loans.

How do I create an additional amortization schedule in Excel?

How to make a loan amortization schedule with extra payments in Excel
  1. Define input cells. As usual, begin with setting up the input cells.
  2. Calculate a scheduled payment.
  3. Set up the amortization table.
  4. Build formulas for amortization schedule with extra payments.
  5. Hide extra periods.
  6. Make a loan summary.

Does Excel have amortization schedule?

Use it to create an amortization schedule that calculates total interest and total payments and includes the option to add extra payments. This loan amortization schedule in Excel organizes payments by date, showing the beginning and ending balance with each payment, as well as an overall loan summary.

How do you calculate monthly mortgage payments?

If you want to do the monthly mortgage payment calculation by hand, you'll need the monthly interest rate — just divide the annual interest rate by 12 (the number of months in a year). For example, if the annual interest rate is 4%, the monthly interest rate would be 0.33% (0.04/12 = 0.0033).

How can I pay off my mortgage in 5 years?

You're adding to other debts to pay off a mortgage
  1. The basic formula for paying a mortgage in 5 years.
  2. Set a target date.
  3. Make larger or more frequent payments.
  4. Cut back on your other spending.
  5. Boost your monthly income.
  6. When you shouldn't pay your mortgage in 5 years.

What is a balloon rate?

Balloon payments are often packaged into two-step mortgages. In a "balloon payment mortgage," the borrower pays a set interest rate for a certain number of years. Then, the loan then resets and the balloon payment rolls into a new or continuing amortized mortgage at the prevailing market rates at the end of that term.

What is an amortising loan?

An amortized loan is a type of loan that requires the borrower to make scheduled, periodic payments that are applied to both the principal and interest. An amortized loan payment first pays off the interest expense for the period; any remaining amount is put towards reducing the principal amount.

How do we calculate compound interest?

Calculating Compound Interest

Compound interest is calculated by multiplying the initial principal amount by one plus the annual interest rate raised to the number of compound periods minus one. The total initial amount of the loan is then subtracted from the resulting value.

What is the PMT formula?

=PMT(rate, nper, pv, [fv], [type]) The PMT function uses the following arguments: Rate (required argument) – The interest rate of the loan. Nper (required argument) – Total number of payments for the loan taken.

How do you calculate total payments?

When you take out a loan for a car, a home, or some other purchase, you pay back interest in addition to the principal, or original loan amount. Add a column in your loan amount chart for Total Payments. To calculate the total amount you will pay for the loan, multiply the monthly payment by the number of months.

Why is Excel PMT negative?

Notice that the Excel PMT function returns a negative value because this represents payments being made from you to your lender. Alternatively, if you prefer the PMT function return a positive value you can enter the Loan Amount as a negative figure.

How do I calculate monthly EMI in Excel?

How to Calculate Your Personal Loan EMI Using Excel
  1. Highlights.
  2. Calculate EMIs using the PMT function on Excel.
  3. Use this formula =PMT(RATE,NPER,PV,FV,TYPE)
  4. These variables need to be computed & may lead to errors.
  5. Use the online EMI calculator to avoid manual errors.

Related Documentation

Can u get free samples at Ulta?

Is normality test necessary?

Is The Merchant of Venice a comedy?

How do I create a loan payment schedule in Excel?