Wondering how to do a what-if analysis for a mortgage decision? Here is a simple way to do so using a Microsoft Excel’s spreadsheet. To begin with you must understand the Excel’s PMT function, which helps calculate the amount of yearly payments, taking interest rate, total number of yearly payments, and the principal amount as parameters.
If you know the amount of yearly payment that you can comfortably afford, it would be easy for you to try different combinations of rate and loan amount. This way you can easily determine how much you can afford to buy and what rate you should be looking for while shopping for an ideal mortgage loan.
What are the different ways of doing financial analysis?
An Example
To illustrate what-if analysis using an Excel spreadsheet, let us assume you can afford to pay $500 every month in mortgage payments. Now if you know the loan amount, you can find out the interest rate, keeping the number of yearly payments constant. Conversely, you can determine the maximum loan amount you can afford if you are being offered a particular interest rate.
When you know the loan amount
Suppose your home financing need is $100,000 and you want to take a mortgage loan for 30 years. You know the monthly payment you are comfortable with, which is $500 per month or $6,000 per year. To use Excel’s PMT function, you need to know the interest rate. However, you know the result rather than the interest rate. Here comes the help from Excel’s goal seek.
Select the cell where you have placed the PMT function and click Goal Seek, which you find in the What-If Analysis group of options. A small window opens, asking you three input values. The ‘Set Cell’ will already have the correct value, which is the name of the cell containing the formula. The ‘To Value’ needs the value that you want to see as a result of the calculation. In this case, it is $6,000, which must be written with a minus sign in front of it. The ‘By changing cell’ is the cell name, the value of which you want to know.
Click the ‘OK’ button to find the interest rate you should be looking for.
How to shop around for the best mortgage rates?
When you know the interest rate
Suppose you are being offered an interest rate of 5.5% per annum by a lender. Now, you want to know the amount you can affordably borrow for a home purchase. Assume that the loan term is 30 years and the amount you can afford to pay each month is $500.
Do the same as you did when you wanted to know the interest rate for a particular loan amount. In this case, you only need to change the third variable, ‘By changing cell’, which is the name of the cell where you have written the loan amount. By clicking the ‘OK’ button on the Goal Seek window you will see the maximum loan amount that you can afford at 5.5% per annum for 30 years by paying $500 every month.
