Answered step by step
Verified Expert Solution
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 year mortgage of $ with an annual interest rate of
Period Keeps track of the number of periods, in this case months, of the loan. Start with Period
Payment Gives the amount of your monthly mortgage payment. You can use the builtin function PMTratenper,pv given the parameters:
rate This is the interest rate per month use
nper This is the total number of periods of the mortgage use
pv This is the present value or initial amount of the mortgage use $
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 $ when the loan is paid off.
Fill out the spreadsheet for years when the Unpaid Balance of the loan should be $ 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.
Repeat this process for the year loan. Assume the same mortgage amount and same interest rate.
Compare the results from the year and 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 year mortgage compared with a year mortgage?
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started