+ Reply to Thread
Results 1 to 5 of 5

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

  1. #1
    commuter
    Guest

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

    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

  2. #2

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

    "commuter" wrote:
    > 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: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.


  3. #3

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

    I wrote:
    > 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?


  4. #4
    Fred Smith
    Guest

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

    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)

    --
    Regards,
    Fred


    "[email protected]" <[email protected]> wrote in
    message news:[email protected]...
    >I wrote:
    >> 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?
    >




  5. #5

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

    Fred Smith wrote:
    > 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.


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1