We build a variable rate mortgage repayment calculator to account for changes in mortgage rates throughout the mortgage term. This is more realistic than a fixed mortgage repayment calculator which assumes that the mortgage rate remains the same throughout the term. We start by adding our mortgage inputs, including what the different mortgage rates will be. We then build an amortisation table to calculate our outputs. Here, we use the VLOOKUP function to return the applicable interest rate in each month and the PMT function to calculate the monthly mortgage payment. Finally, we insert charts to show how our interest and principal change over time.
DISCLAIMER: Although I have taken great care to check all of the calculations, I am not a qualified accountant or advisor and you should not use this video to substitute financial advice. Make sure you talk to your own accountant or financial advisor before you use this calculator to make any financial decisions.
See Fixed Mortgage Repayment Calculator Video:
https://youtu.be/v6n4wWXy5Us
Subscribe for weekly Excel tutorials, techniques and examples:
https://www.youtube.com/channel/UCgdRm6hepAn6Y0FqMZPLiAw?sub_confirmation1
Please share this video with others that may find it useful:
https://youtu.be/zgRw71SgYbo
Please comment below if you have any feedback or any suggestions for videos you would like us to upload next!
The Excel Hub website: https://theexcelhub.com/
Instagram: https://www.instagram.com/theexcelhub/
ABOUT US:
The Excel Hub seeks to enhance your Excel and VBA knowledge by providing value-adding tutorials, techniques and examples in order to help you take your spreadsheet and data analysis skills to the next level.
For any enquiries, please contact:
[email protected]Overview: (0:00)
Inputs: (0:45)
Amortisation Table: (1:39)
Outputs: (5:08)
Graphs: (5:51)