Excel Case 1 (Mortgage Amortization)

Given the following information:

Interest Rate (yearly): 3.5%

Mortgage: $250,000

Term (in months): 360

Start Date: October 1, 2019

Yearly Bonus: $10,000

Formulas to incorporate:

PMT

EDATE

SUM

Other Notes:

Do amortization monthly.

For each scenario, graph the interest paid and principal paid for each month on a line chart together for all months that the loan is active. Create a second line chart that graphs the cumulative interest paid and cumulative principal paid by month. Make sure to include the dates on the x-axis and title your charts.

Format using headers, currency with two decimals and any other formatting to make your spreadsheet look professional.

Note that the interest rate is yearly and that your amortization table is monthly.

Anchoring cells will make it faster to develop your spreadsheet.

Youll want to set up your spreadsheet to have the following columns: Starting Balance, Interest Accrued, Payment, Principal, Ending Balance, Cumulative Interest, Cumulative Principal.

Case:

What is the minimum payment each month?

Scenarios (complete each scenario on a separate tab and label each tab appropriately):

1. Pay the minimum required.
2. Pay an extra $250/month. When will you have the loan paid off by?
3. Pay an extra $250/month and make a larger payment (equal to 50% of your year end bonus) each December. When will you have the loan paid off by?