- #Excel extra payment mortgage calculator full#
- #Excel extra payment mortgage calculator software#
- #Excel extra payment mortgage calculator free#
This is important so that any further calculations of the payment, principal, and interest are all zero as well (remember that we may pay off the loan early). If so, then we simply take the remaining balance and subtract the principal payment for the month and also any extra payment amount. Note that I am testing to see if the previous balance is greater than zero (to five decimal places). Now in F13, we calculate the remaining balance by subtracting the principal payment from the previous balance: Note that we have skipped over column E because we are going to enter the extra payment there. In F12 enter the original balance with the formula =B2. We can now add a column for calculating the remaining balance. You can extend it further if you need a longer amortization period. We now copy those formulas down to row 372, which will allow us to have up to 360 payments. This makes sure that you never pay more than the remaining principal amount. Note that for the principal in D13, I also added a Min function. This makes our payment calculation slightly more complicated. So, we have to calculate that last payment based on the interest for the last month and the remaining principal. It turns out that we cannot use the built-in PMT function for the last payment because it will be a different amount. Because we are going to add extra payments, we want to be sure that we don’t overpay the mortgage.īefore we can calculate the interest and principal we must calculate the payment.
#Excel extra payment mortgage calculator full#
Monthly Principal Payment = Full Payment Amount - Monthly Interest Paymentįor example if we have the payment amount in B13, then we can calculate the first interest payment in cell C13 as: $B$4/$B$5*F12, and the first principal payment in D14 as: B13-C13.
Monthly Interest Payment = Monthly Rate x Remaining PrincipalĪnd the principal portion of the payment is: The interest payment must always be calculated first, and it is simply the per period (here monthly) interest rate times the remaining principal: Here is a screenshot that shows the beginning portion of our spreadsheet:Ĭalculating the Interest, Principal, and Full Payment Amountīecause we can’t use the built-in functions, we will have to do the math. Note that in this tutorial I assume that you will make the same extra payment each month, and that it will start with the first payment. I have set it to $300 per month, but you can change that. You will also notice that I have entered the extra principal that will be paid into B7.
This is just in case you may want to amortize something that has other than monthly payments. Note that I have entered the payments per year in B5. In B6 I have calculated the normal mortgage payment using the PMT function:Īs always, I have adjusted the interest rate and number of payments to a monthly basis. We have a $200,000 mortgage for 30 years with monthly payments at a 6.75% APR. Note that we have all of the information that we need in the upper-left corner of the spreadsheet. However, the basic idea is the same with the exception that we can no longer use Excel’s built-in IPmt and PPmt functions. Obviously, there will need to be some changes, and we will add some new features. We will use the same basic layout and numbers here. If you haven’t yet read the previous tutorial, I suggest that you do it now. Don’t ask them, just do it and see what happens. In fact, I have refinanced my mortgage several times over the years and every mortgage servicer has done this. I have done this for years, and the mortgage statement always shows the extra principal payment even though I have done nothing more than pay extra – there is no need for a separate check or the mortgage company’s approval.
#Excel extra payment mortgage calculator software#
Their software will automatically apply any extra amount to the remaining principal. They will often try to get you to sign up and pay for a program that allows you to pay extra principal, but this is not necessary. In this tutorial we will add this feature.īefore we get started let me mention one important thing: You can almost always (actually as far as I know it is always) just go ahead and add more money to the check that you send to the mortgage servicing company. In the original amortization schedule tutorial I left out a feature that is of interest to a lot of people: adding extra principal payments in order to pay off the loan earlier than the loan contract calls for.
#Excel extra payment mortgage calculator free#
Are you a student? Did you know that Amazon is offering 6 months of Amazon Prime - free two-day shipping, free movies, and other benefits - to students? Click here to learn more
0 kommentar(er)
