Calculating Affordable Mortgages with VBScript

Use a Windows PC to Work Out a Compound Interest Loan on a Budget

Aug 18, 2009
Mark Alexander Bain

Calculating Affordable Mortgages with VBScript - Mark Alexander Bain

A VBScript programmer can quickly produce an application that will calculate the mortgage for any budget, interest rate and repayment period

For anyone thinking about a mortgage their first consideration is how just how big a mortgage they can afford on their budget.. The size of the mortgage will depend on:

the amount of money that they can afford every month
the interest rate
the number of years over which the loan will be repaid

There is a commonly used formula for calculating the monthly repayments for a mortgage (or any compound interest loan) and that's discussed in A Simple Home Mortgage Calculator. However, the formula is quite complicated and laborious to use, and so the automation of the process with VBScript on a Windows computer is discussed in A Simple VBScript Home Mortgage Calculator.

That answers the question of "How much are the monthly payments for a mortgage?", the next question is "How big a mortgage can be obtained for a set monthly budget?". So, for example, if a couple has a budget of $600 a month then what's the size of loan that they can afford?

A VBScript Function for Calculating Monthly Mortgage Repayments

The monthly payment formula, although complicated, can be encapsulated within a VBScript function:

Function mp (amount, interest, years)

Dim payments: payments = years * 12 'Total number of months

Dim percentage: percentage = interest / 100 / 12 'Montly interest rate

mp = amount * ( percentage * (1 + percentage) ^ payments ) / ( (1 + percentage) ^ payments - 1)

End Function

The VBScript programmer can turn this into a function for calculating affordable mortgage by doing a little recoding.

A VBScript Function for Calculating an Affordable Mortgage

This new function can be created by transposing the formula used in the monthly payment function:

Function mortgage (mp, interest, years)

Dim payments: payments = years * 12 'Total number of months

Dim percentage: percentage = interest / 100 / 12 'Montly interest rate

mortgage = mp / (( percentage * (1 + percentage) ^ payments ) / ( (1 + percentage) ^ payments - 1))

End Function

The function will return an affordable mortgage for the inputs:

monthly payment
interest rate
number of years

The next step is to incorporate this function into a script.

Running the VBScript Function for Calculating an Affordable Mortgage

The programmer can now create a script that will:

calculate a mortgage from a monthly budget, an interest rate and the number of years for which the mortgage is to run
format and display the result

For example:

Dim budget: budget = 600

Dim interest: interest = 5

Dim years: years = 25

Dim affordable: affordable = mortgage (budget, interest, years)

WScript.StdOut.Write "The mortgage for "

WScript.StdOut.Write "$" & budget & " per month"

WScript.StdOut.Write " over " & years & " years "

WScript.StdOut.Write "at " & interest & "% "

WScript.Echo "will be $" & round(affordable,2)

This will tell the user that "The mortgage for $600 per month over 25 years at 5% will be $102636.03" as can be seen in figure 1. And, in this way, the VBScript programmer can create an application that supplies an answer to one of the most important questions that anyone can ask "How big a mortgage can I get?".

Mark Alexander Bain - Mark Alexander Bain is a writer, Mo Bro and consultant for all aspects of software development at dsquared. He has also written regularly ...