How to Calculate Mortgage Interest
Three Methods:Calculating Interest Quickly and EasilyCalculating Interest Using a Spreadsheet ProgramCalculating Mortgage Interest Manually
The interest on a loan is the amount of money you pay to a lender in addition to your principal (the amount that you borrowed). Interest is typically provided as a percentage, such that the interest rate is a given fraction of the principal. A mortgage loan is a type of loan used to finance the purchase of a property. You can calculate interest paid on a mortgage loan using the interest rate, principal value (property price), and the terms of the loan (the duration and number of payments). This can be done in a number of ways, depending on what information you have and your personal preference.
Steps
Method 1 Calculating Interest Quickly and Easily
- 1Use an online mortgage loan calculator. There is a variety of online calculators that will find your monthly payment and interest paid with the simple input of a few pieces of key information. Try searching for "mortgage loan calculator" using your preferred search engine. Usually, you'll have to input details of your loan, like the number of years, annual interest rate, and value of your principal. Then, simply hit "calculate" and the provided readout should tell you anything else you need to know.^{[1]}
- This can also be a useful way to compare mortgage plans. For example, you may be deciding between a 15-year loan at 6 percent or a 30-year loan at 4 percent. The calculator will help you easily see that, despite the higher interest rate, the 15-year loan is a cheaper option.
- 2Calculate total interest using loan payments. Similar to the quick method above, this one will allow you to calculate the total interest you will pay on your loans, assuming you already know your monthly payment. However, here you will be multiplying your monthly payment by the number of payments to arrive at a total amount paid over the life of the loan.
- Start by finding your monthly payments either on a recent bill or on your loan agreement.
- Then, multiply your monthly payment by your number of payments.
- Subtract your principal from the total of your payments. This number will represent the total amount you will pay in interest over the life of your loan.
- For example, imagine you are paying $1,250 per month on a 15-year, $180,000 loan. Multiply $1,250 by your number of payments, 180 (12 payments per year*15 years), to get $225,000. Your total interest paid would then be $225,000 - $180,000, or $45,000.
Method 2 Calculating Interest Using a Spreadsheet Program
- 1Understand the function used. Mortgage interest can be easily found using your chosen spreadsheet program. This function, in all major spreadsheet programs (Microsoft Excel, Google Spreadsheet, and Apple Numbers), is known as CUMIPMT, or the cumulative interest payment function.^{[2]}^{[3]}^{[4]} It combines information like your interest rate, number of payments, and principal to arrive at an amount for total interest paid over the life of the loan. You can then divide this information to find the amount of interest paid each month or annually.
- For simplicity, we will be focusing on Microsoft Excel's CUMIPMT function here. The process and inputs will likely be identical or very similar for any other program you are using. Consult the help tab or customer service if you have any problems using these functions.
- 2Use the CUMIPMT function. You can use the cumulative interest payment function to determine your interest paid. Start by entering =CUMIPMT( into your spreadsheet. The program will prompt you for the following information: (rate, nper, pv, start_period, end_period, type).
- rate here means your monthly interest rate. Again, this will be your annual interest divided by 12 and expressed as a decimal. For example, a six percent annual rate would be expressed as 0.005 here (6%/12=0.5%=0.005).
- nper stands for "number of periods" and is asking for your total number of payments. Like before, this will be the term of your loan in year multiplied by 12 for monthly payments.
- pv means "present value." Input your principal (amount borrowed) here.
- start_period and end_period represent your timeframe for calculating interest. To calculate interest over the life of the loan, enter 1 for start_period and your value for nper into end_period.
- type refers to when in each period your payments are made; 0 for the end of the month and 1 for the beginning of the month. In most cases, you should use 0.
- Input the information, close the function with a ")" at the end, and press enter to receive your answer.^{[5]}
- 3Analyze your result. The CUMIPMT function will return an amount that represents the total interest you will pay on your loan. To find the interest paid each month or year, simply divide this amount by either the number of payments or the number of years on your loan, respectively.
- This number will also be expressed as a negative number. This doesn't mean you entered your information incorrectly, but simply that the program represents interest as an expense and therefore, a negative number. Multiply by -1 if this helps you understand and use the figure.^{[6]}
Method 3 Calculating Mortgage Interest Manually
- 1Understand the equation. In order to calculate interest paid on a mortgage loan, we will calculate the monthly payment and then use the simple method from method 1 above to calculate interest. The monthly payment equation can be represented as follows: . These variables represent the following inputs:
- M is your monthly payment.
- P is your principal.
- r is your monthly interest rate, calculated by dividing your annual interest rate by 12.
- n is your number of payments (the number of months you will be paying the loan)^{[7]}
- 2Input your information into the equation. You will need to input your principal, monthly interest rate, and number of payments in order to find your monthly payment. This information can be easily found in your loan agreement or from a quoted loan estimate. Check the information again to be sure of its accuracy before using it in calculations.
- For example, imagine you have a $100,000 mortgage loan with 6 percent annual interest over 15 years. Your input for "P" would be $100,000. For "r," you would use your monthly interest rate, which would be 0.06 (6 percent) divided by 12, or 0.005 (0.5 percent). For "n" you would use your total number of payments, one for each month in fifteen years, which would be 12*15, or 180.
- In this example, your complete equation would look like this:
- 3Simplify your equation by adding 1 to the "r." Simplify your terms by doing the first step in the order of operations, which is adding the 1 and "r" inside the parentheses on the top and bottom of the equation. This is a simple step that will make your equation look much less complicated.
- After this step, your sample equation would look like this:
- 4Solve the exponents. The result of the previous step must now be raised to the power of "n." Keep in mind that only the figures inside the parentheses will be raised to this power, not the "r" outside of it or the -1 at the end.
- After this step the sample equation would look like this:
- 5Simplify again. Here, you should multiple "r" times the result of the last step on the top (the numerator) and subtract 1 from your result on the bottom (the denominator).
- The same equation would look like this after this step:
- 6Divide the numerator by the denominator.
- In the example, your equation would now be:
- 7Multiply "P" by this result. This will give you your monthly loan payment.
- In the example, this would be ($100,000)*(0.008439), or $843.90. This represents your monthly payments.
- 8Calculate interest paid using the payment information. With this information, you can now calculate total interest paid and interest paid each month. Both will allow you to compare different amounts of interest you might pay with different loans and see which one is right for you.
- Find monthly interest paid by dividing "P" by "n" and subtracting this number for your monthly payments, "M."
- Find total interest paid by multiplying your monthly payment "M" by "n" and then subtracting "P."
Sources and Citations
- ↑ http://www.bankrate.com/calculators/mortgages/mortgage-payment-calculator.aspx
- ↑ http://www.accountingweb.com/technology/excel/excel-tip-calculating-interest
- ↑ https://support.google.com/docs/answer/3093211?hl=en
- ↑ http://www.apple.com/mac/numbers/compatibility/functions.html
- ↑ http://www.accountingweb.com/technology/excel/excel-tip-calculating-interest
- ↑ http://www.accountingweb.com/technology/excel/excel-tip-calculating-interest
- ↑ http://www.mtgprofessor.com/formulas.htm
Article Info
Categories: Mortgages and Loans