How to Create Your Own Mortgage Calculator in Microsoft Excel

When you are planning to purchase a home, it is important to know how much mortgage payment you can afford each month. Using a mortgage calculator, you can specify the terms, loan amount and interest rate, view the monthly payment and compare the amount with your finances to determine whether you can afford the loan. Microsoft Excel includes a function to help analyze your financial situation. The PMT Function allows you to specify the loan's criteria and calculate the monthly payment for your mortgage.

Instructions

1. Gather information about your potential loan. This information includes the loan terms, loan amount and interest rate.

2. Open Microsoft Excel and click cell "A1." Type "Interest Rate" and press the "Enter" key on your keyboard.

3. Click cell "B1" and type "Terms." Press "Enter."

4. Click cell "C1" and type "Loan Amount." Press the "Enter" key.

5. Click cell "D1" and type "Monthly Payment." Press "Enter."

6. Click cell "A2" and right-click. Click "Format Cells." The Format Cells dialog box opens on your screen. Click the "Number" tab and click "Percentage." Increase the amount of decimal places to "2" and click "OK."

7. Click cell "C2" and click the "Home" tab. Click the "$" button in the "Number" group to format the cell to display as currency.

8. Type the interest rate of your loan in cell "A2." The rate should display as a percentage.

9. Type the terms of your loan in cell "B2." Generally, mortgage loans are 15, 20, 25 or 30 years.

10. Type the amount of your loan in cell "C2." The amount should display as a dollar amount.

11. Click cell "D2" and click the "Insert Function" button. The Insert Function button is located to the right of the text field and looks like an "fx."

12. Type "PMT" in the "Search for a Function" field and click "Go."

13. Click "PMT" from the list of results and click "OK." The Function Arguments dialog box appears on your screen.

14. Click the "Rate" field and type "A2/12."

15. Click the "Nper" field and type "B2*12."

16. Click the "Pv" field and type "C2."

17. Click the "OK" button. Excel calculates the monthly payment and displays the value in cell "D2."

References

• Microsoft Office: PMT Function