Loan Amortization Schedule Excel With Extra Payments

Some inquiries I’ve received about mortgages and loans in general concern whether it’s a good idea to make additional principal payments. Please feel free to download the Extra Mortgage Payments Calculator for Excel and continue reading this page if you have any similar questions. Advertisement.

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

Why Make Extra Mortgage Payments?

There may be many reasons for wanting to make additional loan payments, but the following seem to be the most prevalent ones:

  • To payoff a home, auto, or consumer loan more quickly.
  • To reduce the amount of total interest paid.
  • To take advantage of high mortgage interest rates when other savings plans have a lower interest rate.
  • All of these may be valid justifications for making additional payments, but first, ensure that you know what you’re doing. See below for more information about each of these reasons.

    Extra Payment Mortgage Calculator for Excel

    License: Personal Use (not for distribution or resale)

    Jon Wittwer stated, “No installation, no macros—just a simple spreadsheet.”

    Determine how much more interest will be paid overall on a mortgage loan if you make additional monthly payments.

    I’ve developed numerous other calculators since making this spreadsheet that allow you to include additional mortgage payments. My Home Mortgage Calculator is the most sophisticated and adaptable.

    * For Excel 2003: The Analysis ToolPak, which is included with Excel but frequently does not install automatically, is necessary for the CUMIPMT function. Open Excel and select the Tools menu > Add-Ins option to install the add-in. and check the box next to “Analysis ToolPak”.

    Reasons to Make Extra Payments

    This approach is pretty easy to understand. You would anticipate that the loan would be repaid sooner if you made additional payments. The spreadsheet presumes that the additional monthly mortgage payments are made.

    Pay Less Total Interest

    Each month, your payment consists of both interest and principal. The amount of interest paid is based on the remaining balance of the principal (i e. the current balance). That implies that you will end up paying less interest if you reduce the principal. The worksheet calculates this as the so-called “interest savings” (i e. the reduction in the interest expense).

    Extra Payments vs. Savings

    It might seem strange to consider making extra payments as an “investment,” but it turns out that doing so is very similar to putting the money in a savings plan. The amount of reduced interest expense from making extra payments is equal to the amount of interest “gained” in the savings plan if the mortgage interest rate is the same as that of the savings plan (assumed both rates are fixed and compound monthly). The primary distinction is that cash is more easily accessible with a savings plan (or other comparable investment).

    In the end, there are three key inquiries (which you must resolve for yourself):

  • What approach provides a better interest rate?
  • Where should the investment be tied up? (Home equity or a savings/investment account?)
  • Simply put, home appreciation or depreciation is a totally different issue. The mortgage interest rate determines the “interest savings” from extra payments, not the home’s present value.

    I won’t respond to this question because I’m not a certified tax advisor, but keep in mind that interest earned in savings or investment accounts may be taxable, while interest paid on a mortgage is typically deductible. related blog articles.

    More Mortgage and Loan Calculators


    How do you add extra payments to amortization schedule?

    Calculate your annual additional payment using the amortization schedule by multiplying the amount by the loan’s term. Enter $18,000, for instance, to add $600 a year to a 30-year loan.

    Do extra principal payments affect amortization?

    How extra payments affect your amortization schedule. You do have the choice to make additional mortgage payments, which will change how your amortization is calculated. Paying more can help you save money over time because it will go toward your principal rather than interest.

    What happens if I pay 2 extra mortgage payments a year?

    By increasing your principal payments, you can reduce the term of your mortgage and accelerate the process of equity growth. You’ll make fewer total payments because your balance is being paid off more quickly, which will result in more savings.

    Should you make additional payments on amortized loans?

    By paying your regular monthly payments, you’ll amortize (or reduce) your loan. But if it’s within your means, making additional principal payments can be a great way to speed up the repayment of your fixed-rate loan and reduce the amount of interest you have to pay.