In microsoft.public.money, Money User wrote:

- quote -

Check Mortgage Calculations, which includes the Canadian formula:
http://www.hughchou.org/calc/formula.html#can

You hadn't mentioned the Canadian Mortgage angle, but I also don't think you
can get to a payment--south-of the-border or north-of-the-border style
compounding--using your formula--it doesn't appear to take compounding into
effect.

A little googling identified:

http://www.hughchou.org/calc/formula.html#can and its link to a .ZIP
(http://www.hughchou.org/calc/CanadaMortgage.zip) that purports to have some
Excel code (Excel 5.0? .XLM???) for a Canadian Mortgage PMT().

Note also this Excel formula:

=PMT((1+NomRate/Frequency)^(Frequency/NumberOfPmts)-1,
NumberOfPmts*Term, -Principal)

from http://support.microsoft.com/?kbid=291106.

"Money User" <msmoney[at]payweb.ca> wrote in message
news:ueA3YOlCFHA.3908[at]TK2MSFTNGP12.phx.gbl...
- quote -

Thanks for all your input, but this isn't quite what I'm looking for,
here's the situation:

I make bi-weekly payments, and my mortgage is amortized over 25 years

However when I run the calculations in Excel I get a different payment
amount, interest, and principal amount than my mortgage company.

I put my mortgage info into Money using the wizard - New account no
financial inst. Mortgage, fixed rate loan, no payments made, paid every
other week, Interest calculated twice /year (Canadian Mortgage) - eh!, loan
amount $110k, interest rate 5.54, I let Money calculate the # of payments,
set my payment $337, and no balloon amount.

I then enter the payments that I've made and Money match's my annual
mortgage statement from my mortgage company exactly.

I've looked at the formula's attached and they all come up with the same
interest rates that I have calculated also - but they don't match Money or
the mortgage company.

Example:
I=Prt
I=110,000*(.0554/26)*552 <-- Money calculated time
I=110,000*0.002131*1
I=$234.38

So then that would mean that my principal was $102.62

But Money and the mortgage co. say it's $230.81 int. and $106.19 princ.

The only thing I can figure is that I'm using the wrong calculation formula.

Does anyone know what the right one is?


"Money User" <msmoney[at]payweb.ca> wrote in message
news:%23dO0geVCFHA.2676[at]TK2MSFTNGP12.phx.gbl...
- quote -

Thanks for all your input, but this isn't quite what I'm looking for,
here's the situation:

I make bi-weekly payments, and my mortgage is amortized over 25 years

However when I run the calculations in Excel I get a different payment
amount, interest, and principal amount than my mortgage company.

I put my mortgage info into Money using the wizard - New account no
financial inst. Mortgage, fixed rate loan, no payments made, paid every
other week, Interest calculated twice /year (Canadian Mortgage) - eh!, loan
amount $110k, interest rate 5.54, I let Money calculate the # of payments,
set my payment $337, and no balloon amount.

I then enter the payments that I've made and Money match's my annual
mortgage statement from my mortgage company exactly.

I've looked at the formula's attached and they all come up with the same
interest rates that I have calculated also - but they don't match Money or
the mortgage company.

Example:
I=Prt
I=110,000*(.0554/26)*552 <-- Money calculated time
I=110,000*0.002131*1
I=$234.38

So then that would mean that my principal was $102.62

But Money and the mortgage co. say it's $230.81 int. and $106.19 princ.

The only thing I can figure is that I'm using the wrong calculation formula.

Does anyone know what the right one is?


"Money User" <msmoney[at]payweb.ca> wrote in message
news:%23dO0geVCFHA.2676[at]TK2MSFTNGP12.phx.gbl...
- quote -

Probably something pretty close to:

interest accrued = periodic interest rate * outstanding balance

principal applied = payment - interest accrued.

When I do my monthly paid mortgage calculations in Excel, for periodic
interest rate I use 1/12 * annual rate. For the weekly paid car, I use
7/365.26 * annual rate.

"Money User" <msmoney[at]payweb.ca> wrote in message
news:%23dO0geVCFHA.2676[at]TK2MSFTNGP12.phx.gbl...
- quote -

In microsoft.public.money, Money User wrote:

- quote -

It is normally best when trying to enter a loan from a bank to let
Money compute the interest, and leave the interest blank. The quoted
bank interest uses some strange things, often including a 360 day
year in their calculations.

- quote -

Are you asking a math question as to how to solve for interest rate?
http://www.arches.uga.edu/~jkau/re9830/pv14.html has some analysis
discussions.



- quote -

http://www.hughchou.org/calc/ss.html uses Excel formulas, but I
think you are looking for the underlying math.
http://www.google.com/search?as_q=+i...du&safe=images
may have some more links.

Have I understood what you are asking?

Can anyone tell/show me how Money calculates the mortgage principal and
interest amounts?
I've been pulling (what's left) my hair out the last couple of days trying
to figure this out.

I'm sure there is some kind of standard howto but I am unable to find
anything in my many searches.

Specifically I'm interested in the calculation based on a payment, rather
than just a normal amortization which works the other way round.