Calculating The Total Interest
Calculating the total interest paid is a simple matter of summing the values in column C. However, we will make use of our defined name, and offset that range by 3 columns to the left:
=SUM)
For this example, you should get $146,991.83. Of course, you could get the same answer with =SUM, but that wouldnt be as much fun.
What Is Included In The Amortization Schedule
Principal: The principal balance refers to the amount owing to the mortgage loan. As you continue to pay the principal, the interest that is attached will slowly decrease.
Interest Rate: The interest rate refers to the mortgage rate you are committed to with your loan. For the schedule to work, your loan rate will be a fixed-rate.
Mortgage Term : Multiple the number of years in your mortgage term by 12 the number of months in a year.
Make The Amortization Schedule Fancy
Just for fun and some functionality, I fancied it up a bit by using some IF statements, conditional formatting, and creating a chart that shows the remaining balance over time. Even though these things are mostly for looks, they also improve the functionality of the spreadsheet. I’ll go through each of these one by one.
You May Like: Why Is Mortgage Cheaper Than Rent
Mortgage Payoff Calculator Using Microsoft Excel
When you take out a fixed mortgage to buy or refinance a Home, your lender has the ability plug three elements of your amortization into a formula to calculate your monthly payments for the entire period.
Those three elements are your principal balance, your interest rate and the also the number of months in your mortgage term.
If you have access to Microsoft, you can create your own amortization schedule using Microsoft Excel. This software allows you to utilize a formula that will keep track of all your amortization payments. Doing this yourself will give you a better understanding of your loan.
The Final Amortization Schedule
The image below shows the beginning and end of the example amortization schedule.
Note that I have used the same conditional formatting as is described in the previous tutorial to hide the unused portion of the amortization schedule.
I hope that you have found this tutorial to be useful. You can download the Amortization Schedule With Extra Payments spreadsheet to use for yourself. If you have any questions, please feel free to contact me.
Don’t Miss: What Makes Mortgage Rates Change
Create A New Worksheet
To create the worksheet, start by creating a new worksheet in Excel based on the following example:
The fields marked as green in the example will be filled in by the user when you are ready to compare loans. The red fields are based on formulas that you will add to your worksheet.
The Loan Amount is calculated by subtracting the Down Payment field from the Total Loan Amount field. The formula looks like this:
=C3-C4
The Monthly Payment Formula uses the PMT function in Excel and is used to calculate the payment due for the loan. The formula looks like this:
=-PMT
The Payment function in Excel is great for calculating payments due on a loan. To access the payment function, you can either type in the formula above, or you can select the Monthly Payment Cell and then select PMT from the Financial formulas found in the Formulas menu.
When you select the PMT function, the function arguments window open automatically to allow you to enter the arguments for the function:
The rate is the interest rate for the loan per payment period. In this case it is the monthly interest rate that applies to your loan. The NPer refers to the total number of payments for the loan. The PV represents the total loan amount and the FV in this case represents the balloon payment due at the end of the loan.
The monthly interest rate is calculated by dividing the annual interest rate by twelve. The formula looks like this:
=E3/E5
=E4*E5
How To Prepare Amortization Schedule In Excel
This article was written by Nicole Levine, MFA. Nicole Levine is a Technology Writer and Editor for wikiHow. She has more than 20 years of experience creating technical documentation and leading support teams at major web hosting and software companies. Nicole also holds an MFA in Creative Writing from Portland State University and teaches composition, fiction-writing, and zine-making at various institutions.The wikiHow Tech Team also followed the article’s instructions and verified that they work. This article has been viewed 1,109,785 times.
An amortization schedule shows the interest applied to a fixed interest loan and how the principal is reduced by payments. It also shows the detailed schedule of all payments so you can see how much is going toward principal and how much is being paid toward interest charges. This wikiHow teaches you how to create your own amortization schedule in Microsoft Excel.
Recommended Reading: How To Become A Mortgage Broker In Massachusetts
How To Create An Amortization Table Using The Pmt Function
This lesson guides you through the process of creating an amortization table with Microsoft Excel.It’s a useful example because an amortization table can help you make wise decisions aboutborrowing money or repaying loans. An amortization table shows for each payment of a loanhow much of the payment is going toward the interest on the unpaid balance and how much isgoing toward the principal.
Creating an amortization table is a 3 step process:
A Few Things To Consider
There are a few important things to note here. First, we need to make two important adjustments, because we’re calculating monthly payments. We need to divide our annual rate by 12 to get the monthly interest rate , and we need to multiply 4 by 12 to get the total number of months in our repayment period .
Second, the is entered as a negative value because that amount is owed to the lender.
Once your boxes are filled in, hit “OK.”
Your spreadsheet should now look like this:
You May Like: How To Apply For A Home Mortgage
Calculating First Months Interest And Principal
A Note About Amortization In The Uk
Some loans in the UK use an annual interest accrual period , but a monthly payment is calculated by dividing the annual payment by 12 and the interest portion of the payment is recalculated only at the start of each year. For these types of loans, if you create an amortization schedule using the technique described above, the schedule would need to show yearly payments . For a 30-year loan at 6% you would set r = 0.06, n = 30, and p = 1 to calculate the annual payment.
Also Check: Where To Find Lowest Mortgage Rates
How To Export The Amortization Schedule To Excel
While it is possible to manually create an amortization schedule in excel, it may take you some time if you are not familiar with excel. For this reason, we created this amortization calculator that allows you to export the amortization schedule to excel.Simply enter your loan information for calculation and you will be shown an amortization schedule table. You can then hit the Export buttons to download the amortization schedule to excel.
Calculations In An Amortization Schedule
When you know the payment amount, it is pretty straight forward to create an amortization schedule. The examplebelow shows the first 3 and last 3 payments for the above example. Each line shows the total payment amount as well as how much interest and principal you are paying. Notice how much more interest you pay in the beginning than at the end of the loan!
The Interest portion of the payment is calculated as the rate times the previous balance, and is usually rounded to the nearest cent. The Principal portion of the payment is calculated as Amount – Interest. The new Balance is calculated by subtracting the Principal from the previous balance. The last payment amount may need to be adjusted to account for the rounding.
An amortization schedule normally will show you how much interest and principal you are paying each period, and usually an amortization calculator will also calculate the total interest paid over the life of the loan. Besides considering the monthly payment, you should consider the term of the loan . The longer you stretch out the loan, the more interest you’ll end up paying in the end. Usually you must make a trade-off between the monthly payment and the total amount of interest.
To quickly create your own amortization schedule and see how the interest rate, payment period, and length of the loan affect the amount of interest that you pay, check out some of the amortization calculators listed below.
Don’t Miss: How Many Times Can You Pull Credit For Mortgage
Computing Amortization For The Entire Loans Term
Set Up The Amortization Table
For starters, define the input cells where you will enter the known components of a loan:
- C2 – annual interest rate
- C3 – loan term in years
- C4 – number of payments per year
- C5 – loan amount
The next thing you do is to create an amortization table with the labels in A7:E7. In the Period column, enter a series of numbers equal to the total number of payments :
With all the known components in place, let’s get to the most interesting part – loan amortization formulas.
You May Like: How Much Interest Do I Pay On A Mortgage
How To Use Excel Formulas To Calculate A Term
Term loans can have a variety of repayment periods, interest rates, amortizing methods, and so on. Here’s how to calculate amortization schedules for the two most common types of amortizing loans.
How do I calculate cumulative principal and interest for term loans? I have scoured the web for a function that will perform this task, with no avail. Lake M.
This is an interesting question because it touches on at least four issues related to the time value of money:
1. How Do You Define a Term Loan?
The definitions vary. For example, heres how different sources define a term loan:
- Business Dictionary: Asset based short-term loan payable in a fixed number of equal installments over the term of the loan. Term loans are generally provided as working capital for acquiring income producing assets that generate the cash flows for repayment of the loan.
- Wikipedia: A monetary loan that is repaid in regular payments over a set period of time. Term loans usually last between one and ten years, but may last as long as 30 years in some cases.
- Investopedia: A loan from a bank for a specific amount that has a specified repayment schedule and a fixed or floating interest rate.
2. Is the Loans Interest Rate Fixed or Floating?
Im going to assume that the interest rate is fixed. Ill cover floating rate loans at another time.
3. What Type of Loan is It? Even-Payment? Or Straight-Line?
4. What Help Does Excel Provide for Each Type of Loan?
What Does An Amortization Table Look Like
What, then, will your amortization schedule look like? That depends largely on the type of loan you take out and your interest rate.
Say you’re approved for a 30-year mortgage for $200,000 at a fixed interest rate of 4%. Your monthly payment to pay off your loan in 30 years broken down into 360 monthly payments will be $954.83, not counting any money you must pay to cover property taxes and homeowners insurance.
In the table below, you can see that a whopping $666.67 of that first payment will go toward interest with only $288.16 going toward principal. That first payment will reduce the principal balance of your loan to $199.711.84.
Gradually, more of your payments will go toward principal than interest. For instance, by payment 351, only $31.25 of your payment will go toward interest and $923.58 will go toward reducing your principal balance.
Don’t Miss: How To Calculate Self Employed Income For Mortgage
Estimate Your Monthly Loan Repayments Interest Rate And Payoff Date
An amortization calculator is useful for understanding the long-term cost of a fixed-rate mortgage because it shows the total principal that youll pay over the life of the loan. Its also helpful for understanding how your mortgage payments are structured.
If youve ever wondered how much of your monthly payment will go toward interest and how much will go toward principal, an amortization calculator is an easy way to get that information.
Explaining The Manual Formulas
Step 8: This step converts your annual interest rate into a decimal figure by dividing it by 100. Doing that actually breaks the number down from a yearly rate into a monthly rate by dividing it by 12.
Step 9: This step considers the addition of interest to the principal sum of the entire mortgage.
Step 10: This step takes all the data which determines a given number that is then applied to the principal to figure out a monthly payment.
Step 11: This steps applies that multiplier to your loan principal.
You are able to adjust the principal amounts, interest rates or mortgage terms by changing the values in cells B1, B2 and B3. This is a useful comparison tool. The total payment in cell A7 will reflect the new numbers you plugged in.
Having this spreadsheet will make you consider several options such as:
- Converting to a different interest rate
- Cutting down your mortgage term
- Increase your monthly payments
You May Like: How Long Does Fha Mortgage Insurance Last
Preparing A Loan Amoritization Calculator In Excel
Excel is an extremely useful tool for calculating your very own amortization table. You can even experiment with the different inputs to see how different interest rates, periods and principal loan amounts affect the loan payments you’ll be making.
Let’s look at an example to illustrate the process.
Assume you just purchased a car with a $20,000 loan that has to be completely repaid in 4 years. The annual interest rate on the loan is 3%. Since the loan is amortized, the monthly payments will all be the same amount.
The first step is to calculate the monthly payment amount. This can be done using the PMT function in Excel.
Start by making a new spreadsheet and entering in the following data needed for the payment amount calculation.
Column A will contain the following labels for our data:
A1 = the original loan principal
A2 = the loan term in years
A3 = the annual interest rate
A4 = the number of payments per year
A5 = the payment amount
Column B will contain the corresponding values for each label in column A:
B1 = $20,000
B4 = 12
B5 = the value we’re looking for
Excel’s PMT function can be found in the financial functions menu. First select cell B5, then click on the financial functions menu under the formulas tab and select the PMT function from the list. A box will be displayed where you should enter the data from the spreadsheet just as it is in this image:
