The monthly payment is $149.85, and that amount appears in each row of the payment schedule, in column C. By default this calculator is selected for monthly payments and a 30-year loan term. A person could use the same spreadsheet to calculate weekly, biweekly or monthly payments on a shorter duration personal or auto loan. The following table shows locally available mortgage rates which you can use to help calculate your monthly home loan payments.

Then you can experiment with other payment scenarios such as making an extra payment or a balloon payment. Make sure to read the related blog article to learn how to pay off your loan earlier and save on interest. Below the loan information cells, there is a loan payment schedule, with all the monthly payment details. Easy to use Microsoft Excel loan payment schedule shows monthly payment details, based on your loan info, entered at the top of the worksheet.

Sample Repayment Agreement Template

As you can see in this example, the total payment decreases each month as the amount of interest decreases while the principal stays the same. Cell H2 contains our interest rate and because it’s the annual rate we divide it by 12. For example, after the 40th payment, we will have to pay $83,994.69 on $120,000.

If you add an extra payment the calculator will show how many payments you saved off the original loan term and how many years that saved. Excel offers a wide range of powerful functions that can enhance the functionality of your amortization schedule. For example, you can use the PMT function to calculate the monthly payment amount or the IF function to handle different payment scenarios.

You simply add the extra payment to the amount of principal that is paid that period. For fixed-rate loans, this reduces the balance and the overall interest, and can help you pay off your loan early. But, the normal payment remains the same (except for the last payment required to bring the balance to zero – see below). Once you have selected and downloaded the desired Excel template, it’s time to input your loan information. This typically includes the principal amount, interest rate, loan term, and start date.

• Due to the use of relative cell references, the formula adjusts correctly for each row.
• The interest is calculated for each period—for example, the monthly repayments over 10 years will give us 120 periods.
• The payment amount is calculated with the PMT(rate, nper, pv, [fv], [type]) function.
• It displays particular dates of an individual’s occurring transactions and its payment amount.
• By paying off your loan sooner, you can save a significant amount of money in interest payments.

Scroll to the end of the loan payment schedule to see when your last payment will be made. For this example, the first payment was made on January 1st, 2018, and the last payment will be made on December 1, 2020. If you have variable additional payments, just type the individual amounts directly in the Extra Payment column. Create a loan amortization table with the headers shown in the screenshot below. In the Period column enter a series of numbers beginning with zero (you can hide the Period 0 row later if needed).

For example, with a loan amount of $5000, over 36 months, at an annual interest rate of 5%, the monthly payment is calculated to be $149.85. In the above example, we built a loan amortization schedule for the predefined number of payment periods. This quick one-time solution works well for a specific loan or mortgage. You cannot fully make a payment schedule if the legal contract is not yet approved. If you’re considering any type of loan, whether it’s an auto loan, mortgage, personal loan, or otherwise, you want to know what it will cost. You want to know what it will cost you in the short term, to see if you can afford the regular payments.

Home Equity Loan Calculator

This amortization table Excel template will show you the balance remaining after each payment, and the amount of interest paid to date. It also calculates the total number of payments necessary to pay the loan balance in full, so you can plan accordingly. The following amortization schedule templates provide a framework for easily determining balances paid and owed. They also list all scheduled payments over the lifetime of the loan, and show the total amount that remains. Simply plug in your figures for the total loan amount, interest rate, loan duration, and payment frequency, and the calculator will do the rest.

Determining the Length of a Loan

As you see here, the interest rate is in cell B2 and we divide that by 12 to obtain the monthly interest. Then, the number of payments is in cell B3 and loan amount in cell B4. The only required arguments are the first three for interest rate, number of payments, and loan amount. With a few simple functions and your data, you can easily get basic loan calculations in Microsoft Excel.

You can then begin to make decisions about prepayments or refinancing options. We use the PMT function to calculate the monthly payment on a loan with an annual interest rate of 5%, a 2-year duration and a present value (amount borrowed) of $20,000. An amortization schedule, sometimes called an amortization table, displays the amounts of principal and interest paid for each of your loan payments. You can also see how much you still owe on the loan at any given time with the outstanding balance after a payment is made. Optional extra payment – if you want to add an extra amount to each monthly payment then add that amount here & your loan will amortize quicker.

Maybe you have an existing loan and want to quickly see the annual interest rate you’re paying. As simple as calculating a payment with basic loan details, you can do the same to determine the interest rate. The interest is calculated for each period—for example, the monthly repayments over 10 years will give us 120 periods. We have seen how to set up the calculation of a monthly payment for a mortgage. But we may want to set a maximum monthly payment that we can afford that also displays the number of years over which we would have to repay the loan. For that reason, we would like to know the corresponding annual interest rate.

How to Create a Good Payment Schedule

It has has been refined and improved over years of use and feedback received from both professionals and every-day home buyers. This spreadsheet lets you choose from a variety of payment frequencies, including Annual, Quarterly, Semi-annual, Bi-Monthly, Monthly, Bi-Weekly, or Weekly Payments. It only works for fixed-rate loans and mortgages, but it is very clean, professional, and accurate. The amount of the interest payment for a specified Period is equal to the balance of the loan for the previous period, multiplied by the periodic interest rate. The loan balance for the previous period is equal to the amount of the original loan multiplied by the current period minus 1, multiplied by the periodic principal payment. The principal amount each period is equal to the loan amount divided by the total number of periodic payments.

In that case, the rate per period is simply the nominal annual interest rate divided by the number of periods per year. When the compound period and payment period are different (as in Canadian mortgages), a more general formula is needed (see my amortization calculation article). In this Excel loan payment schedule template, enter your loan information at the top of the worksheet, in the green cells.

An amortization schedule is a detailed breakdown of each loan payment, illustrating how much of each installment goes toward the principal and how much is attributed to interest. Essentially, it serves as a roadmap for your loan repayment journey via a clear visualization of your progress reducing the debt. Firstly, it helps you build a good credit history, which can open doors to better financial opportunities in the future. When you consistently make your loan payments on time, it demonstrates to future lenders that you are a responsible borrower. Loan repayment can be a daunting task, especially when you’re dealing with multiple payments and varying interest rates. However, with the right tools and strategies, you can streamline the repayment process and stay on track toward becoming debt-free.

The spreadsheet is not password protected, which means you can see the calculations and customize the spreadsheet. To check whether your calculations are correct at this point, add up the numbers in the Principal and Interest columns. The sum should be equal to the value in the Payment column in the same row. The calculator updates results automatically when you change any input. Annual Interest Rate, aka Annual Percentage Rate (APR), is the interest rate designated by the lender. To get started, I would recommend downloading the Simple Amortization Chart template.

With a straight-line loan, you pay the amount of interest due each period plus a fixed amount for principal reduction. As mentioned with each function above, the future_value and type arguments are optional. Here’s a brief explanation of each if you’d like to include them in your https://1investing.in/ formula. By increasing the number of payments, you can see how much the monthly payments decrease. By making slight adjustments to the constants, you can see what your payment would be if you had a different interest rate, made more or fewer payments, or changed the loan amount.