use-revolution Digest, Vol 18, Issue 8

[Gregory Lypny]

OK, Hershel.  Here's the example.  You can tweak it to suit your 
purposes.

In Canada, ING Direct offers a three-year fixed term, twenty-five year 
mortgage for a posted rate of 4.45%.  They don't offer a thirty-year 
amortization; that's why I chose twenty-five.  For a $300,000 loan, you 
would be paying $1,652.09 per month.  My answer is correct to the 
penny.

Here's how to set up the problem.  Each variable is indicated in 
parentheses.

Posted annual rate (r1): .0445
Payment frequency (m): 12  (i.e., monthly)
Effective monthly rate (r2): 0.00367441421  (you'll figure it)
Amortization (N): 25 years
Principal (P): $300,000
Payment (A): $1,652.09  (you'll figure it)
Annuity factor (F): 181.58815514 (you'll figure it)

Step 1
You need to convert the posted rate (r1) to the effective monthly rate 
(r2) because the posted rate is always a year but the effective rate 
corresponds to the frequency of your payments (monthly, semi-monthly, 
bi-weekly, weekly, etc.).  Notice that r2 is not simply r1 divided by 
12 (or m).  That's because of compounding.  So you have to find r2 by 
answering this question:  what rate, r2, which when compounded for 12 
periods gives us r1?  The added twist in Canada is that, by convention, 
compounded is taken to occur semi-annually, so .0445 actually slightly 
understates the true posted rate.  I'm not sure if that's done in the 
US.  Confused yet?  Look at the numbers.  (The carets, ^, mean take to 
the power of...).

	(1 + r2)^12 = (1 + r1/2)^2

	Rearrange to solve for r2 in general to get

	r2 = (1 + r1/2)^(2/m) - 1,

	where (2/m) means take the mth root and square it.

	Plug in the numbers to get

	(1 + .0445/2)^(1/6) - 1 = 0.00367441421

	So, that's your effective monthly rate.  Change m to 4 and it becomes 
an effective quarterly rate; 52 and it's a weekly rate, and so on.  You 
may want to write a function for it.

Step 2
	Now you can use the effective rate in the standard annuity formula to 
get the annuity factor, F, that you'd use to divide any principal 
amount in order to compute the payment, A.

	F = (1 - (1 + r2)^(-N*m))/r2

	Plug in the numbers.  Note N*m = 25 years x 12 months = 300 (easy) 
payments (ouch).

	F = (1 - (1 + 0.00367441421)^(-300))/0.00367441421

	F = 181.58815514

	Create the function, F, which is itself a function of r2, N, and m.

Step 3
	To get the monthly payment, just divide the principal by F.

	A = P/F    (another function you can create)

	Plug in the numbers.

	A = $300,000/181.58815514 = $1,652.09

	Or you can do create tables of A for a mortgage book like the banks 
used to use by computing A per $1,000 borrowed as a function of 
different rates, amortization periods, and frequency of payment.  In 
this example,

	A = $5.5069671214 per $1,000 borrowed.

	Of course, you can collapse Steps 1 and 2 into 3 to create a function 
that computes the payment directly.

	Regards,

		Greg

	
	"Better for us if you don't understand."
		-The Tragically Hip


On Mar 2, 2005, at 4:26 PM, use-revolution-request at lists.runrev.com 
wrote:

> On 3/2/05 10:57 AM, "Gregory Lypny" <gregory.lypny at videotron.ca> wrote:
> Thanks, First I need to be able to do a simple mortgage calculation 
> e.g.
> 300,000.00 loan for a period of 30 years. After I have this then I 
> could go
> on and do my cap rates, cash on cash and property value and so on.
> Hershel Fisch
>
>> Hershel,
>>
>> Can you post an example of your calculation?  I can give you a direct
>> calculation that doesn't require the annuity function so that you'll
>> have more control of day adjustments and the like.  Mine is for
>> Canadian mortgages but you will be able to modify it easily for
>> American mortgages.
>>
>> Greg
>>
>>
>> On Mar 2, 2005, at 10:22 AM, use-revolution-request at lists.runrev.com
>> wrote:
>>
>>> Hi all I'm trying to write a mortgage function and I can't get right.
>>> Some how the annuity function doesn't do it the way I understand it
>>> should.
>>> Any help is appreciated.
>>> Hershel