ZuoTi.Pro
Question

PLEASE EXPLAIN HOW TO CREATE THE AMORTIZATION TABLE IN EXCEL! Mini-Project 1: Mortgage Loan Analysis (50...

PLEASE EXPLAIN HOW TO CREATE THE AMORTIZATION TABLE IN EXCEL!

Mini-Project 1: Mortgage Loan Analysis (50 points):

You plan to buy a house in December 2019. The sale price is $385,000. You need to pay 20% down payments and borrow additional 80% from Bank of America with a 25-year, 3.0% fixed-rate mortgage loan. You are expected to pay an equal MONTHLY payment starting from January 2020 for a total of 25 years.

(1) Calculate your expected monthly mortgage payment.

(2) Develop the 2020~2044 amortization table for your mortgage loan.

(A)   When you prepare your 2020 tax filing, what is the total mortgage interest payment that you can consider for the tax deduction?

business   finance   
0 0
Add a comment Improve this question Transcribed image text
Next > < Previous
Sort answers by oldest

Homework Answers

Answer #1
  • Price of the home is 385000
  • Down payment = 20%
  • Loan amount = 80%
    • Loan amount = 0.80 x 385000 = 308000

We are given the following information:

r 3.00%
n 25
frequency 12
PV $   3,08,000.00

We need to solve the following equation to arrive at the required PMT

So the monthly payment = 1460.57

Following is the amortization schedule:

Opening balance = previous year's closing balance
Closing balance = Opening balance+Loan-Principal repayment
PMT is calculated as per the above formula
Interest = 0.03 /12 x opening balance
Principal repayment = PMT - Interest

Total interest on the loan is  $1,30,171.25

0 0
Add a comment
Know the answer?
Add Answer to:
PLEASE EXPLAIN HOW TO CREATE THE AMORTIZATION TABLE IN EXCEL! Mini-Project 1: Mortgage Loan Analysis (50...
Your Answer:

Post as a guest

Your Name:

What's your source?

Earn Coins

Coins can be redeemed for fabulous gifts.

Log In

Sign Up

Not the answer you're looking for? Ask your own homework help question. Our experts will answer your question WITHIN MINUTES for Free.
Similar Homework Help Questions

  • show all work please and explanations 3. You need to work on the mini-projects in Excel....

    show all work please and explanations 3. You need to work on the mini-projects in Excel. Your final mini-project report should be well- organized and typed in a Word file. Are required to submit both your Word report and Excel files to the instructor using the mini-project submission tool in Blackboard. The style and organization of the project accounts for 10 points. 4. You are required to work on the work independently. The due date for the project is 11:59pm...

  • please show work. Thank you 3. You need to work on the mini-projects in Excel. Your...

    please show work. Thank you 3. You need to work on the mini-projects in Excel. Your final mini-project report should be well- organized and typed in a Word file. Are required to submit both your Word report and Excel files to the instructor using the mini-project submission tool in Blackboard. The style and organization of the project accounts for 10 points. 4. You are required to work on the work independently. The due date for the project is 11:59pm on...

  • You plan to buy a house in December 2019. The sale price is $385,000. You need...

    You plan to buy a house in December 2019. The sale price is $385,000. You need to pay 20% down payments and borrow an additional 80% from Bank of America with a 25-year, 3.0% fixed-rate mortgage loan. You are expected to pay an equal MONTHLY payment starting from January 2020 for a total of 25 years. (1) Calculate your expected monthly mortgage payment. (2) Develop the 2020~2044 amortization table for your mortgage loan. (1)   When you prepare your 2020 tax filing,...

  • Mortgage Amortization Complete the loan amortization schedule for a Mortgage that will be repaid over 360...

    Mortgage Amortization Complete the loan amortization schedule for a Mortgage that will be repaid over 360 months and answer the following questions (The details about the loan are shown below): Correct Answers 1. What is your monthly payment? 2. What is the total $ amount of payments made over the life of the loan Enter Answers Here. 3. How many months will it take to pay off the loan if you pay an extra $465.71 per month? Note: Enter the...

  • Part 1. Create a loan amortization table (50 points) You are considering purchasing a SUV with...

    Part 1. Create a loan amortization table (50 points) You are considering purchasing a SUV with a sticker price of $42,270 (nonnegotiable, with a 20% down payment required). You have cash to cover the down payment and you want to make monthly payments over five years to cover the remainder of the cost. The highest monthly payment that you can afford is $700. The credit union has agreed to loan you the money at a 4.23% annual interest rate la:...

  • 13. [Loan Amortization] You have just obtained a $300,000 mortgage loan from the Chase bank toward...

    13. [Loan Amortization] You have just obtained a $300,000 mortgage loan from the Chase bank toward the purchase of a home at 6% APR. The amortization schedule of your mortgage is set in the monthly payments for the next 30 years. A) What is the monthly loan payment?   B) What is the balance of the loan after 20 years of loan payments?     C) From the previous mortgage loan question, what will be the principal and the total interest that you...

  • 3. [Loan Amortization] You have just obtained a $300,000 mortgage loan from the Chase bank toward...

    3. [Loan Amortization] You have just obtained a $300,000 mortgage loan from the Chase bank toward the purchase of a home at 6% APR. The amortization schedule of your mortgage is set in the monthly payments for the next 30 years. A) What is the monthly loan payment?   B) What is the balance of the loan after 20 years of loan payments?         C) From the previous mortgage loan question, what will be the principal and the total interest that...

  • Loans Excel Homework Assignment Mortgage Amortization Schedule Excel Assignment 1. Excel: Complete the amortization table provided...

    Loans Excel Homework Assignment Mortgage Amortization Schedule Excel Assignment 1. Excel: Complete the amortization table provided in the Excel document posted in in the homework area by setting the appropriate values for a $165,000, 30-year mortgage at 4.5% interest and using Excel's autofill (drag) feature to fill in the cells to the end of the mortgage period. Use this to answer the following: a. How much of the first payment goes towards the principal? How much goes toward the interest?...

  • 7A) Use excel to build a monthly amortization table for a 30-year 5% fix-rate mortgage on...

    7A) Use excel to build a monthly amortization table for a 30-year 5% fix-rate mortgage on a $320,000 home, with a $15,000 down payment. [Please be good to the forests and cut and paste only the first and last few rows of the table, not the whole thing.] 7B) Using the amortization table you built for the previous question, answer the following [Again, just cut and paste the segment of the amortization table that helps you answer the question.]: a....

  • i need part B and the rest on excel Question 1 Prepare the amortization schedule for...

    i need part B and the rest on excel Question 1 Prepare the amortization schedule for a thirty-year loan of $100,000. The APR is 3% and the loan calls for equal monthly payments. The following table shows how you should prepare the amortization schedule for the loan. a. Interest Principal Ending Month lPayment Payment Payment Balance Beginning ota S100,000.00 b. Use the annuity formula to find how much principal you still owe to the bank at the end of the...

ADVERTISEMENT
Free Homework Help App
Download From Google Play
Scan Your Homework
to Get Instant Free Answers
Need Online Homework Help?
Ask a Question
Get Answers For Free
Most questions answered within 3 hours.
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT