pretty much the title sums it up. i need to create a mortgage calculator using those 2 things for my IE311 class. the only help i need is with the coding since the coding is pretty much what makes this an extra credit assignment for the class. i've googled my ass off and have found very little to help me with the actual coding. can anyone provide some assistance?

10/5/2005 4:06:14 PM

i can make on in excel. probably vb as well. i will give it a shot and see what happens. pm me at 6 pm.

10/5/2005 4:09:18 PM

is using VBA actually part of the assignment? Because it can be done completely using regular Excel functions. VBA would really only add unneeded complexity to a relatively simple calculation.

i can't open these templates on my Mac, but you could look in the VBA editor here to see if they use any
http://office.microsoft.com/en-us/templates/TC010197771033.aspx
although the code might be locked.

10/5/2005 4:19:08 PM

unfortunately yea i have to use VBA cause that's part of the assignment. i could easily do it with just excel but it has to be done using both. it has to be a user-friendly program type thing where u enter in the loan amount, interest rate, time period, and # of payments which all has out to be outputted to a mortgage value. then from there a graph has to be done. not sure what the graph is for though.

10/5/2005 4:23:00 PM

the graph should probably show the monthly payments broken down into principle and interest - a line graph with 2 lines, the principle starting low and curving up, and the interest starting high and curving low.

10/5/2005 4:48:11 PM

this is a ridiculous assignment, but, i am up to the challenge. about the vb part, i will have to research that - i am assuming that the vb code is for the graph.

10/5/2005 4:55:30 PM

