Create Excel Formula to Match Mortgage Payment Data
In this article, we will discuss how to create an Excel formula to match mortgage payment data, specifically focusing on a scenario where the interest rate is 4.06% and the regular monthly payment is £977.02 (with the last two payments being £1,977.02). By the end of this article, you will have a better understanding of how to work with Excel formulas to manipulate and analyze financial data.
Understanding the Mortgage Payment Structure
Before we dive into creating the formula, it is essential to understand the structure of mortgage payments. A mortgage payment typically consists of two components: principal repayment and interest payment. The principal repayment reduces the outstanding loan balance, while the interest payment is calculated based on the remaining balance and the interest rate.
Calculating the Mortgage Payment
To calculate the mortgage payment, we can use the following formula:
M = P * r(1 + r)^n / ((1 + r)^n - 1)
Where:
M: the monthly paymentP: the principal loan amountr: the monthly interest rate (annual interest rate divided by 12)n: the number of payments (loan term in years multiplied by 12)
However, in our scenario, we already know the monthly payment (M) and want to find the principal loan amount (P). To do this, we can rearrange the formula as follows:
P = M * ((1 + r)^n - 1) / (r(1 + r)^n)
Now that we have the formula to calculate the principal loan amount, let's proceed to create an Excel formula to match the mortgage payment data.
Creating the Excel Formula
Assuming you have entered the interest rate (as a decimal) in cell A1, the number of payments in cell A2, and the regular monthly payment (excluding the last two larger payments) in cell A3, you can create the following Excel formula to calculate the principal loan amount:
=A3 * ((1 + A1)^A2 - 1) / (A1 * (1 + A1)^A2)
To calculate the total mortgage payment, including the last two larger payments, you can use the following formula:
=A3 * (A2 - 1) + A3 * 2
In this article, we learned how to create an Excel formula to match mortgage payment data. By understanding the mortgage payment structure and rearranging the mortgage payment formula, we were able to calculate the principal loan amount and the total mortgage payment. Here's a quick recap of the formulas we used:
- Principal loan amount formula:
=A3 * ((1 + A1)^A2 - 1) / (A1 * (1 + A1)^A2) - Total mortgage payment formula:
=A3 * (A2 - 1) + A3 * 2