How to Create a Mortgage Calculator With Microsoft Excel California

Steps

1. Start Microsoft Excel.
2. Input all headings as shown in the screen shot. Mortgage Calculator headings.
3. Input, for the purposes of this exercise, a loan amount of $150,000, an interest rate of 7.5%, over a 20 year period, and payments to be made monthly. Starting in cell B6, enter the necessary amounts. Basic Information
4. description
5. Put in the total number of payments. Multiply cells B8 and B9 together.
6. Next, calculate the monthly payment and for that, use the PMT function. Type =-PMT(B7/B9,B10,B6) or =ABS(PMT(B7/B9,B10,B6)). Both ways result in the same value. 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.
7. Calculate the total value of the payments in B12. To do this, multiply the monthly payment with the total number of payments.
8. Calculate the cost of the mortgage by subtracting the total value of payments in B12 with the mortgage amount.
9. Now with all the summary information in place, proceed to the main part of the exercise. Summary Information Complete
10. In cell E7, type 1. This represents the 1st payment.
11. In cell F7, type =B6. This is the starting balance.
12. In cell G7, type =SUM(F7*$B$7/$B$9). This calculates the amount of interest on the loan that needs to be paid. AutoFill down to row 246.
13. In cell H7, type =IF(F7>($B$11-G7),$B$11-G7,F7). On the surface, this calculates the amount of money left over from the monthly payment after the interest amount has been subtracted off. But it also checks to see if the balance of the loan is greater than the monthly payment less interest. If it is, then it calculates how much will be applied to principle, else, it puts a 0. AutoFill down to row 246.
14. For column I, default it to the value 0. AutoFill down to row 246.
15. In cell J7, type =F7-(H7+I7). This formula takes the balance of the loan and subtracts the amount of money being paid to the principle, plus any extra money. AutoFill down to row 246. 1st Payment
16. In cell F8, type the formula =J7. This will make sure that the balance of the loan for each successive period will be equal to what is remaining from the previous period. AutoFill down to row 246.
17. Go back to the top of the spreadsheet and click on cell E8.
18. In cell E8, type =IF(F8>0,E7+1,0). This formula checks to see if the balance of the loan is greater than 0 and if it is, to increment the previous period cell value by 1. If the balance of the loan is 0, then the formula puts in a 0. Autofill down to row 246.
19. In cell D7, put in today's date. AutoFill down to D246.
20. By default, Excel created a series of consecutive days. Click the AutoFill Options button and change the fill options to Fill Months. Fill Months
21. In cell I24, put in an extra payment of $20,000.
22. Notice the change. The mortgage is paid off as of Payment 185 instead of 240.
23. Let's drill additional information out. In cell A15, type in the heading "Observed Payments".
24. In cell A16, type "Cost of Mortgage" and in cell A17, type "Total Payments".
25. In cell B16, type =SUM(G7:G246). By putting an extra $20,000 against the principle at that point in the mortgage, $46,000 is saved in costs.
26. In cell B17, type =MAX(E7:E246). This formula states the total number of payments by searching for the largest value in the column. Observed Information

Tips

• To AutoFill, select the range of cells in which the information will be needed. Then click on the Edit menu, go down to Fill, and then choose the appropriate fill option.

Warnings

• The - sign in front of the PMT function is necessary else the value will be negative. Also, the reason the interest rate is divided by the number of payments is because the interest rate is for the year, not month.

Things You'll Need

• Microsoft Excel
• A mortgage or be shopping for one.

Article provided by wikiHow, a wiki how-to manual. Please edit this article and find author credits at the original wikiHow article on How to How to Create a Mortgage Calculator With Microsoft Excel. All content on wikiHow can be shared under a Creative Commons license.