Mortgage Functions

[Legacy 98055]

MortgageCalculator.xls
	A	B	C	D	E	F	G	H	I	J	K
1
2		StartingDateofthisLoan:			09/1/2001		PresentLoanValue:			$94,124.26
3		DateofFirstPayment:			10/1/2001		PrinciplePaidThrough09/01/02:			$875.74
4		InterestRate:			7.5%		InterestPaidThrough09/01/02:			$7,095.30
5		AmortizationPeriod(Months):			360		TotalInterest:			$144,131.36
6		AmortizationPeriod(Years):			30		TotalScheduledPayments:			$239,131.36
7		OriginalLoanValue:			$95,000.00		EstimatedMonthlyPayment:			$660.13
8		LastPaymentDate:			09/1/2002
9
10		Ifyoudon'tmind,pleasechecktomakesuretheabovefunctionsarecorrect.
11
12		Hypotheticals:
13
14		Fromaspecificperiodtoaspecificperiod(30yearwith360months(periods))
15		1.IfIincreasemymonthlypayment
16		2.IfIpayalumpsumonanygivendate
17		3.Ifpaidbi-weeklyorevenweekly
18
19		Ifpossible,IwouldliketoreturntheamountofinterestdebtwhichIwouldsave.
20		Also,toshowtheamountoftimetheamortizationperiodwouldbereducedby.
21
Sheet1

Thanks,
Tom

 

[Ekim]

Tom,

The monthly repayment should be $664.25.

=PMT(.075/12, 30*12, -95000)

or

=ABS(PMT(0.075/12, 30*12, 95000)


I have emailed you with a Loan Calculator (Excel spreadsheet) that allows the option to over-ride the calculated monthly repayment with a payment amount of your choice. The spreadsheet includes an option to pay monthly, fortnightly and weekly (but not bi-weekly), and includes a simple Chart that graphs repayments in months. In reference to bi-weekly payments, you will have to amend the formula.

Surprisingly, despite the difference in the monthly loan amount, all your other calculations are fine (I have highlighted my model in green to shows your figures).

My model does not cater for lump sum payments. Michael Kelly, a mortgage specialist, has devised a number of spreadsheets (in English pounds) that may give you the answer (I don't have the time to go through all his models):

http://www.mortgagesexposed.com/Book_Contents/spreadsheet_summary.htm

Incidentally, I have no objection to you or Mr. Excel publishing the model as a free download (you may wish to spruce it up, particularly the Chart!). The model include one of Aladin's super formulas that gets the last value in a range.


Kind regards,


Mike

 

[Legacy 98055]

Thanks Mike.
I downloaded all of the books and am sifting through them.
Tom