Calculating Affordable Mortgages with VBScript

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

Aug 18, 2009

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:

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

Running the VBScript Function for Calculating an Affordable Mortgage

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?".

The copyright of the article Calculating Affordable Mortgages with VBScript in Windows Programming is owned by Mark Alexander Bain. Permission to republish Calculating Affordable Mortgages with VBScript in print or online must be granted by the author in writing.