use-revolution Digest, Vol 18, Issue 8

Gregory Lypny gregory.lypny at videotron.ca
Wed Mar 2 21:05:11 EST 2005

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