Loan Calculator With Extra Payments Excel

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.

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

FAQ

How do I create a loan amortization schedule with extra payments in Excel?

1. Define input cells. As usual, begin with setting up the input cells.
2. Calculate a scheduled payment. …
3. Set up the amortization table. …
4. Build formulas for amortization schedule with extra payments. …
5. Hide extra periods. …
6. Make a loan summary.

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.

How do I calculate a loan repayment schedule in Excel?

The PMT (rate, nper, pv, [fv], [type]) function in Excel is used to figure out how much to pay out. You should maintain consistency with the values provided for the rate and nper arguments to ensure consistency in payment frequencies: Rate – The rate argument represents the loan’s interest rate per period.

How do I create a repayment schedule in Excel?

1. Calculate the principal portion of the payment using the PPMT function.
2. Calculate the interest component of the payment using the IPMT function.
3. Update the balance.
4. Drag the range A7:E7 (first payment) down one row after choosing it.
5. Drag range A8:E8 (second payment) to row 30 after choosing it.