Free amortization schedule with fixed monthly payment

Know at a glance your balance and interest payments on any loan with this simple loan calculator in Excel. Just enter the loan amount, interest rate, loan duration, and start date into the Excel loan calculator. It will calculate each monthly principal and interest cost through the final payment. Great for both short-term and long-term loans, the loan repayment calculator in Excel can be a good reference when considering payoff or refinancing. Download this Excel loan calculator and take charge of your financial obligations. This is an accessible template.

Excel

Amortization is the process of paying off a debt with a known repayment term in regular installments over time. Mortgages, with fixed repayment terms of up to 30 years (sometimes more) are fully-amortizing loans, even if they have adjustable rates. Revolving loans (such as those for credit cards) don't have a fixed repayment term, are considered are open-ended debt and so don't actually amortize, even though they may be paid off over time.

What is an amortization schedule?

Simply put, an amortization schedule is a table showing regularly scheduled payments and how they chip away at the loan balance over time. Amortization schedules also will typically show you a payment-by-payment breakout of the loan's remaining balance at the start (or end) of a period, how much of each payment is comprised of interest and how much is repayment of principal. Although the total monthly payment you'll make may remain the same, the amounts of each of these payment components change over time as the loan is repaid and the loan's remaining term declines.

An amortization schedule can be created for a fixed-term loan; all that is needed is the loan's term, interest rate and dollar amount of the loan, and a complete schedule of payments can be created. This is very straightforward for a fixed-term, fixed-rate mortgage.

For Adjustable Rate Mortgages (ARMs) amortization works the same, as the loan's total term (usually 30 years) is known at the outset. However, interest rates for ARMs change at regular intervals, so both the total monthly payment due and the mix of principal and interest in a given payment can change considerably at each interest-rate "reset".

Here we are going to build out an amortization schedule for a loan, and it’s going to be one of those exercises like in high school where your teacher made you do it by hand, yet the entire time you were probably thinking, “this would be much easier with a calculator.” The good thing is that, in real life, we can use Excel, an online calculator, or some type of online spreadsheet to make our lives much easier. That being said, I’m going to show how to do it by hand because, in order to build out a schedule, we must first understand how to calculate all the parts.

Payments Formula

The total payment each period is calculated through the ordinary annuity formula.

Where:

• PMT = total payment each period
• PV = present value of loan (loan amount)
• i = period interest rate expressed as a decimal
• n = number of loan payments

The present value of an annuity formula equates how much a stream of equal payments made at regular intervals is worth at current time. By rearranging the formula, we can calculate how much each payment must be worth in order to equal a present value, where the present value is the value of the loan. The payment calculated will be the total payment each month for the duration of the loan. Loan payments consist of two parts: payments toward principal, and payments toward interest.

Calculating Payment towards Interest

As part of the total loan payment each period, the borrower must make a payment towards interest. The lender charges interest as the cost to the borrower of, well, borrowing the money. This is a result of the time value of money principle, since money today is worth more than money tomorrow. Interest is easy to calculate. You simply take the interest rate per period and multiply it by the value of the loan outstanding. The formula is shown below:

Where:

• P = principal remaining
• i = period interest rate expressed as a decimal

Calculating Payment towards Principal

There isn’t a good direct way to calculate the payment towards principal each month, but we can back into the value by subtracting the amount of interest paid in a period from the total payment each period. Since interest and principal are the only two parts of the payment per period, the sum of the interest per period and principal per period must equal the payment per period.

Amortization Schedule Example

Let’s take a look at an example. Suppose you take out a 3-year, $100,000 loan at 6.0% annually, with monthly payments. When building out a table, I think the most important part is the setup. Once a good table is set up, filling in the values is relatively easy. Below is an example of a table that could be used for the schedule:

Loan$100,000Periods36Interest Rate6.0%

PeriodPrincipalInterestPaymentBalance1$2,542.19$500.00$3,042.19$97,457.812$2,554.90$487.29$3,042.19$94,902.913$2,567.68$474.51$3,042.19$92,335.23...34$2,997.01$45.18$3,042.19$6,039.2135$3,011.99$30.20$3,042.19$3,027.2236$3,027.22$15.14$3,042.36$0.00

How to calculate amortization schedule with fixed monthly payment?

Can I make my own amortization schedule?

How do you calculate the monthly payment on a fixed

What is the formula for monthly amortization?