Mortgage Repayments with Variable Rates in Excel
In this guide, learn how to calculate mortgage repayments with variable interest rates in Excel. By using functions like PMT and HLOOKUP, you can adjust payments as interest rates change over time, giving you a clear view of future payments.
Step-by-Step Instructions for Variable Rates
1. Input Mortgage Years and Rates
- Enter the years and their respective interest rates at the top of your sheet. For example, 5% for year one, 4.2% for year two, etc.
2. Enter Loan Amount and Term
- Enter the loan amount (e.g., £200,000) and the loan term (e.g., 25 years) in designated cells.
3. Calculate Monthly Payments Using PMT
- Use
=PMT(rate/12, term*12, loan_amount)to calculate monthly payments. Adjust the rate and term references as needed.
4. Set Up an HLOOKUP for Interest Rates
- Create an HLOOKUP formula to match each year in the schedule with the interest rate from the top table. This helps apply the correct rate for each period.
Final Tips on Calculating Variable Rate Mortgages
Calculating mortgage repayments with variable rates in Excel allows you to see how changes in interest affect your monthly payments. By setting up an HLOOKUP and calculating payments with PMT, you can stay prepared for adjustments over the life of the mortgage.