BMAT 230 OSC –Loan Amortization Project

The Loan Amortization Project is an individual project done in Excel, worth 10% of your overall BMAT 230 mark. Do your own work. You will earn marks by following specific instructions.

The Amortization Project is due by 11:59 P.M. on Sunday, July 9, 2023.

A submission on July 10, 2023, will be marked at a maximum of 50% of the total.

• A later submission will not be marked.
• Plan to submit early.

Create a brand-new Excel document and save it as: “BMAT 230 Amortization - your first and last name".

Name two separate Excel worksheets within the same document: sheet 1 as “your first name’s Personal Loan" (ex: Paul’s Personal Loan), and sheet 2 as “your first name’s House Mortgage".

Shade the Personal Loan tab blue , and the House Mortgage tab orange .

On each worksheet, insert a header showing your first & last name and insert a footer showing your SAIT student ID.

Solve the Personal Loan on sheet 1, and the House Mortgage on sheet 2.

Submit the Amortization Project only once by uploading it into the Amortization Project folder, under Assignments in Brightspace (D2L).

• Make sure you have the final version before you submit.

Include a short appropriate message to me.

For each solution:

• Type an appropriate title including your name (not as a header).
• Type given values in separate cells to be cell referenced in further calculations. Never type a given value twice. Never type a number (except perhaps 1, when calculating a rate) within a calculation.
  ex: For the personal loan, to calculate the balance after 1 year, type 1 & year in 2 separate cells; cell reference the value of 1 in the calculation.
• Organize and label all given values using our in-class symbols, with subscripts where applicable, (j, mp, n, mi , i, PMT, etc.) and active calculated values. Include section headings for various components of the project, but do not type the questions. Do not use #1, #2, etc.
• I suggest that you use the methods introduced in class to perform various calculations. This will reinforce your learning.
• Pay attention to details such as appropriate units, subscripts, correctly rounded or unrounded values as introduced in class (showing i2 with a minimum of 9 decimal places, unrounded), and showing c as a fraction.
• Please show all key n and PV values. Do not embed too many calculations within one calculation.
• Produce a very active worksheet using cell references and absolute cell references appropriately.

Do not type substitutions or formulas.

• Explore and use the ROUND, ROUNDUP and ROUNDDOWN features appropriately. ROUND all calculated final answers appropriately (before typing statements).
• Use Excel functions OR create algebraic formulas, depending on the circumstance.
• Perform Excel calculations that are as ACTIVE AS POSSIBLE!
• Type a complete full-scenario sentence active statement answering each question, following this format: ex: = “The size of the payments is $“& (cell reference value) &”." If your calculated answer changes, the statement is automatically updated (and rounded correctly). You can type the statements wherever you deem appropriate, within the amortization project.
• Personalize your project. Make it presentable and professional. Use colour at your discretion.

Formulas and substitutions should not be shown. Think of including appropriate $, fixing any spelling mistakes, avoiding unnecessary “=" signs, merging cells where applicable, as well as adjusting row heights and column widths as needed.

• Develop a common theme for all two solutions.
• Avoid clutter.

PERSONAL LOAN

You just obtained a personal loan for $130, 000.00 at 6.785% compounded monthly. For the fun of it, you are choosing to make regular quarterly payments, PMT(END), the same size as the first four digits, (ignoring the leading zeros) of your own SAIT student ID (ex: if ID is 000123456, PMT = $1234). Make sure the payment is correct!

• How many payments will be required to pay off your personal loan?
• What will be the term of your personal loan, expressed in years and months?

Your calculation should show a number of years and months (even if it’s 0 month) (Hint: use the ROUNDing features; cell reference 12 months/year).

Perform the following calculations without an amortization schedule. Do not calculate any value twice. If you calculate a value, cell reference it in a subsequent step whenever useful.

• What will be the loan balance after 2 years and 9 months?
• What will be the principal reduction of the loan during the 3rd year?
• What will be the total amount of interest paid during the 3rd year?
• What will be the size of the final loan payment?

Prepare a complete amortization schedule for your loan. ROUND all monetary values to the nearest cent. Express only the totals as currency. Perform checks to compare the answer of each of the above questions with your results from the amortization schedule. Choose a colour scheme such that the first answer is highlighted the same colour as the matching answer in the schedule (choose colours that are not too dark).

Use a different colour for each new answer and its check.

Please note:

Some checks can be shown by highlighting cells directly on the amortization schedule; other checks will require a simple calculation using some amortization schedule values. Do any simple calculation beside the amortization schedule. Label clearly! For example, you might want to add a thicker border around a group of cells to indicate a check corresponding to a sum from a series of values. Refer to the example below.

Note: Checked values might not match the schedule perfectly but should be very close!

HOUSE MORTGAGE

Suppose you purchase a house for $450, 000.00 and make a down payment of 20% of the purchase price. The balance is amortized over 20 years. The house mortgage agreement is subject to 4.135% compounded semi-annually for the first five years and requires equal monthly payments.

• What is the size of the equal monthly payments for the first five years?
• What is the house mortgage balance at the end of the five years?

Perform this calculation using Excel, without an amortization schedule. After five years, the house mortgage is refinanced at a reduced nominal rate of 3.792% compounded semi-annually and the house mortgage is amortized over the remainder of the term.

• What is the size of the new mortgage payments for the remainder of the term?
• What is the size of the final loan payment? Perform this calculation in Excel, without an amortization schedule.

Prepare one complete amortization schedule showing the mortgage amortized over the full 20-year period (reflecting the change in payment size). Cell reference values appropriately. ROUND all monetary values to the nearest cent. Express only the totals as currency.

Highlight the calculated payment for the first five years and the first payment on the amortization schedule using one colour.

Highlight the new payment and the first new payment on the amortization schedule with a second colour.

Perform checks of your calculated values compared to the amortization schedule values.

• Highlight the calculated principal balance of the loan after five years and the balance shown in the amortization schedule using a third colour.
• Highlight the calculated final loan payment and the amortization schedule final payment using a fourth colour.

Use the amortization schedule value directly to answer this question:

• What would be the total cost of financing your house mortgage assuming that the rate did change after five years, as detailed above?
• Highlight the statement and the amortization schedule value using a fifth colour.

Are you struggling to keep up with the demands of your academic journey? Don't worry, we've got your back! Exam Question Bank is your trusted partner in achieving academic excellence for all kind of technical and non-technical subjects.

Our comprehensive range of academic services is designed to cater to students at every level. Whether you're a high school student, a college undergraduate, or pursuing advanced studies, we have the expertise and resources to support you.

To connect with expert and ask your query click here Exam Question Bank