Creating A Mortgage Table: Excel Magic

Creating a mortgage table in Excel is a useful way to calculate and schedule your loan repayments. You can use a mortgage calculator to input variables such as interest rates, loan amounts, and loan terms to determine your monthly payments. Excel's PMT function can be used to calculate the total amount of a periodic payment, helping you to understand the interest rate, loan calculation, decomposition of the loan, amortization, and monthly payment. This can be done in three steps: determining the monthly payment, calculating the interest rate, and creating a loan schedule.

Using the PMT function

The PMT function in Excel is a financial function that calculates the total payment (principal and interest) required to settle a loan or mortgage with a fixed interest rate over a specific time period. It is used to determine the payments owed by a borrower to a lender.

To use the PMT function, you will need to input the following arguments: Rate (required), Nper (required), and Pv (required). Rate refers to the interest rate of the loan, Nper is the total number of payments for the loan, and Pv is the present value or the total amount that a series of future payments is worth now. You can also include Fv (optional) and Type (optional). Fv is the future value or a cash balance you want to attain after the last payment, and Type is the type of day count basis to use.

For a mortgage, the interest rate is typically annual, and the terms are stated in years. Therefore, to normalize inputs to monthly, you need to divide the annual interest rate by 12 to get the period rate and multiply the term by 12 to get the total number of periods. For example, if you have a 30-year mortgage with an interest rate of 7% and a loan amount of $450,000, the formula would be: =PMT(C5/12,C6*12,-C9) =PMT(0.07/12,30*12,-450000). The PMT function returns a monthly payment of $2,994 in this case.

Additionally, if you prefer to see the result as a positive number, you can use a minus sign before the PMT function. For instance, in the previous examples, if you wanted to enter the total number of payments due, you would calculate the number of years in the loan term multiplied by the number of payments per year. However, to simplify, you can select the payment frequency from a drop-down list of options, and Excel will calculate the number of payments per year for you.

Tracking interest rate

Step 1: Understanding the Components

Before creating the table, it's essential to understand the components that will be involved in your calculations. These components include:

• Interest Rate: This is the annual interest rate associated with your mortgage. It represents the cost of borrowing and is typically expressed as a percentage.
• Number of Payments per Year: This refers to how frequently you make payments on your mortgage. It could be monthly, quarterly, or even bi-weekly.
• Loan Amount: This is the total amount of money borrowed, which serves as the principal of the loan.
• Loan Term: The duration of your loan, usually stated in years, defines the length of time you have to repay the loan.

Step 2: Setting up the Excel Worksheet

Open Microsoft Excel and select 'Blank Workbook' to start a new spreadsheet. Designate the 'A' column as your 'Categories' column, where you will list the different components mentioned above. You can adjust the column width by clicking and dragging the divider between columns 'A' and 'B' to ensure you have sufficient space for entering data.

Step 3: Entering the Data

In the 'B' column, directly to the right of your 'Categories' column, you will input the corresponding values for each category. For instance, if your 'Categories' column has 'Annual Interest Rate' in one of the cells, the adjacent cell in the 'B' column should contain the specific interest rate percentage applicable to your mortgage. Repeat this process for all relevant categories, including the loan amount, loan term, and number of payments per year.

Step 4: Calculating Monthly Payments

To determine your monthly payments, you can utilise the PMT function in Excel. The formula for this calculation is:

> = -PMT(Interest Rate/Payments per Year, Total Number of Payments, Loan Amount, 0)

For example, if your annual interest rate is 5%, your total number of payments is 360 (for a 30-year mortgage with 12 monthly payments per year), and your loan amount is $200,000, the formula would be:

> "= -PMT(5%/12, 360, $200,000, 0)"

Step 5: Tracking Interest Rate Changes

If you anticipate changes in the interest rate over the course of your mortgage, you can incorporate this into your table. Create additional columns labelled "Interest Component" and "Principal Component". In the 'Interest Component' column, you can calculate the interest cost for each payment by multiplying the interest rate for that period by the remaining loan balance. The 'Principal Component' column will then subtract this interest cost from the original principal, giving you the new principal balance.

