Mortgage Repayment Formula Excel

Understanding the Mortgage Repayment Formula in Excel

The mortgage repayment formula is a fundamental concept in personal finance, and Excel provides an efficient way to calculate and visualize mortgage repayments. The formula takes into account the loan amount, interest rate, and repayment period to determine the monthly payment amount. In this article, we will delve into the details of the mortgage repayment formula in Excel, exploring its components, calculation, and application.

Components of the Mortgage Repayment Formula

The mortgage repayment formula involves several key components: * Loan Amount (P): The initial amount borrowed from the lender. * Interest Rate ®: The annual interest rate charged on the loan, expressed as a decimal. * Repayment Period (n): The number of payments made over the life of the loan, typically expressed in months. * Monthly Payment (M): The amount paid each month to repay the loan.

Calculating the Monthly Payment

The mortgage repayment formula is as follows: M = P [ i (1 + i)^n ] / [ (1 + i)^n – 1] Where: * M = monthly payment * P = loan amount * i = monthly interest rate (annual interest rate / 12) * n = number of payments

To calculate the monthly payment in Excel, you can use the PMT function: =PMT(rate, nper, pv, [fv], [type]) Where: * rate = monthly interest rate * nper = number of payments * pv = loan amount * fv = optional future value (set to 0 for a loan) * type = optional payment type (set to 0 for end-of-period payments)

Example Calculation

Suppose you borrow 200,000 at an annual interest rate of 4% over a 30-year period. To calculate the monthly payment: * Loan Amount (P) = 200,000 * Interest Rate ® = 4%/year = 0.04 * Monthly Interest Rate (i) = 0.04/12 = 0.003333 * Repayment Period (n) = 30 years * 12 months/year = 360 months * Monthly Payment (M) = 200,000 [ 0.003333 (1 + 0.003333)^360 ] / [ (1 + 0.003333)^360 – 1] ≈ 955.66

In Excel, you can use the PMT function to calculate the monthly payment: =PMT(0.003333, 360, 200000, 0, 0) ≈ $955.66

Visualizing Mortgage Repayments in Excel

To visualize the mortgage repayment schedule, you can create a table with the following columns: * Month * Payment * Interest * Principal * Balance

Using the mortgage repayment formula, you can calculate the interest and principal components of each payment and update the balance accordingly.

...

Month	Payment	Interest	Principal	Balance
1	$955.66	$666.67	$289.00	$199,711.00
2	$955.66	$665.13	$290.53	$199,420.47

📝 Note: The interest and principal components of each payment will vary over time, with the interest portion decreasing and the principal portion increasing as the loan is repaid.

Conclusion and Final Thoughts

In conclusion, the mortgage repayment formula is a powerful tool for calculating and visualizing mortgage repayments in Excel. By understanding the components of the formula and how to apply it in Excel, you can make informed decisions about your mortgage and create a personalized repayment schedule. Whether you’re a homeowner, a financial advisor, or simply looking to manage your debt, the mortgage repayment formula is an essential concept to grasp.