7.7 Technology: Amortization Tables and Spreadsheets

[Pages:15]7 . 7 Technology: Amortiza tion Ta bles a nd

Sprea dsheets

Generally, people must borrow money when they purchase a car, house, or condominium, so they arrange a loan or mortgage. Loans and mortgages are agreements between a money lender and a borrower to finance a purchase. T he agreement usually requires the borrower to repay the loan in equal payments at equal time intervals, with payments that include both principal and interest.

To amortize a mortgage means to repay the mortgage over a given period of time in equal payments at regular intervals. T he period of time is known as the amortization period. T he term of a mortgage is the length of time the mortgage agreement is in effect.

I N V ESTI G ATE & I N Q U I RE

An amortization table can be created to show how much of the principal remains after each payment, and how much of each payment is interest or principal.

Suppose you borrow $10 000 at an interest rate of 6% per annum, compounded monthly, and agree to pay back the loan with equal payments at one month intervals over one year. You need to know the monthly payment and the principal that has been repaid at any time during the year.

You can use a graphing calculator to find the monthly payments for the loan and a spreadsheet to keep track of the amounts paid and owed. Change the mode settings to 2 decimal places. From the Finance menu, choose TVM Solver.

Enter the known values.

T here are 12 payments a year, so N = 12. T he interest rate is 6% per annum, so I = 6.

544 MHR ? Chapter 7

T he loan is for $10 000, so PV = 10 000. A payment is made each month, so P/Y = 12. T he interest is compounded monthly, so C/Y = 12.

T he payments are made at the end of each payment interval, so select END.

Move the cursor to PMT to find the payment for the loan, and press ALPHA SOLVE. Since the payments for the loan are paid out, PMT is negative.

T he payment for the loan is $860.66.

Use a spreadsheet to follow the progress of repaying the loan with an amortization table. T he following spreadsheet shows data for the $10 000 loan at the top left, and formulas for calculations with the loan. T hen, formulas are copied down into other rows.

For details of how to create the spreadsheets used in this chapter, see the M icrosoft? Excel and Corel? Quattro? Pro sections of Appendix C.

1. a) Explain the value entered in each of these cells. A1 A2 A4 A8 b) Explain the formula entered in each of these cells. A3 A9 B8 B9 E8 F8 c) Make a generalization about what happens to the formulas in the cells listed in part b) when they are copied into other rows. d) Identify the cells with $ in the formulas. e) Make a generalization about what happens to the formulas with $ when they are copied into other rows.

2. T he following spreadsheet shows the results of the calculations from the formulas. T he results of the calculations are seen on a spreadsheet. You can see a formula for an individual cell by selecting the cell.

7.7 Technology: Amortization Tables and Spreadsheets ? MHR 545

a) For each of the following cells, explain how the formula in the first spreadsheet results in the value in the second spreadsheet. A3 A9 B8 B9 E8 F8 C8 C9 C10 D8 D9 D10 b) Make a generalization about how the values in cells where formulas are copied are related. c) Make a generalization about what happens to the values in cells where formulas with $ are copied.

3. a) Create the spreadsheet from question 1. Check your results with the spreadsheet from question 2. b) Predict for how many rows you should copy the formula for this loan. T hen, copy the formula down, and compare the results with your prediction. c) Format the cells to express amounts of money to 2 decimal places.

4. a) Choose various months in the spreadsheet, and state how much of the principal has been repaid for each month. Include the row for the 12th month. b) Explain whether the results in your spreadsheet are what you would expect.

A mortgage is a loan secured by real estate. Generally, a mortgage is arranged to finance the purchase of property. However, a mortgage can be set up for someone to borrow money for any reason, using property as security for the loan.

By Canadian law, the interest rate on mortgages is compounded semiannually. Although other lengths of time can be arranged, most mortgages are paid monthly. T his is contrary to the investments and loans in previous sections of this chapter, where the compounding period and the payment period are the same. Because of this difference, some calculations for mortgages are different.

546 MHR ? Chapter 7

Suppose you arrange a mortgage of $112 500 on a house, with an interest rate of 7.5% per annum, compounded semi-annually, and agree to make equal monthly payments. If you amortize the mortgage over 25 years, this means that the amount paid each month is the amount that would pay off the loan in 25 years if the mortgage continued. Generally, mortgages are for a term such as 3, 4, or 5 years. Planning a mortgage would require knowing the monthly payments, and you would probably want to know how much of the principal, the $112 500, had been repaid at various times during the mortgage.

Use a graphing calculator to find the monthly payments for the mortgage, and the monthly interest rate. T he monthly interest rate, or rate per month, is calculated differently for a mortgage than for a loan because the compounding period and the payment period of a mortgage are not the same. A spreadsheet can show amounts paid and owed.

Choose the mode settings to 2 decimal places. From the Finance menu, choose TVM Solver.

Enter the known values.

T here are 12 payments a year for 25 years, so N = 25 ? 12. T he interest rate is 7.5% per annum, so I = 7.5. T he mortgage is $112 500, so PV = 112 500. A payment is made each month, so P/Y = 12. T he interest is compounded semi-annually, so C/Y = 2.

T he payments are made at the end of each payment interval,

so select END.

For a mortgage, the value of P/ Y is 12, but the value of C/ Y is 2. W hen you store a value for P/ Y, C/ Y automatically changes to match P/ Y, so you must go back to C/ Y and enter 2.

Move the cursor to PMT to find the payment for the loan, and press ALPHA SOLVE. Since the payment is paid out, PMT is negative.

T he monthly payment for this mortgage is $823.00.

For finding the monthly interest rate, change the mode settings to 9 decimal places. From the Finance menu, choose TVM Solver. Enter values for $1 for 1 month of this mortgage.

Enter the known values.

T he interest rate per month is being calculated, so N = 1. T he interest rate is 7.5% per annum, so I = 7.5. Use the negative amount $1 to find the value per dollar paid out, so PV = -1.

7.7 Technology: Amortization Tables and Spreadsheets ? MHR 547

T he payments are monthly, so P/Y = 12. T he interest is compounded semi-annually, so C/Y = 2.

T he payments are made at the end of each payment interval, so select END.

Move the cursor to FV to find the future value of $1, and press ALPHA SOLVE.

T he future value of $1 is $1.006 154 524.

Since the future value is the amount with compound interest for the

present value, or investment of $1, A = P + i, where i represents the

monthly interest rate.

Amount equals principal plus interest.

A=P+i

Substitute known values:

1.006 154 524 = 1 + i

Isolate i :

i = 0.006 154 524

T he interest rate per month is 0.006 154 524, or 0.615 452 4%.

Use a spreadsheet to follow the progress of repaying the mortgage with an amortization table. T he following spreadsheet shows data for the $112 500 mortgage at the top left, and formulas for calculations for the mortgage. T hen, formulas are copied down into other rows.

5. a) Explain the value entered in each of these cells. A1 A2 A4 A8 b) For each of these cells, explain the formula and predict the value that it will calculate. A9 B8 B9 E8 F8 c) Make a generalization about what happens to the formulas in cells listed in part b) where they are copied into other rows. d) Make a prediction about what will happen for the values in cells where formulas listed in part b) are copied.

548 MHR ? Chapter 7

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download