High School

We appreciate your visit to CASE 120 Business and Property Spreadsheet Project The spreadsheet calculations should be set up in a systematic manner Your set up should contain a list. This page offers clear insights and highlights the essential aspects of the topic. Our goal is to provide a helpful and engaging learning experience. Explore the content and find the answers you need!

**CASE 120 Business and Property Spreadsheet Project**

The spreadsheet calculations should be set up in a systematic manner. Your set-up should contain a list of the given values, and as many calculated values as possible. Make your spreadsheet as 'active' as possible by using cell references (so that if one value is changed, subsequent calculations will automatically update). Use absolute cell references in special situations.

Bob and Angelique Mackenzie bought a property valued at $84,000 for $15,000 down with the balance amortized over 20 years. The terms of the mortgage require equal payments at the end of each month. Interest on the mortgage is 3.4% compounded semi-annually and the mortgage is renewable after five years.

a. What is the size of each monthly payment?

b. Prepare an amortization schedule for the first five-year term. Make sure your values are rounded to the nearest cent. Express totals at the bottom of each column as currency.

c. What is the cost of the debt during the first five-year term?

d. If the mortgage is renewed for a further five years at 4.2% compounded semi-annually, what will be the size of each monthly payment?

The Mackenzies also bought a business for $10,000. They borrowed the money to buy the business at 6.9% compounded semi-annually and are to repay the debt by making quarterly payments of $3,645.

e. How many payments are required to repay the loan?

f. What is the term of the loan in years and months?

g. Prepare a complete amortization schedule for the loan. Make sure your values are rounded to the nearest cent. Express totals at the bottom of each column as currency.

h. What is the principal reduction in the 6th year?

i. What is the total cost of financing the debt?

j. If Angelique makes a lump sum payment of $10,000 at the end of the fourth year, by how much is the amortization period shortened?

Answer :

a. To calculate the size of each monthly payment for the property mortgage, we can use the formula for the monthly payment of an amortized loan. In this case, the loan amount is the balance after the down payment, which is $84,000 - $15,000 = $69,000. The interest rate is 3.4% per year, compounded semi-annually. The loan term is 20 years, which is equivalent to 20 * 12 = 240 months. Using these values, we can calculate the monthly payment.



b. To prepare an amortization schedule for the first five-year term, we need to calculate the monthly payment and then determine the principal and interest portions of each payment. We can use the loan balance, interest rate, and loan term to calculate the monthly payment. Then, for each month, we can calculate the interest based on the loan balance and interest rate, and subtract it from the monthly payment to determine the principal portion. The loan balance for each month is the previous month's loan balance minus the principal portion.

c. The cost of the debt during the first five-year term can be calculated by summing up the interest portions of each monthly payment from the amortization schedule.

d. If the mortgage is renewed for a further five years at 4.2% compounded semi-annually, we can use the same formula to calculate the new monthly payment. The loan balance will be the remaining balance from the previous term.

e. To calculate the number of payments required to repay the loan for the business, we can divide the total loan amount by the quarterly payment amount.

f. The term of the loan in years and months can be calculated by dividing the number of payments by the number of payments in a year (4) and then dividing the result by 12.

g. To prepare a complete amortization schedule for the loan, we can use the same approach as in part b, but with quarterly payments instead of monthly payments.

h. The principal reduction in the 6th year can be found by looking at the amortization schedule and finding the principal portion for that year.

i. The total cost of financing the debt can be calculated by summing up the interest portions of each quarterly payment from the amortization schedule.

j. To calculate how much the amortization period is shortened by a lump sum payment of $10,000 at the end of the fourth year, we can compare the remaining loan balance before and after the payment and calculate the difference in the number of payments based on the new loan balance.

To learn more about the amortization schedule:

https://brainly.com/question/14511778

#SPJ11

Thanks for taking the time to read CASE 120 Business and Property Spreadsheet Project The spreadsheet calculations should be set up in a systematic manner Your set up should contain a list. We hope the insights shared have been valuable and enhanced your understanding of the topic. Don�t hesitate to browse our website for more informative and engaging content!

Rewritten by : Barada