On Jun 25, 11:27 pm, Formula Fred
wrote:
In answer to your rebuke

It was not intended to be a "rebuke" so much as a constructive
suggestion. My point is: by burying this inquiry in an unrelated
thread, you might get less participation in the discussion and,
therefore, less of an answer.

I am new to this site and could not find a way of posting a new
discussion, so I tagged it onto an old one

That is really a poor excuse. If you don't know how to do it
correctly, don't do it incorrectly. Get some help.

This should be the correct formula for calculating mortgage interestj

Not at all! It is a formula for computing the effect of compounding
interest in a savings account. A=P*(1+r)^n, as you wrote it, results
in a future value (A) that is larger than the principal. But of
course, for a mortgage calculation, the future value is declining --
one would hope ;-).

The discrete math formula can be found on the PV() help page. But it
is really quite ugly and complicated to use. That's why God -- ah, MS
(really Visicalc) -- created functions.

You are right - it does not compare exactly
with my mortgage statement. The trouble is that I do not know what further
information I could supply beyond the fact that interest is calculated daily
and added monthly

Well, you start with the terms of the loan agreement: loan amount,
term of the loan, payment frequency, interest rate, payment amount
(not including PMI or any other periodic payments that are not paying
down the principal and interest). You might also mention if you are
talking about a Canadian mortgage (sigh).

Moreover, it is not clear what you mean when you say that you want
"really accurate compound interest calculations". Do you want the
interest charged in a billing period? Or do you want to compute
cumulative interest over several billing periods -- for example, the
interest charge in a year?

This must be a universal problem that many people have encountered before
and there should be some standard formulae somewhere, shouldn't there?

Part of the problem with trying to answer such an abstract question
is: the devil is in the details. There are straight-forward
answers. But often, people find they do not exactly match their
mortgage statements because reality creeps in.

For example, for 15-year loan of $100,000 at 6% with monthly payments,
the payment is ostensibly PMT(6%/12, 15*12, -100000). But that is
wrong because a lender must round at least to the smallest coin of the
realm -- for example, cents in the US. And often, lenders will round
it further -- for example, to dollars in the US. That radically
affects how interest is subsequently computed, how many payments are
made, and the amount of the last payment.

Suppose the payment is $844 -- that is, ROUNDUP(PMT(...), 0). Then,
the actual number of payments is ROUNDUP(NPER(6%/12,844,-100000),0).
In this case, that happens to be 180, the same as 15*12. But
sometimes, it is 1 or 2 periods less. In any case, the last payment
is not $844. You can estimate the last payment with ROUNDUP(FV(6%/
12,180-1,844,-100000)*(1+6%/12),0).

Note: Whether to use ROUND() or ROUNDUP() and whether to round to 0
or 2 decimal places are really up to the lender. In the US, the only
legal requirement is that the lender disclose all of this information
when the loan contract is finalized.

But all of that is approximate because you said that interest is
computed daily, not monthly. Consequently, the exact numbers will
depend on the date when the first period begins. It might also depend
on when the payment is actually made; but let's assume that payments
are made on their due dates.

The only way I know of to compute interest "really accurately" when it
is accrued daily is to create an amortization schedule. Suppose
payments are due on the 15-th of each month, and the first period
begins on 15 Feb 2006. If A1 contains 2/15/2006, A2 contains
3/15/2006 and E1 contains the initial balance (100000), then the
interest for the first payment is (in C2):

=E1*(A2-A1)*6%/365

and the new balance is (in E2):

=E1+C2-B2

where B2 is the payment (844). Likewise, if A3 contains 4/15/2006,
the interest for the second payment is (in C3):

=E2*(A3-A2)*6%/365

Clearly, (A2-A1)*6%/365 is not necessarily the same as (A3-A2)*6%/365
for any two months. Therein lies the problem in trying to compute
interest "really accurately" when interest is computed daily: the
interest rate is not constant for every month.

Hope this intro helps. It is necessarily very brief.