Introduction

Adjustable rate mortgages (ARM) provide homebuyers with a relatively low interest rate at the beginning of their loan term. However, this rate can change over time, making it slightly more complicated than a fixed-rate mortgage. To effectively manage your adjustable-rate mortgage, it’s essential to understand your loan and how to calculate it in Excel. In this article, we will go through the step-by-step process of calculating an ARM in Excel, so you can confidently track and forecast your future payments.

Step 1: Gather Required Information

Before starting, gather the following information:

– Loan amount

– Initial interest rate

– Index rate

– Margin

– Adjustment period

– Rate cap structure

You will need this data to create your adjustable-rate mortgage excel spreadsheet.

Step 2: Create Your Spreadsheet

Open Microsoft Excel and set up columns for each required piece of information:

1. Column A – Period Number

2. Column B – Interest Rate

3. Column C – Monthly Payment

4. Column D – Principal Paid

5. Column E – Interest Paid

6. Column F – Remaining Balance

Step 3: Enter Initial Loan Data

Input the initial loan data in the relevant columns:

1. In cell A2, enter “1” for the first payment period.

2. In cell B2, input your initial interest rate (divided by 12 for a monthly rate).

3. Enter your loan amount in cell F2.

Step 4: Calculate Monthly Payment

1. In cell C2, input a formula as follows:

= -PMT(B2;, *adjustment_period*, F2)

This will calculate the first set of monthly payments based on the initial interest rate and loan balance.

Step 5: Calculate Principal & Interest Paid for Each Period

1. In cell D2, input this formula:

=PPMT(B2, A2, *adjustment_period*, F2)

2. In cell E2, input the formula:

=IPMT(B2, A2, *adjustment_period*, F2)

These formulas will calculate the principal and interest paid for each payment period.

Step 6: Calculate Remaining Loan Balance

1. In cell F3, input this formula:

=F2-D2

This will determine the remaining loan balance after the first payment.

Step 7: Populate Future Periods

Drag down your formulas in cells A3 through F3 depending on the length of your loan term. However, make sure to adjust the interest rate according to your index rate, margin rate, and cap structure using an IF condition for the adjustment periods.

Step 8: Analyzing Your Data

Once you have populated all cells in your spreadsheet based on the loan term, you will have a comprehensive adjustable-rate mortgage schedule. By analyzing this data, you can see how your payments and interest rates will change over time and create a budget plan accordingly.

Conclusion

Calculating an adjustable-rate mortgage in Excel may seem complex initially, but once you understand the formulas and process involved, it is a helpful tool in managing your mortgage. Use this guide to help stay informed about your loan’s progression and make strategic financial decisions in the future.