Step 6: Automating Calculations

Excel allows you to automate your calculations by using functions like 'Goal Seek'. This feature enables you to adjust values and have the spreadsheet automatically recalculate the results. For instance, if you change the interest rate or the loan amount, the spreadsheet will update the monthly payment amount accordingly.

By following these steps, you can create a dynamic mortgage table in Excel that effectively tracks interest rates and provides valuable insights into your financial commitments.

Calculating monthly payments

To calculate monthly mortgage payments in Excel, you can use the PMT function. This function calculates the monthly payment for a loan, given the loan amount, interest rate, and repayment time. The formula for the PMT function is:

> PMT(rate, nper, pv, [fv], [type])

Here, "rate" refers to the interest rate, "nper" is the total number of payment periods, "pv" is the present value of the loan, "fv" is the future value of the investment, and "type" specifies the timing of the payments.

To get the number of periods (nper), multiply the term in years by the periods per term. For example, for a 30-year mortgage with 12 monthly payments per year, the nper would be 30 * 12. To get the present value (pv), use the loan amount with a minus sign to indicate a cash outflow. For example, if the loan amount is $450,000, the pv would be -450,000.

Putting it all together, the formula for calculating the monthly payment for a 30-year mortgage with a 7% interest rate and a loan amount of $450,000 would look like this:

> =PMT(rate/12, nper, -loan amount)

> =PMT(7%/12, 30*12, -450000)

> =PMT(0.07/12, 360, -450000)

> =2994

So, the monthly payment for this mortgage would be $2,994.

You can also include additional factors in your calculations, such as down payment, property taxes, homeowner's insurance, and other fees, to get a more accurate estimate of your monthly mortgage payments.

Creating a loan amortization schedule

To create a loan amortization schedule in Excel, you can either create your own or download a template. To create your own, open a new Excel spreadsheet and create a "Categories" column. You will need eight cells in total for the following categories: loan amortization, monthly payments, monthly principal, monthly interest, the amount remaining, interest component, principal component, and additional principal.

Next, enter your values in the column directly to the right of the "Categories" column. You can then use the PMT function in Excel to create the formula for calculating monthly payments: PMT(rate, nper, pv, [fv], [type]). This formula lets you calculate the monthly payments when you divide the annual interest rate by the number of months in a year.

You can also download a free, pre-made amortization schedule template from Microsoft. This template makes it easy to calculate the total interest and total payments and even includes the option to add extra payments. Once downloaded, you can add your own data to the template.

Summing the payment schedule

Summing up the payment schedule is the final step in creating a mortgage calculator in Excel. This step involves adding up the payments, interest, and principal at the bottom of the table. It is important to cross-reference these values with your mortgage calculator to ensure they match. A correct formula implementation will result in the principal matching the original loan amount, the payments matching the total cost of the loan, and the interest matching the interest cost from the mortgage calculator.

To create a mortgage calculator in Excel, you can use the PMT function, which stands for Payment. This function calculates the periodic payment for a loan based on a constant interest rate and constant payments made throughout the life of the loan. The formula for the PMT function is as follows: PMT(rate, nper, pv, [fv], [type]). In this formula, "rate" refers to the interest rate per period, "nper" is the total number of payment periods, "pv" is the present value or the total amount that a series of future payments is worth now, "fv" is the future value or the cash balance you want to attain after the last payment is made, and "type" specifies when the payments are due (beginning or end of the period).

Additionally, you can use the IPMT function to calculate the interest portion of a given loan payment and the PPMT function to calculate the principal portion. The IPMT function uses the following formula: IPMT(rate, per, nper, pv, [fv], [type]). The PPMT function is written as PPMT(rate, per, nper, pv, [fv], [type]). In these formulas, "per" represents the payment for which you want to find the interest or principal.

It is worth noting that in older versions of Excel (Excel 2019 and older), creating a payment schedule with a single formula is not possible due to a lack of support for dynamic arrays. However, it is still achievable by building out the mortgage payment schedule one formula at a time.

Frequently asked questions