In this exercise, you are to create a mortgage calculator that will look something like this screen shot. The mortgage calculator will be able to work out the monthly repayment of a mortgage, given the loan amount, the annual interest rate and the loan peoridin years.

First, create a table for the 3 given values, the calculated monthly repayment (based on these 3 values), an optional extra monthly payment amount and a total of all payments paid over the duration of the loan. The monthly repayment can be calculated using Excels PMT() function.

In addition, you need to set up a second table to show the progress of the loan on a month by month basis. In this table, you will need a column for the date, monthly payment, interest accrued, principal reduction and the current balance. The current balance is always equal to last month's balance minus the principal reduction. In the case of the first month, the current balance is equal to the total amount borrowed. The monthly payment is the standard monthly payment you worked out with the PMT() function plus any extra monthly payment. The interest accrued and principle reduction can be calculated with:

interest_accrued = current_balance * (annual_rate/12) principal_reduction = monthly_payment - interest_accrued 

Create enough rows in the second table for 30 years. If your calculation is correct, by the 360th month, the outstanding balance should be $0.

Right click on the sheet tab and select Insert.... Insert two chart sheets, one for comparing the interests against principal payment, one for the outstanding balance for every months. Use line graph for both.

Or you may insert the chart from selecting the chart option from the Insert tab on top of Excel

With a loan of $200,000 for 30 years at an annual rate of 5.8% started on January 2012, what will the current balance be in January 2020? (up to 2 decimal positions)

Interests vs. Principal Mortage Caculation $1.400.00 $1.200.00 TO 4202 12024 220 URT Mar 204