Mortgage Information
The following required
information is the main input for Excel Mortgage Calculator to make the
pertinent calculations and get the mortgage values:
- The Loan Amount or total amount of money being borrowed (not including any previous down payment).
- The Annual Interest Rate specifically applied to the mortgage.
- The Loan Term, which is the number of years to pay off the whole sum (generally 20 or 30, or even longer, depending on numerous factors).
- Number of Payments per Year (generally 12).
On top of those
values, an additional extra payment can be computed into the Excel Mortgage
Calculator in order to account for deductions to the loan term and, subsequently,
to the balance and cumulative interest. All those values can be either directly
input into the corresponding cells of the worksheet “Calculator”, or through an
Excel VBA UserForm that pops up when clicking “Change Values”.
The UserForm is
activated with the ‘Show’ method (see below). ‘MortgageValues’ is the name of
the UserForm (otherwise, ‘UserForm1’ is the default name when adding a new one).
Once active, the ‘Initialize’ procedure adds the current values to the form as
indicated below.
Sub OpenChangeValues()
MortgageValues.Show
End Sub
Sub UserForm_Initialize()
With MortgageValues
.LoanAmount.Text =
Format(Sheets("Calculator").Range("C4"), "€
#,##")
.InterestRate.Text =
Sheets("Calculator").Range("C5")
.LoanTerm.Text =
Sheets("Calculator").Range("C6")
.PaymentsPerYear.Text =
Sheets("Calculator").Range("C7")
.ExtraPayments.Text =
Format(Sheets("Calculator").Range("C12"), "€
#,##")
End
With
End Sub
The values can
be then updated in the form and, when saving, they will be copied to the
corresponding cells (using ‘Range’) of the worksheet “Calculator” to
automatically make calculations and update the results.
Sub UpdateValues_Click()
Sheets("Calculator").Select
With
MortgageValues
Range("C4") = .LoanAmount.Text
Range("C5") = .InterestRate.Text
Range("C6") = .LoanTerm.Text
Range("C7") =
.PaymentsPerYear.Text
Range("C12") = .ExtraPayments.Text
End
With
Unload
Me
UpdateChart
End Sub
Mortgage Payments
Excel Mortgage
Calculator makes use of some Excel formulas to calculate the scheduled payment
amount for the given loan amount, annual interest rate, payments per year, and
total number of payments. The formula used to do so in Excel is PMT, which
gives a negative value as that amount deducts from the balance. In order to get
the positive value shown in C10 we use the following expression:
=-PMT(InterestRate/PaymentsPerYear,NumberPayments,LoanAmount)
As explained
earlier, Excel Mortgage Calculator allows to account for an optional extra
monthly payment. That amount is added to the mortgage payment, and the total
payment minus interest equals the principal, which is the amount deducted from
the loan amount every period.
Note that the
names for each variable have been defined and can be found in the Excel Name Manager.
Thus, names can be used in formulas as substitutes for cells references.
Amortization schedule
Excel Mortgage
Calculator presents monthly payment information in the amortization table along
with balance, principal and interest movements. The table updates automatically
when changing the mortgage values, and allows to calculate the total interest
and the actual number of periods when extra payments are added.
The amortization
chart shows the balance, and cumulative principal and interest for each year.
That data is looked up in the amortization table with the VBA WorksheetFunction.VLookup
method, and copied into cells behind the chart of the Excel Mortgage
Calculator. The chart is updated every time the input values change as per the
following worksheet event procedure:
Private Sub Worksheet_Change(ByVal Target As
Range)
If MortgageValues.Visible = False Then
If Not Intersect(Target,
Target.Worksheet.Range("C4:C7")) Is Nothing _
Or Target.Address = "$C$12" Then
Call UpdateChart
End If
End
If
End Sub
Click Here to download
the Excel Mortgage Calculator.
No comments:
Post a Comment