Create a spreadsheet in Excel that will keep track of the mortgage unpaid balance over time. First, we will set up the following columns for a 30-year mortgage of $250,000 with an annual interest rate of 5.10%.
Period – Keeps track of the number of periods, in this case months, of the loan. Start with Period 0.
Payment – Gives the amount of your monthly mortgage payment. You can use the built-in function =PMT(rate,nper,pv) given the parameters:
rate – This is the interest rate per month (use 5.10%/12).
nper – This is the total number of periods of the mortgage (use 360).
pv - This is the present value or initial amount of the mortgage (use $250,000).
*The payment amount is automatically given as a negative value. In order to change this to a positive number, simply put a negative sign in front of the PMT.
Interest Paid – Gives the amount of the monthly payment that goes toward interest on the loan. This can be calculated by multiplying the previous unpaid balance by the interest rate per period.
Equity Paid - Gives the amount of the monthly payment that goes toward paying off the loan itself. This can be calculated by taking the monthly payment and subtracting the interest paid.
Unpaid Balance – Gives the amount of the mortgage that is still unpaid. Only the equity paid goes toward the unpaid balance. It will decrease over the life of the loan until it becomes $0 when the loan is paid off.
1. Fill out the spreadsheet for 30 years when the Unpaid Balance of the loan should be $0. The first two rows have the appropriate formulas done for you. You will need to autofill these formulas down to complete all rows of the spreadsheet.
a. Calculate the total amount of the monthly payments.
b. Calculate the total amount of interest paid.
c. Calculate the total amount of equity paid.
2. Repeat this process for the 15-year loan. Assume the same mortgage amount and same interest rate.
3. Compare the results from the 30-year and 15-year loans by answering the following questions on the cover page of your spreadsheet.
a. What is the difference in the monthly payment?
b. What is the difference in the total of all the monthly payments?
c. What is the difference in interest paid?
d. What are the advantages and disadvantages of getting a 30-year mortgage compared with a 15-year mortgage?
To create the spreadsheet in Excel, follow these steps:
1. Open Excel and create a new worksheet.
2. Label the columns as follows: Period, Payment, Interest Paid, Equity Paid, Unpaid Balance.
3. In the first row of the Payment column, enter the formula: =-PMT(5.10%/12, 360, 250000).
4. In the first row of the Interest Paid column, enter the formula: =Previous Unpaid Balance * (5.10%/12).
5. In the first row of the Equity Paid column, enter the formula: =Payment - Interest Paid.
6. In the first row of the Unpaid Balance column, enter the formula: =Previous Unpaid Balance - Equity Paid.
7. Autofill the formulas down for the remaining rows of the spreadsheet to calculate the values for each period.
8. Calculate the total amount of monthly payments by summing the Payment column.
9. Calculate the total amount of interest paid by summing the Interest Paid column.
10. Calculate the total amount of equity paid by summing the Equity Paid column.
To repeat the process for a 15-year loan, follow steps 3-7 using the same mortgage amount and interest rate, but change the number of periods to 180 (15*12).
To compare the results from the 30-year and 15-year loans, answer the following questions on the cover page of your spreadsheet:
a. The difference in the monthly payment can be calculated by subtracting the monthly payment for the 15-year loan from the monthly payment for the 30-year loan.
b. The difference in the total of all the monthly payments can be calculated by subtracting the total amount of monthly payments for the 15-year loan from the total amount of monthly payments for the 30-year loan.
c. The difference in interest paid can be calculated by subtracting the total amount of interest paid for the 15-year loan from the total amount of interest paid for the 30-year loan.
d. The advantages of a 30-year mortgage compared to a 15-year mortgage are lower monthly payments, which can be more manageable for some borrowers, and the ability to allocate funds to other investments or expenses. The disadvantages are paying more interest over the life of the loan and building equity at a slower pace. The advantages of a 15-year mortgage are paying off the loan faster, saving on interest payments, and building equity more quickly. The disadvantages are higher monthly payments, which may be harder to afford for some borrowers, and less flexibility in budgeting.