Re: Problems with calculating the proper compounding

On Feb 2, 11:32 pm, Jim <j...@xxxxxxxxxxxx> wrote:
On Feb 1, 5:59 pm, The World Wide Wade <aderamey.a...@xxxxxxxxxxx>
wrote:





In article
<c8fb3fa8-3680-4de4-ab3c-4e7b991d6...@xxxxxxxxxxxxxxxxxxxxxxxxxxxx>,

 Jim <j...@xxxxxxxxxxxx> wrote:
Hello,

I'm creating a spread*** that automatically determines the payment
amount for a mortgage.

The problem that I'm running into is that when the compounding for the
interest rate is different from that of the payment frequency, I'm not
calculating the equivalent rate correctly.

For example:

J12 = 5.75%
Monthly payments
Amortization = 40
n = 40 * 12 = 480

                                       PV
PMT  =    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                          1 - (1 + (J1/12) ) ^ (-n))
                         ~~~~~~~~~~~~~~~~~~
                                      (J1/12)

^ = exponent

I do that, and then I input J1 instead of J12.

It calculates it very closely, but I know that I'm doing something
wrong.

Can someone please tell me?

Looks like the right formula. Maybe some roundoff error somewhere. Try
its mathematical equivalent,

            pmt = PV*(1+r)^n*r/((1+r)^n - 1),

where r is the monthly interest rate (= 5.75%/12 in your example).
That's what I set up in Excel a long time ago and it seems to give me
exact answers.

That is not working.

I guess really what I want is to know how to find an equivalent rate
if the compounding is different from the payment frequency??  For
example, semi-annual compounding and monthly payments.

I know that the first step is to give a monthly rate that is
equivalent to semi-annual payments, but I don't know how to do it.- Hide quoted text -

- Show quoted text -

The problem should only arise when the quoted compounding frequency is
different than the payment frequency...

OK... Here are the steps, as I see them...

First, what interest rate is "quoted"? Say it's 4.5% compounded
quarterly. You need to convert this into an <effective> annual rate.

In this case
EAR = (1 + 0.045/4)^4 - 1 = 0.045786086...

This is the annual rate, compounded annually, that will yield the same
result as the <quoted> rate and compounding period.

Next, you need to find the effective rate for the payment frequency.
Say the mortgage has monthly payments.

Effective monthly rate is

Monthly interest rate = (1 + 0.045786086)^(1/12) - 1 = 0.00373602473

Again, this rate compounded monthly will yield the same result as the
two other (rate & compounding frequency) combinations. The important
point is that <this> compounding period matches the mortgage payment
period, so it can be used in the annuity formula above.

As for the "Canadian difference". It's purely a consumer protection
thing. Mortgage lenders can confuse the rate issue by quoting nominal
interest rates with short compounding periods. For example, it's not
immediately obvious whether a mortgage quoted at 8% compounded weekly
is better than one at 8.25% compounded annually. So Canadian
mortgages must be quoted with AT MOST semi-annual compounding. Almost
all lenders use this max, allowing easy comparison of interest
rates...

The actual payment frequency is unregulated, as far as I know...
.

Quantcast