they probably want you to use vb for all the calculations instead of the typical, easy to use, excel functions. IE teachers are gay like that (and you'll need to know how to do that shit before you take IE401). Do you have berhnard or that other guy that sucks just as bad??

10/5/2005 5:00:40 PM

if you did it in javascript, would that count?

10/5/2005 5:13:18 PM

does this help?
http://lacher.com/toc/tutvba1.htm

10/5/2005 5:15:36 PM

this is the assignment here: http://www.filefarmer.com/brianj320/Challenge.pdf

my understanding is that only excel and VBA are to be used, nothing else. after that, i dont know anythin cause he hasnt explained it to us at all. we're meant to do this on our own and figure it out ourselves since its an extra credit project that replaces an entire test. it's ayoub btw.

10/5/2005 6:29:09 PM

hahaha, Ayoub is an ass. That shouldn't be too difficult if you know what you're doing in VBA...otherwise unless you have a need to learn VBA for another reason, you probably don't want to do it...unless your grade depends on it.

ps. it will probably take 25 hours to do if you don't know what you're doing, this is a rough estimate and probably a bit low.

[Edited on October 5, 2005 at 6:34 PM. Reason : . ]

10/5/2005 6:33:51 PM

see i dont know what i'm doin in VBA so that's y i thought i'd post on here and see if anyone can help me out. i mean i'd appreciate anythin at this point. i still have time till its due but i'd like to get it done asap.

10/5/2005 6:36:14 PM

yeah, except my point is that basically if you don't know what you are doing in VBA this project is going to take a lot of time. if you know how to code that helps but this "project" isn't a good starter on VBA, its just going to piss you off.

10/5/2005 6:46:54 PM

1. open Excel
2. make sure the task pane is visible (View > Task Pane)
3. Under "New from Template" choose "General Templates"
4. click on the "Spreadsheet Solutions" tab, select "Loan Amortization", and click "OK".

you're done.

10/5/2005 7:13:05 PM

way to not read the thread

10/5/2005 7:14:32 PM

^^ did u not read anythin at all?

10/5/2005 7:16:47 PM

bttt for any help

10/6/2005 3:19:13 PM

bttt

10/9/2005 2:39:04 PM

any help? please

10/10/2005 1:54:36 PM

buy me dinner and ill tell you everything you need to know

(been doing VBA for 3 years now)

[Edited on October 10, 2005 at 1:58 PM. Reason : ]

10/10/2005 1:57:59 PM

well there would be some sort of compensation for this since its gonna require a bit of work. but what does dinner entail? like what kind of restaurant/food?

10/10/2005 1:59:43 PM

like cheapass food. the project would probably take me less than an hour to do it myself. i doesnt sound hard at all. or you could give me 10 bucks and ill buy my own damn food.

10/10/2005 2:04:19 PM

Just take on from this guy: http://www.mtgprofessor.com/spreadsheets.htm

Its actually a really great site for people buying or refinancing.

10/10/2005 2:06:17 PM

i appreciate the link but look, the program IS REQUIRED to be done using VBA. this is the reason it is extra credit. anyone could do it in a spreadsheet form easily, i coulda done that in about 30 minutes or less. NO SPREADSHEETS! MUST BE DONE USING VBA!

^^ i could do $10

[Edited on October 10, 2005 at 2:45 PM. Reason : .]

10/10/2005 2:45:40 PM

i'll PM you my address. you can come and i'll show you everything you need to know. (i'm assuming you have at least some programming experience)

10/10/2005 5:07:47 PM

PHP 30yr I modified. You can adjust it for VB accordingly

	/* --------------------------------------------------- *
	 * Set Form DEFAULT values
	 * --------------------------------------------------- */
	$default_sale_price              = "150000";
	$default_annual_interest_percent = 6.5;
	$default_year_term               = 30;
	$default_down_percent            = 10;
	$default_show_progress           = TRUE;
	/* --------------------------------------------------- */



	/* --------------------------------------------------- *
	 * Initialize Variables
	 * --------------------------------------------------- */
	$sale_price                      = 0;
	$annual_interest_percent         = 0;
	$year_term                       = 0;
	$down_percent                    = 0;
	$this_year_interest_paid         = 0;
	$this_year_principal_paid        = 0;
	$form_complete                   = false;
	$show_progress                   = false;
	$monthly_payment                 = false;
	$show_progress                   = false;
	$error                           = false;
	/* --------------------------------------------------- */


	/* --------------------------------------------------- *
	 * Set the USER INPUT values
	 * --------------------------------------------------- */
	if (isset($_REQUEST['form_complete'])) {
		$sale_price                      = $_REQUEST['sale_price'];
		$annual_interest_percent         = $_REQUEST['annual_interest_percent'];
		$year_term                       = $_REQUEST['year_term'];
		$down_percent                    = $_REQUEST['down_percent'];
		$show_progress                   = (isset($_REQUEST['show_progress'])) ? $_REQUEST['show_progress'] : false;
		$form_complete                   = $_REQUEST['form_complete'];
	}
	/* --------------------------------------------------- */


	// Style Sheet
	?>

		/* --------------------------------------------------- */
	// This function does the actual mortgage calculations
	// by plotting a PVIFA (Present Value Interest Factor of Annuity)
	// table...
	function get_interest_factor($year_term, $monthly_interest_rate) {
		global $base_rate;

		$factor      = 0;
		$base_rate   = 1 + $monthly_interest_rate;
		$denominator = $base_rate;
		for ($i=0; $i < ($year_term * 12); $i++) {
			$factor += (1 / $denominator);
			$denominator *= $base_rate;
		}
		return $factor;
	}
	/* --------------------------------------------------- */

	// If the form is complete, we'll start the math
	if ($form_complete) {
		// We'll set all the numeric values to JUST
		// numbers - this will delete any dollars signs,
		// commas, spaces, and letters, without invalidating
		// the value of the number
		$sale_price              = ereg_replace( "[^0-9.]", "", $sale_price);
		$annual_interest_percent = 5.75;
		$year_term               = 30;
		$down_percent            = eregi_replace("[^0-9.]", "", $down_percent);

		if (((float) $year_term <= 0) || ((float) $sale_price <= 0) || ((float) $annual_interest_percent <= 0)) {
			$error = "You must enter a Sale Price of Home, Length of Motgage and Annual Interest Rate";
		}

		if (!$error) {
			$month_term              = $year_term * 12;
			$down_payment            = $sale_price * ($down_percent / 100);
			$annual_interest_rate    = $annual_interest_percent / 100;
			$monthly_interest_rate   = $annual_interest_rate / 12;
			$financing_price         = $sale_price - $down_payment;
			$monthly_factor          = get_interest_factor($year_term, $monthly_interest_rate);
			$monthly_payment         = $financing_price / $monthly_factor;
			$io_monthly_payment      = $financing_price * (.035/12);
			$cf_monthly_payment	 = ($financing_price * 1.0195) / 360;
		}
	} else {
		if (!$sale_price)              { $sale_price              = $default_sale_price;              }
		if (!$annual_interest_percent) { $annual_interest_percent = $default_annual_interest_percent; }
		if (!$year_term)               { $year_term               = $default_year_term;               }
		if (!$down_percent)            { $down_percent            = $default_down_percent;            }
		if (!$show_progress)           { $show_progress           = $default_show_progress;           }
	}

	if ($error) {
		print("" . $error . "

\n");
		$form_complete   = false;
	}

$step = 1;
		// Set some base variables
		$principal	 = $financing_price;
		$current_month = 1;
		$current_year  = 1;
		// This basically, re-figures out the monthly payment, again.
		$power = -($month_term);
		$denom = pow((1 + $monthly_interest_rate), $power);
		$monthly_payment = $principal * ($monthly_interest_rate / (1 - $denom));

		print("

Amortization For Monthly Payment: \$" . number_format($monthly_payment, "2", ".", "thousands_sep") . " over " . $year_term . " years
\n");
		print("\n");

		// This LEGEND will get reprinted every 12 months
		$legend  = "\t\n";
		$legend .= "\t\t\n";
		$legend .= "\t\t\n";
		$legend .= "\t\t\n";
		$legend .= "\t\t\n";
		$legend .= "\t\n";

		echo $legend;

		// Loop through and get the current month's payments for
		// the length of the loan
		while ($current_month <= $month_term) {
			$interest_paid	 = $principal * $monthly_interest_rate;
			$principal_paid	= $monthly_payment - $interest_paid;
			$remaining_balance = $principal - $principal_paid;

			$this_year_interest_paid  = $this_year_interest_paid + $interest_paid;
			$this_year_principal_paid = $this_year_principal_paid + $principal_paid;

			print("\t\n");
			print("\t\t\n");
			print("\t\t\n");
			print("\t\t\n");
			print("\t\t\n");
			print("\t\n");

			($current_month % 12) ? $show_legend = FALSE : $show_legend = TRUE;

			if ($show_legend) {
				print("\t\n");
				print("\t\t\n");
				print("\t\n");

				$total_spent_this_year = $this_year_interest_paid + $this_year_principal_paid;
				print("\t\n");
				print("\t\t\n");
				print("\t\t\n");
				print("\t\n");

				print("\t\n");
				print("\t\t\n");
				print("\t\n");

				$current_year++;
				$this_year_interest_paid  = 0;
				$this_year_principal_paid = 0;

				if (($current_month + 6) < $month_term) {
					echo $legend;
				}
			}

			$principal = $remaining_balance;
			$current_month++;
		}
		print("
Month
Interest Paid
Principal Paid
Remaing Balance
" . $current_month . "
\$" . number_format($interest_paid, "2", ".", "thousands_sep") . "
\$" . number_format($principal_paid, "2", ".", "thousands_sep") . "
\$" . number_format($remaining_balance, "2", ".", "thousands_sep") . "
Totals for year " . $current_year . "
 
\n");
				print("\t\t\tYou will spend \$" . number_format($total_spent_this_year, "2", ".", "thousands_sep") . " on your house in year " . $current_year . "
\n");
				print("\t\t\t\$" . number_format($this_year_interest_paid, "2", ".", "thousands_sep") . " will go towards INTEREST
\n");
				print("\t\t\t\$" . number_format($this_year_principal_paid, "2", ".", "thousands_sep") . " will go towards PRINCIPAL
\n");
				print("\t\t
 

\n");

10/10/2005 8:02:38 PM