Mortgage Application Userform

[flamesrule34]

So basically I have a excel sheet (that I've attached), I recorded a macro to webquery the values into the spreadsheet.

I need help on the second part of the application now.

I need to create a userform that will allow a user to choose one of the twelve houses listed in the spreadsheet, and then to enter how many year mortgage they wish to take (e.g. 1 to 50 year mortgage) and how much of a down payment they wish to make.

Then the application needs to calculate the monthly mortgage payment on the full house price including legal fees, property taxes (monthly) and monthly mortgage insurance costs (all in the spreadsheet plus GST (5%) and less any down payment (user input).

Anybody have any idea how to do this via a userform? Any help would be appreciated!

Thanks.

Also posted here:

ozgrid.com/forum/showthread.php?t=147651&p=529009#post529009

[austenr]

Well at least you confessed to cross posting. A warning though, you may not get the help you need simply because of that. People are reluctant to give help working on something knowing that someone on another board is most likely coming up with a solution as well.

As for your question, probably plenty of samples of this on the web.

[flamesrule34]

Confessed? I apologize...I thought it was okay so long as I posted that I have put it up elsewhere.

At any rate I did a search but was unable to find anything of use.

Of course I'll keep looking, however if someone could help too that'd be great thanks.

[flamesrule34]

If it helps I'll attach another file, that basically shows in spreadsheet form what I'm trying to do, but I need to put that into a userform to automatically show/calculate the mortgage details for someone.

The amortization table isn't quite necessary just really shows the work, but at any rate help would be great for the userform of this.

[Tinbendr]

It will take quite a bit of work to do this for you. Why not give us a headstart by inserting a userform on your first example and drop in the items you want instead of us guessing?

Use Labels for text the user won't change.
Use listbox for the list of houses (by address?)
User Textboxes for user input.

David

[jaylotheman]

Okay done.

So basically as goes I've reattached my Canadian Mortgage excel file with a userform in the VBA section.

It has a "select housing list number" listbox for users to select one of the twelve houses from the spreadsheet.

It has a "enter length of mortgage" textbox for users to enter in their own terms (e.g. a 25 for a 25 year mortgage).

It has a "enter interest rate (as a decimal)" textbox to enter in their own rate (e.g. 0.05 for a 5% annual interest rate).

It has a "listing price" label that should show a listing price that corresponds to the listing price from the house they select from the spreadsheet.

It has a "property taxes (monthly)" label that should show a property tax that corresponds to the property tax from the house they select from the spreadsheet (e.g. A 1.4% property tax on a $425,000 house for a 25 year mortgage should be 0.014*425000=5950, then take that number and divide it by the number of years the user entered for their terms (25) and then again divide by 12 to make it monthly. So 5950/25=238, 238/12= $19.84 is what should show up in the label.

It has a "legal fees (monthly)" label that should show the legal fees that corresponds to the legal fees from the house they select from the spreadsheet (e.g. A 0.90% legal fee on a $425,000 house for a 25 year mortgage should be 0.0090*425000=3825, then take that number and divide it by the number of years the user entered for their terms (25) and then again divide by 12 to make it monthly. So 3825/25=153, 153/12= $12.75 is what should show up in the label.

It has a "mortgage insurance (monthly)" label that should show a property tax that corresponds to the property tax from the house they select from the spreadsheet (e.g. A 10% property tax on a $425,000 house for a 25 year mortgage should be 0.10*425000=42500, then take that number and divide it by the number of years the user entered for their terms (25) and then again divide by 12 to make it monthly. So 42500/25=1700, 1700/12= $141.67 is what should show up in the label.

A "number of payments" label should simply be the user's inputted mortgage term x 12 months (e.g. 25 years mortgage should calculate 25*12= 300 payments should show in the label).

A "monthly mortgage payment" label should calculate this via finance calculations.

A "principle owing" label should calculate this via finance calculations.

A "interest owing" label should calculate this via finance calculations.

A "total amount due" label should simply calculate the the total payments paid (e.g. If monthly payments were determined to be $250 for 25 years, then the label should show 250*25 years= 6250, then take this number and multiply it by 12 months, 6250*12= $75,000 is what should show up in the label).

Lemme know if you have any questions, it should make sense, I know the calculations just not how to code this in VBA with the userform.

Thanks.

[austenr]

Thats at least a start. However, the hard part is yet to come. You need to fill your list box, be able to select a property and do the calculations.

It's certainly doable but its a lot of work for free. How much VBA skills do you have if any? Even if someone does help you, can you maintain it?

[flamesrule34]

I agree there is some work to be done, I have done the other parts myself thus far, and I have the framework done as far as what I want to do now with the userform.

I just don't have the skills to write the code for the actual buttons that correspond what I want them to do.

As far as maintaining once this is up and running there will be very minimal maintaining involved and this is all that will be necessary.

However if no one is able to help me and this as far as I can get then I guess so be it, and thank-you anyway to those have taken a look at it.