Determine interest and principal portions of monthly payment

Assignment Help Financial Accounting

Reference no: EM133560692

Case - Interest and Loan Amortization

For this case assume: You have located a home that you wish to purchase and wish to evaluate bank financing options in order to determine your budget. You've been working with a few banks on potential mortgage terms and wish to determine for yourself the payment schedule, monthly payment, and most importantly, just how much interest you will pay over the life of the mortgage. You also want to run a few scenarios to determine which is the best option for you.

To complete this exercise, you will use multiple aspects of Excel. These include:

1. Setting up and formatting a loan payment (amortization) schedule

2. Using the "absolute cell reference" and "autofill" features in Excel

3. Using the formula function PMT to calculate monthly payment based on a given interest rate and length of load

4. Copy a worksheet and adjust given values to answer various "what-if" scenarios.

The home you wish to purchase is listed at $379,900 and you expect that the seller will accept $370,000. You have saved $20,000 as a down payment and are evaluating mortgages for $350,000 from a couple of banks. Here are the mortgage terms:

1. 30-year, fixed mortgage, 3.15%

2. 15-year fixed mortgage, 2.85%

Case requirements: Using Excel: 1. Create a loan amortization schedule for both loan options for the life of the loan. Determine the monthly payment, and the $ amount of interest and principal paid monthly. Use the format as illustrated in the text, table 8-2 and below as your guide in setting up the schedule.

2. Note the following:

a. In the above image, to calculate the monthly payment, you can use the PMT formula.

b. Also, to determine interest and principal portions of the monthly payment, you can use the Excel functions IPMT and PPMT formulas.

c. You can also solve for payment using i. Business calculator ii. Algebraic equation d. To easily complete the amortization tables, use the following Excel functions: i. Absolute cell reference ii. Autofill Once the amortization schedules are completed, answer the following questions: 1. What is the total amount of $ interest paid for each mortgage for the entire 15 and 30 year period? 2. Evaluate the pros and cons of the 15-year versus 30-year mortgage 3. You've learned that making one extra payment per year can result in less interest paid as well as paying off the mortgage sooner. One can do this by making half the monthly payment every 2 weeks, which results in essentially one extra monthly payment per year. a. Using the 15-year mortgage amortization schedule, create a new amortization schedule using a bi-weekly payment (26 payments per year) i. Hint: use the worksheet copy function ii. Then adjust the interest rate and number of payments cells for a bi-weekly payment b. What is the $ savings in interest and how many months of payments do you save as compared to the 15-year monthly amortization? Provide a detailed summary of the loan analysis, including the pros and cons of the two mortgages. Include what you learned from the exercise, both from a finance and budget perspective.

Reference no: EM133560692

Questions Cloud

Provide an overview of the jurisdiction : Provide an overview of the jurisdiction, the problems and programs they highlight in Michigan budget documents and any other information

Discuss the historical development of our democracy : Discuss the historical development of our democracy and the many European influences that impacted the development of the U.S. Constitution.

What are depreciation expense and accumulated depreciation : What are Depreciation Expense and Accumulated Depreciation? (2) Depreciation Expense Formula pimms bowvin

What steps will you take in your policy, procedure : What steps will you take in your policy, procedure, and HR rules review with the jewelry company? What will that involve? As you consider their HR policies

Determine interest and principal portions of monthly payment : How much interest you will pay over the life of the mortgage. You also want to run a few scenarios to determine which is the best option for you

Describe how progressively developing covey habit 1 : Describe how progressively developing Covey's Habit 1, Be Proactive, is absolutely necessary if we wish to strategically lead and manage first ourselves

Prepare the relevant accounts in the books : Prepare the relevant accounts in the books of Phumlani (consignment account, goods sent on consignment, and consignee account)

What does truth mean when she says aint i a woman : Why did the other women at the meeting ask Gage not to allow Sojourner Truth to speak? What does Truth mean when she says, Ain't I a woman?

Prepare the income statement for the year ended 31 october 2 : Prepare the income statement for the year ended 31 October 2019 - Debenture interest has not been paid for the year. The items listed below should be

User Account

All Pages