Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

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

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?

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Teaching Public Budgeting And Finance

Authors: Meagan M. Jordan, Bruce D. McDonald III

1st Edition

1032146680, 978-1032146683

More Books

Students also viewed these Finance questions