Excel Mortgage

Open Microsoft Excel.

Build the calculator as shown in the screenshot. (You can click any of the screenshots to enlarge them). You don't need to do the "Reference" column if you don't want to - in this exercise, these variables are simply present to help you work out the equations. The variables will be used to tell you how to input functions.

Enter your values. For this exercise, the screenshots will show a $500, 000 loan at 7% interest over a 30-year period, with 12 payments per year (or one per month). For your mortgage, enter the appropriate values.

Figure out the total number of payments. As you can see in the reference list, the total number of payments is c (life of the loan) multiplied by d (number of payments per year). In this specific spreadsheet, the function for B9 is B7 * B8.

Calculate the monthly payment. To figure out how much you must pay on the mortgage each month, use the PMT function. For this spreadsheet, the formula is -PMT(B6/B8, B9, B5, 0). If your values are slightly different, input them with the appropriate cell numbers. (The reason you can put a minus sign in front of PMT is because PMT returns the amount to be deducted from the amount owed.)

Calculate the total cost of the loan and the interest cost. The total cost of the loan will be f (payment per period) multiplied by e (total number of payments). Total interest cost is g (total cost of the loan) minus (initial loan amount). Your mortgage calculator is complete.