It’s probably happened to you at one time: after computing a basic mortgage calculation, you’re unable to recreate your steps. Financial calculators make the calculations simple, but you can’t save the results or print them. However, you can do just that using a spreadsheet package.
If you are familiar with the process of compounding and discounting and know the basics of Microsoft Excel, you can solve financial calculations encountered every day using Excel. The spreadsheet program contains a number of "wizards" to walk you through a set of procedures step by step. The "Function Wizard" is one of the most useful for quickly making mortgage financing calculations such as mortgage payments, amortization schedules, effective interest rates, and more.
Assumptions
Loan amount $200,000
Interest rate 12%
Number of periods 30 years
Monthly payment $2,057.23
Getting Started
Since mortgage financing operations employ the same five variables—number of periods (N), periodic interest rate (I), present value (PV), periodic payment (PMT), and future value (FV)—given any four of these variables, you can solve for the fifth unknown variable. One easy way to solve many mortgage calculation problems quickly is to develop a spreadsheet template that incorporates these variables. Simply type those variables into a blank worksheet, which first appears when you open Excel. To change a column’s width to fit information on a single line, position the mouse over the right of the column heading and drag to the right.
Then, by entering the values for the known variables in column B, you can reference them in formulas to solve for the unknown variable. This approach makes it easy to perform a "what if" analysis so you can change one of the variables and a new solution automatically will be computed.
The following examples demonstrate some basic mortgage calculations using Excel 97.
Fully Amortizing, Constant Payment Mortgage—Monthly Payment
Calculate the monthly payment on a loan amount of $200,000 with an interest rate of 12 percent and a period of 30 years. To solve the problem, enter the assumptions into the input section of the template.
1. Move the cursor to cell B7, where the answer will be displayed. The cell is shaded to indicate it is a solution rather than an assumption.
2. Click on the Function Wizard icon (fx). A dialog box of available options appears. (If the Function Wizard box obscures information you want to see, click your mouse on its title bar, drag it out of the way, and release the mouse.
3. From the Function Category menu, select Financial.
4. From the Function Name menu, select PMT.
5. Click on the OK button to continue. A dialog box appears to guide you through the calculation.
6. At the rate prompt, click on cell B2 and type /12. B2 specifies the cell containing the requested information. The function needs the rate per period. Since you want to determine the monthly payment and 12 percent is an annual rate, you must divide by 12.
7. Press Tab to move to the next prompt.
8. At the nper prompt, click on cell B1 and type *12. B1 specifies the cell containing the requested information. B1 contains the number of years for the loan and you want the number of months, so you must multiply by 12.
9. Press Tab to move to the next prompt.
10. At the pv prompt, click on cell B3, which specifies the cell containing the requested information.
11. Press Tab to move to the next prompt.
12. At the fv prompt, leave blank. If fv is omitted it is assumed to be zero.
13. Press Tab to move to the next prompt.
14. At the type prompt, leave blank. The type prompt allows you to specify when the cash flows occur: "1" indicates that they occur at the beginning of each period, while "0" indicates that they occur at the end of each period. If omitted, it is assumed that cash flows occur at the end of each period.
15. Click on the OK button to close the Function Wizard. The periodic payment of $2,057.23 now should be displayed in cell B7.
Loan Amortization Schedule
Prepare a monthly loan amortization schedule for months one through six. Use the monthly payment computed above. You must complete all the steps to see complete calculations. Numbers may vary slightly due to rounding. Enter the following headings in cells A9 through E9: period, payment, interest, principal, and balance.
1. In column A (period), which specifies the amortization period, type 0 in cell A10.
2. Type 1 in cell A11.
3. Select cells A10 and A11.
4. Position the mouse pointer over the bottom right corner of cell A11 until the pointer turns into thin crosshairs. Drag it down to outline through cell A16 and release the mouse to complete the fill.
5. In cell B11, type =.
6. Click on cell B7. "B7" now will appear after the = in cell B11. This "links" cells B7 and B11; if the number in B7 changes, it automatically changes in B11 as well.
7. Press F4 on your keyboard to place dollar signs in front of both the letter and number address of the cell. This is called "absolute referencing" and is used when you want the formula in the cell to reflect the exact reference in the original cell.
8. Press Enter and the monthly payment of $2,057.23 appears in cell B11.
9. Drag from cell B11 down to outline through cell B16.
10. In cell C11, type =.
11. Click on cell B2, which then will appear after the = in cell C11.
12. Press F4 on your keyboard to place dollar signs in front of the cell letter and number.
13. In cell C11, type /12*E10. Dividing by 12 determines the monthly interest rate. The interest payment is calculated by multiplying the monthly rate by the loan balance at the beginning of the month.
14. Drag from cell C11 through cell C16.
15. In cell D11, type =B11-C11.
16. Press Enter.
17. Drag from cell D11 through cell D16. The principal payment is simply the periodic payment (column B) less the interest portion (column C) of the total payment.
18. In cell E10, type =-1*.
19. Click on cell B3 and B3 appears in cell E10.
20. Press F4 to place dollar signs in front of the cell letter and number.
21. Press Enter and the loan balance of $200,000 now appears in cell E10. Note that the loan amount is not preceded by a minus sign, because you multiplied by -1 to eliminate sign differences; this change simplifies the subsequent arithmetic.
22. In cell E11, type =E10-D11.
23. Drag from cell E11 through cell E16. The loan balance is determined by subtracting the amount of the principal payment made during the month (column D) from the loan balance outstanding at the beginning of the month (column E).
Now that the amortization template is set up, you might want to try some "what if" analyses. For example, click on cell B2 (periodic interest rate) and change 12 percent to 11 percent and press Enter. Notice that the monthly payment changes from $2,057.23 to $1,904.65 and the amortization schedule changes accordingly.
Outstanding Loan Balance
To calculate the outstanding loan balance at the end of five years, enter new assumption information into the input section of the template. The outstanding loan balance is equal to the present value of the remaining monthly payments over 25 years.
1. Move the cursor to cell B7.
2. Click on the fx icon.
3. From the Function Category menu, select Financial.
4. From the Function Name menu, select PV.
5. Click on the OK button to continue.
6. At the rate prompt, click on cell B2 and type /12 to determine the monthly interest rate.
7. Press Tab to move to the next prompt.
8. At the nper prompt, click on cell B1 and type *12 to get the number of months for the loan.
9. Press Tab to move to the next prompt.
10. At the pmt prompt, click on cell B4, which specifies the cell containing the requested information.
11. Press Tab to move to the next prompt.
12. At the fv prompt, leave blank to assume zero.
13. Press Tab to move to the next prompt.
14. At the type prompt, leave blank to assume that cash flows occur at the end of each period.
15. Click on the OK button to close the Function Wizard. The outstanding loan balance of $195,326.89 is displayed in cell B7.
Alternatively, you could calculate the solution to the problem using future value. To do so, change the number of periods from 25 years to five years, and insert the original loan amount of $200,000 in the input section of the template.
1. Move the cursor to cell B7, where the answer will be displayed.
2. Click on the fx icon.
3. From the Function Category menu, select Financial.
4. From the Function Name menu, select FV.
5. Click on the OK button to continue.
6. At the rate prompt, click on cell B2 and type /12 to determine the interest rate.
7. Press Tab to move to the next prompt.
8. At the nper prompt, click on cell B1 and type *12 to get the number of months for the loan.
9. Press Tab to move to the next prompt.
10. At the pmt prompt, click on cell B4, which specifies the cell containing the requested information.
11. Press Tab to move to the next prompt.
12. At the pv prompt, click on cell B3, which specifies the cell containing the requested information.
13. Press Tab to move to the next prompt.
14. At the type prompt, leave blank to assume that cash flows occur at the end of each period.
15. Click on the OK button to close the Function Wizard. The outstanding loan balance of $195,326.04 is displayed in cell B7. The slight difference in this answer from the previous one is due to rounding.
Loan Fees and Effective Borrowing Cost
Using the same $200,000 loan, calculate the effective interest rate to the borrower—which is yield, or internal rate of return (IRR), to the lender—assuming the lender charges two points ($4,000) and the loan is held to maturity. Assuming that the borrower pays the points from separate funds, the lender actually "invests" $196,000 ($200,000 - $4,000). Enter the new assumptions in the input section of the template.
1. Move the cursor to cell B7, where the answer will be displayed.
2. Click on the fx icon.
3. From the Function Category menu, select Financial.
4. From the Function Name menu, select Rate.
5. Click on the OK button to continue.
6. At the nper prompt, click on cell B1 and type *12 to get the number of months for the loan.
7. Press Tab to move to the next prompt.
8. At the pmt prompt, click on cell B4, which specifies the cell containing the requested information.
9. Press Tab to move to the next prompt.
10. At the pv prompt, click on cell B3, which specifies the cell containing the requested information.
11. Press Tab to move to the next prompt.
12. At fv prompt, leave blank to assume zero.
13. Press Tab to move to the next prompt.
14. At the type prompt, leave blank to assume that cash flows occur at the end of each period.
15. Click on the OK button to close the Function Wizard. The effective monthly interest rate of 1 percent now is displayed in cell B7.
16. Calculate the effective annual interest rate, compounded monthly, by typing *12 at the end of the rate function in cell B7.
17. Press Enter. The effective annual interest rate of 12.27 percent, compounded monthly, appears in cell B7.
18. Click on the Increase Decimal icon to display additional decimal places.
Loan Fees, Early Payoff, and Effective Borrowing Cost
Calculate the effective interest rate to the borrower (yield, or IRR, to the lender), assuming the lender charges two points ($4,000 that the borrower pays from separate funds) and the loan is paid off in five years. The initial step is to calculate the loan balance at the end of five years; the outstanding loan balance of $195,326.89 was calculated earlier. The second step is to calculate the effective interest rate, compounded monthly. Enter the new assumptions in the template.
1. Move the cursor to cell B7, where the answer will be displayed.
2. Click on the fx icon.
3. From the Function Category menu, select Financial.
4. From the Function Name menu, select Rate.
5. Click on the OK button to continue.
6. At the nper prompt, click on cell B1 and type *12 to get the number of months for the loan.
7. Press Tab to move to the next prompt.
8. At the pmt prompt, click on cell B4, which specifies the cell containing the requested information.
9. Press Tab to move to the next prompt.
10. At the pv prompt, click on cell B3, which specifies the cell containing the requested information.
11. Press Tab to move to the next prompt.
12. At fv prompt, click on cell B5, which specifies the cell containing the requested information.
13. Press Tab to move to the next prompt.
14. At the type prompt, leave blank to assume that cash flows occur at the end of each period.
15. Click on the OK button to close the Function Wizard. The effective monthly interest rate of 1 percent now is displayed in cell B7.
16. Calculate the effective annual interest rate, compounded monthly, by typing *12 at the end of the rate function in cell B7.
17. Press Enter. The effective annual interest rate of 12.55 percent, compounded monthly, should appear in cell B7.
18. Click on the Increase Decimal icon to display additional decimal places.
Market Value of a Loan
Using a market interest rate of 16 percent, calculate the market value of the loan, assuming that it is held to maturity. Enter the assumptions in the template.
1. Move the cursor to cell B7, where the answer will be displayed.
2. Click on the fx icon.
3. From the Function Category menu, select Financial.
4. From the Function Name menu, select PV.
5. Click on the OK button to continue.
6. At the rate prompt, click on cell B2 and type /12 to determine the monthly interest rate.
7. Press Tab to move to the next prompt.
8. At the nper prompt, click on cell B1 and type *12 to get the number of months for the loan.
9. Press Tab to move to the next prompt.
10. At the pmt prompt, click on cell B4, which specifies the cell containing the requested information.
11. Press Tab to move to the next prompt.
12. At the fv prompt, leave blank to assume zero.
13. Press Tab to move to the next prompt.
14. At the type prompt, leave blank to assume that cash flows occur at the end of each period.
15. Click on the OK button to close the Function Wizard. The market value of $152,981.54 now should be displayed in cell B7.
Balloon Payment—Partially Amortizing, Constant Payment Mortgage
Calculate the balloon payment at the end of year 10 using the previously mentioned assumptions on a loan amortized over 30 years, with monthly payments of $2,057.23 due in 10 years. Enter the new assumptions in the template.
1. Move the cursor to cell B7, where the answer will be displayed.
2. Click on the fx icon.
3. From the Function Category menu, select Financial.
4. From the Function Name menu, select FV.
5. Click on the OK button to continue.
6. At the rate prompt, click on cell B2 and type /12 to determine the monthly interest rate.
7. Press Tab to move to the next prompt.
8. At the nper prompt, click on cell B1 and type *12 to get the number of months for the loan.
9. Press Tab to move to the next prompt.
10. At the pmt prompt, click on cell B4, which specifies the cell containing the requested information.
11. Press Tab to move to the next prompt.
12. At the pv prompt, click on cell B3, which specifies the cell containing the requested information.
13. Press Tab to move to the next prompt.
14. At the type prompt, leave blank to assume that cash flows occur at the end of each period.
15. Click on the OK button to close the Function Wizard. The outstanding loan balance of $186,834.89 now is displayed in cell B7.
Just the Basics
These examples illustrate the simplicity of using Excel to make a variety of basic mortgage financing calculations. These examples do not demonstrate the full range of options and computing power available in Excel.