How to Calculate Mortgage Payable

Learn the equation. To calculate the amount that needs to be paid monthly, we can also rely on a relatively simple equation. The monthly payment equation can be represented as follows: USA=Pr(first+r)n(first+r)n−first{displaystyle M=P{frac {r(1+r)^{n}}{(1+r)^{n}-1}}} . Meaning of the variables:

• M is your monthly payment.
• P is the principal loan amount.
• r is the monthly interest rate, calculated by dividing the annual interest rate by 12.
• n is your number of payments (the number of months you will have to pay for the loan) ^[6]

Enter information into the equation. You need to enter the principal amount, the monthly interest rate, and the number of payments to figure out how much to pay each month. This information can be easily found in the loan agreement or from the quoted loan estimate. Check the information again to make sure it is correct before calculating.

• For example, let’s say you have a $100,000 mortgage with an interest rate of 6% per annum for 15 years.
• So “P” would be $100,000.
• With “r” you will enter the monthly interest rate, so 0.06 (6%) divided by 12 gives 0.005 (0.5%).
• With “n” you would enter your total payments, pay once a month and pay over 12 years, which is 12*15 for 180.
• In this example, your complete equation would be: USA=$100,000 won0,005(first+0,005)180(first+0,005)180−first{displaystyle M=$100,000{frac {0.005(1+0.005)^{180}}{(1+0.005)^{180}-1}}}

Simplify the equation by adding 1 to the value “r.” Simplify your equation by adding 1 to the “r” inside the parentheses at the beginning and end of the first equation. This is a simple step to make your equation look much less complicated.

• After this step, your sample equation should look like this: USA=$100,000 won0,005(first,005)180(first,005)180−first{displaystyle M=$100,000{frac {0.005(1.005)^{180}}{(1,005)^{180}-1}}}

Solve exponents. The result inside parentheses, (1+ r), of the previous step must now be calculated to the power “n”. Again, this “n” represents the total number of payments. This step requires you to have a calculator with an exponential function, which is usually represented as follows: xy{displaystyle x^{y}} .

• This step is done by entering the value added in brackets, in this example (1,005), then pressing the exponent button, then typing the value “n” and pressing enter or =. In the example, the calculated result is 2,454.
• If your calculator doesn’t have an exponent function, you can type the calculated value in brackets into a Google search followed by the string ^(n), making sure to replace the “n” in brackets with the value value “n”. The search engine will help calculate this value for you.
• Note that only the value inside the brackets is exponential, not the “r” value outside the brackets (in front) or the -1 at the end of the equation.
• After this step, the sample equation will look like this: USA=$100,000 won0,005(2,454)2,454−first{displaystyle M=$100,000{frac {0.005(2,454)}{2,454-1}}}

Continue to simplify the equation. In the numeric element you will multiply the value “r” by the result calculated in the previous step, and for the denominator you will also subtract 1 from the result calculated from the previous step.

• The sample equation will look like this: USA=$100,000 won0,012271.454{displaystyle M=$100,000{frac {0.01227}{1.454}}}

Divide the numerator by the denominator. That is, divide the upper part of the equation by the lower part of the equation. The result will be a small decimal number.

• Following the example above, your equation should now be: USA=$100,000∗(0.008439){displaystyle M=$100,000*(0.008439)}

Multiply the “P” value by this result. This is the amount that needs to be paid monthly.

• In this example, the monthly payment would be ($100,000) * (0.008439), which is $843.90.

Schedule your depreciation. The amortization schedule will show you exactly how your monthly mortgage payment will be split between the principal and interest payments, and through the amortization schedule you will also know your balance at the end of the month. Start by entering basic information about your loan in the upper left of the spreadsheet program. For example, in cell A1 would enter “annual interest rate.” Then, enter your annual interest rate as a percentage in the next cell, cell B1. Continue down to cell A2, enter the loan period in the year, enter the time in column B as above. Do the same for the payment each year and the principal respectively in cells A3 and A4. ^[7]

Create amortized time column. After the loan information, leave a blank line. Then enter the following phrases in row 6 from column A to column E of the spreadsheet:

• Number of payments.
• Payment amount.
• Original payment amount.
• Interest payment.
• Loan balance. ^[8]

Enter the first month’s deductible expenses. Just below the newly titled columns, start filling in your loan information. Under the number of payments column, enter the number 1. Then, under the payment amount column, enter “=pmt(B1/B3,B2*B3,B4)”. This is the payment function. Under the original payment amount column, enter “=ppmt(B1/B3,A7,B2*B3,B4)”. This is the main payment function and this function helps to calculate the principal amount to be paid each month. Under the interest payment column, type “=ipmt(B1/B3,A7,B2*B3,B4)”. This is a function that calculates interest payments and displays the amount of interest payable each month. Finally, in the loan balance column, enter “=(B4+C7)”.

• Cell A7 should be your first payment, number 1.
• Cell C7 is the payment amount. ^[9]