Financial Model for Optimal Mortgage

I made a financial model that can help decide which mortgage is ideal. There are multiple ways to do this but I found this to be the most efficient. When deciding which mortgage to choose, there are three factors that you want to consider. First, you would need to come up with a monthly budget and ensure that the mortgage would fit into this budget. Next, you will need to have enough money for fees. The last comparison we are going to make is between each mortgage's EAR. We want to be paying the lowest EAR possible because that will make the cost of borrowing more affordable.

The only tool needed is Microsoft Excel

These numbers should be given when looking to buy a house.

In order to perform this step, we will need to look at the origination fees and discount points. We are simply going to add these two inputs together and convert it into a percent. You can see how I did this in the formula bar at the top of the page.

We need to use the PMT function in excel to perform this step. It is going to do most of the work for us, we just need to know the correct inputs. Our first input is going to be the rate which in this case is going to be the APR divided by 12 for monthly compounding. Next, excel wants us to find the number of periods which is going to be the amount of years multiplied by 12 again. Lastly, we are going to input the present value of the loan which is $183,800 in this scenario. There is no future value which is why we input a zero next, and the type is 1 because the payments occur at the beginning of the period. Refer to the formula bar at the top of the page.

I have included this link which describes how to use the payment function in depth: https://support.microsoft.com/en-us/office/pmt-function-0214da64-9a63-4996-bc20-214433fa6441

We will need to calculate the closing costs for this step. In order to make this calculation, you will need to multiply the loan by the sum of fees as a %. As you can see in my formula bar, I locked in the cell for the loan because I know that I will be using this formula for Mortgage B.

Closing costs- Closing costs are fees paid at the closing of a real estate transaction

A guide of why we lock in cells- https://www.ablebits.com/office-addins-blog/relative-absolute-reference-excel/

The amount required to bring to closing is essential when figuring out if you have enough cash flow to afford the mortgage. In order to find this, we will subtract the number we found in step 4 from the initial loan. This is done with a simple subtraction formula found at the top of the picture reference.

This number will give us the monthly rate on the interest of our loan. In order to do this, we will need to use the RATE function. I have shown the inputs in the picture above

How to use the RATE function: https://support.microsoft.com/en-us/office/rate-function-9f665657-4a7e-4bb7-a030-83fc59e748ce

Our final step will be to find the EAR. This is the number that will determine which mortgage is optimal. We want the lowest EAR out of all the options so that we aren't compounding as much interest. We will use the EFFECT function as shown above.

How to use the EFFECT function: https://support.microsoft.com/en-us/office/effect-function-910d4e4c-79e2-4009-95e6-507e04f11bc4