XCEL 2 calculate week or bi-weekly accel. mortgage payment

[commuter]

I need a spreadsheet to help calculate what the interest cost and remaining
balance of a mortgage would be after a certain period 9similar to an
amortizationtable). Available templates do not offer the option to enter my
own payment and the frequency of the payment i.e. monthly, bi-weekly or
weekly.

I need to be able to enter my own payment amount and the frequency 12,24,26,52

 

[joeu2004]

I need a spreadsheet to help calculate what the interest
cost and remaining balance of a mortgage would be after
a certain period (similar to an amortization table). Available
templates do not offer the option to enter my own payment
and the frequency of the payment i.e. monthly, bi-weekly or
weekly. I need to be able to enter my own payment amount
and the frequency 12,24,26,52

There are several different problems to solve:

1. Computing the required payment and estimating the
number of payments.

2. Generating the appropriate column of payment dates.

3. Generating the rest of the amortization table.

You only ask about #2 and #3.

Once you have #2 (see below), #3 is quite easy. It is the
same in all cases. If A:A is the date, B:B is the payment,
C:C is the prinicipal paid in the period, D is the interest
paid in the period, E:E is the remaining balance, then set
A1 and E1 to the date and amount of the initial loan, enter
the following formulas and copy down:

C2: =B2-D2
D2: =D1*(1+rate/365)^(A2-A1)
E2: =D1+D2-B2

where "rate" is the nominal annual interest rate; "rate" might
be a cell reference. This assumes that loans compound daily.
Alternatively, ignoring daily compounding, you might replace
D2 with simply D1*(1+rate/freq), where "freq" is 12, 24, 26 or
52 (or a cell reference).

#2 can be more difficult, namely: generating the dates in
A:A in a general manner. I would suggest entering the first
two dates into A1 and A2, then entering the following formula
into A3 and copying down:

=if (or(freq=12, freq=24), date(year(A1), 1+month(A1), day(A1)),
A1+14)

where "freq" is the number 12, 24, 26 or 52; "freq" might be
a cell reference. If "freq" is 12 or 24, we want payments on
the same day one month apart every other payment. If "freq"
is 26 or 52, we want payments 14 days apart every other
payment. By entering the first two dates, this computes
monthly and weekly payment schedules, even though the logic
seems to only compute bimonthly and biweekly schedules.

#1 can be the most difficult, if only because lender policy
usually differs from mathematical reality. That is, the way
that lenders compute the payment does not conform to the
mathematical-correct formula to reduce the loan balance to
zero in the prescribed number of payments, taking daily
compounding into account. I believe the following conforms
to what most US lenders do:

=pmt(rate/freq, n, -loan)

where "n" is the number of payments (nominally term*freq,
where "term" is in years) and "loan" is the initial loan amount.

A more mathematically-correct formula would be (for the US):

=pmt(fv(rate/365, 365/freq,, -1) - 1, n, -loan)

Note that even that formula is not mathematically correct
because no payment period has exactly 365/freq days.

NPER() can be used to estimate the number of payments,
given the payment amount and the periodic rate computed
by one of the methods above, namely fv(rate/freq,...)-1 or
rate/freq.

 

[joeu2004]

I believe the following conforms to what most US lenders do:
=pmt(rate/freq, n, -loan)

I just learned that the OP is asking about Canadian mortgages.
(I had a premonition that that might be the case.) Based on a
comparison with one online Canadian mortgage calculator, I
believe the following would be the Canadian formula:

=pmt(rate(freq/2,, -1, 1 + r/2), n, -loan)

where "r" is the annual rate; note the name change to avoid
confusion with the RATE() function.

But note that this is for a "regular" frequency (weekly, biweekly
etc). I do not know what the "accel" frequency is and how
it differs, other than the fact that it results in slight higher
payment and shorter term. Google searches have not yielded
any insightful information.

(In hindsight, I see that "accel" was mentioned in the subject
line, but not in the body of the posting.)

Can anyone shed some light on "accel" v. "regular" (my term)
weekly/biweekly payment schedules for Canadian mortgages?

 

[Fred Smith]

Canadian mortgages are compounded "every six months, not in advance". So a
mortgage quoted as 6% is actually 3% compounded semiannually.

If payments are anything other than semi-annual (which they almost always are),
you need to convert the quoted rate to a periodic rate, as in:

=rate(annualperiods/2,0,-1,1+annualrate/2)

 

[joeu2004]

Canadian mortgages are compounded "every six months, not in advance".
So a mortgage quoted as 6% is actually 3% compounded semiannually.
If payments are anything other than semi-annual (which they almost
always are), you need to convert the quoted rate to a periodic rate,
as in:
=rate(annualperiods/2,0,-1,1+annualrate/2)

Obviously I know that since that is what I wrote in my updated posting,
which you quote. But that does nothing to improve my understanding
of the difference between "accel" v. "regular" weekly or biweekly
payment
schedules. No matter: I believe I discovered the answer myself.

Based on one online Canadian calculator, the rate() computation above
(and in my earlier posting) is useful for computing pmt() for "regular"
periodic payments. Thus, "regular" weekly/biweekly payments are
computed using normal amortization, assuming the payments go on
for the full loan term.

With "accel" weekly/biweekly schedules, the payment amount seems
to be based on the total monthly payments for 13 months (i.e. a year
plus one month), based the original loan term (e.g. 25 yr). That total
is divided by the number of "accel" payments (26 or 52). The "accel"
schedules are marketed as "allow[ing] you to make the equivalent of
13 months of payments over a 12-month period" -- similar to the way
semimonthly payments (at least) are marketed in the US. The effect
is to greatly reduce the total interest paid over the life of the loan,
as
well as to shorten the actual length of the loan.

For example, for a $100,000 Canadian loan at 6% over 25 yr nominally
the monthly payment is $639.81 for 300 payments and less than
$91,942 total interest, the "regular" biweekly payment is $294.90 for
650 payments and less than $91,687 interest, the "accel" biweekly
payment is $319.90 for 547 payments (21.04 yr) and less than $74,987
interest, and the "accel" weekly payment is $159.95 for 1093 (21.02 yr)
and less than $74,827 interest, which closely correspond to the results
of the online Canadian mortgage calculator.

Thus, the Excel formulas for Canadian "accel" schedules are:

=13*pmt(rate(12/2,,-1,1+r/2), 12*term, -loan) / freq

=roundup(nper(rate(freq/2,,-1,1+r/2), p, -loan), 0)

where "r" is the annual rate, "p" is the payment (the result of the
pmt()
formula), and "term" is the nominal length (in years) of the loan based
on monthly payments. The nper() formula yields "n", i.e. the number
of payments, which I used in other formulas in my previous posting.