Need to figure out how much home you can afford? Let’s create a mortgage calculator in excel. Want it be flexible and geared towards your needs? Your best bet is to create a Mortgage Template that you can easily customize. In this tutorial, we will review how to create a mortgage calculator in excel. Before continuing, please make sure you are familiar with how to use the PMT function.
Please download the template above to follow along.
I have populated the highlighted cells in yellow to complete the calculation for this example. Once the fields are completed, the PMT Function is used to calculate the Payment due for each period.
By taking the Sum of all payments in Cell B12 and subtracting it from the starting loan in Cell B2, you can easily determine the total cost of interest over the life of the loan.