Monday, February 19, 2024

How To Amortize A Mortgage In Excel

Don't Miss

Calculating The Total Interest

How to Make Loan Amortization Tables in Excel || Download Demo File

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

30 Loan Amortization Schedule Excel

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:

  • Use the =PMT function to calculate the monthly payment.
  • Create the first two lines of your table using formulas with the correct relative and absolute references.
  • Use the Fill Down feature of Excel to create the rest of the table.
  • A Few Things To Consider

    How to create a loan amortization table in Excel – Microsoft Excel Tutorial

    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

  • 1Gather the information you need to calculate the loans amortization. Youll need the principal amount and the interest rate. To calculate amortization, you also need the term of the loan and the payment amount each period. In this case, you will calculate monthly amortization.XResearch source
  • The principal is the current loan amount. For example, say you are paying off a 30-year mortgage. If your loan has a balance outstanding of $100,000 , that is the principal.
  • Your interest rate is the annual rate on the loan. To calculate amortization, you will convert the annual interest rate into a monthly rate.
  • The term of the loan is 360 months . Since amortization is a monthly calculation in this example, the term is stated in months, not years.
  • Your monthly payment is $599.55. The dollar amount of the payment stays constant. However, the portion of the payment that is principal or interest will change. You will mostly be paying off the interest when you start making payments, and then your payments will start to go to the balance.
  • 2Set up a spreadsheet. This calculation has a few moving parts and would best be accomplished in a spreadsheet where you’ve pre-loaded all your relevant info into column headings like: Principal, Interest Payment, Principal Payment, and Ending Principal.
  • The total number of rows below those headings would be 360 to account for each monthly payment.
  • You can also try an online amortization calculator.
  • Calculate the principal amount for month two: .
  • 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

    Loan Amortization Schedule In Excel

    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

  • 1Analyze the trend that occurs over time. You can see that the loans principal is reduced each month. Because the principal amount declines, the interest computed on the lower principal amount also goes down. Over time, a growing amount of each monthly payment goes toward principal.XResearch source
  • Calculate the new principal balance for month threes interest calculation: .
  • Compute interest for month three: .
  • Calculate the principal payment in month three: .
  • 2Consider the impact of amortization at the end of the loans term. Youll see that, over time, the amount of interest charged each month declines. The principal portion of each payment increases over time as your remaining balance gets smaller.XResearch source
  • Interest payments decline to nearly zero. In the last month of the loans term, the interest payment is $2.98.
  • The principal amount still owed is $0 at the end of the term.
  • 3Use the concept of amortization to make smart choices about your finances. Since your mortgage loan and many car loans use amortization, you need to understand this concept. You can use your knowledge of amortization to manage your personal debts.XResearch source
  • Whenever possible, make extra payments to reduce the principal amount of your loan faster. The faster youre able to reduce principal, the less total interest you will pay over the loan 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

    Loan amortization table in Excel

    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:

    More articles

    Popular Articles