Loan Ammortization Forumlas?

[Don I]

I'm trying to build a worksheet that will allow me to analyze properties
when considering one for a purchase (RE Investing).

Given the number of years, original principle amount and APR:
* I need one formula that will give me the monthly payment (P & I).
* I need one formula that will give me the interest portion of the first
payment.

However when I enter the info using the financial functions provided in
Excel (Pmt, IPmt and I think a couple others), they all return either $150
too low or a few $1000 too high. Can someone help me with this?

Assume the Original Principal is in B6, the APR is in D6 (as a whole
number--i.e. 7.00% is displayed as 7.00), the number of years is in G6.

Thx.

 

[Ron Rosenfeld]

I'm trying to build a worksheet that will allow me to analyze properties
when considering one for a purchase (RE Investing).

Given the number of years, original principle amount and APR:
* I need one formula that will give me the monthly payment (P & I).
* I need one formula that will give me the interest portion of the first
payment.

However when I enter the info using the financial functions provided in
Excel (Pmt, IPmt and I think a couple others), they all return either $150
too low or a few $1000 too high. Can someone help me with this?

Assume the Original Principal is in B6, the APR is in D6 (as a whole
number--i.e. 7.00% is displayed as 7.00), the number of years is in G6.

Thx.

The PMT and IPMT functions expect the rate to be entered as the rate per
PERIOD. If you are making monthly payments, then you need to divide the APR in
D6 by 12 (and multiply the number of years in G6 by 12, also).

So your formula might look like:

=PMT(D6/12,G11*12,B6)

If B6 is positive, this will give a negative number as a result. This is
normal as cash going away from you is represented by negative numbers, and cash
coming towards you is represented by positive numbers. (You might or might not
want to change that by changing B6 in the formula to -B6).


--ron

 

[Don I]

Thanks. That's what I thought they expected--I must've had a typo earlier
because when I typed what I THOUGHT said the same thing as the forumla
below, it produced $322.77.

OK this got me close but the ammortization table I'm looking at tells me
that the monthly payment would be $471.83 and this formula returned $42.43.
And the interest formula returned nearly $100 less than the payment. The
interest portion of the first payment should be almost ALL of it.

I can deal with the negatives/positives.

B6 = 66500 (original principle)
D6=7.00 (APR)
G6=30 (YEARS)

Assumes 1 period is 1 month (divide APR by 12 months and also by 100 to
convert to decimal):
=PMT(D6/1200,G6*12,B6) produced $442.43

Given the same data as above, the following should give the interest portion
of the FIRST payment:
=IPMT(D6/1200,1,G6*12,B6) produced $-387.92.

 

[Ron Rosenfeld]

Thanks. That's what I thought they expected--I must've had a typo earlier
because when I typed what I THOUGHT said the same thing as the forumla
below, it produced $322.77.

OK this got me close but the ammortization table I'm looking at tells me
that the monthly payment would be $471.83 and this formula returned $42.43.
And the interest formula returned nearly $100 less than the payment. The
interest portion of the first payment should be almost ALL of it.

I can deal with the negatives/positives.

B6 = 66500 (original principle)
D6=7.00 (APR)
G6=30 (YEARS)

Assumes 1 period is 1 month (divide APR by 12 months and also by 100 to
convert to decimal):
=PMT(D6/1200,G6*12,B6) produced $442.43

Given the same data as above, the following should give the interest portion
of the FIRST payment:
=IPMT(D6/1200,1,G6*12,B6) produced $-387.92.

Are you from other than the US?

It may be that what you think is a 7% APR isn't calculated according to US
rules? Or maybe it's being calculated in some strange method.

By the way, the first months interest payment can also be calculated by the
formula: Rate (for the period) * Purchase or, in your case:

=7%/12*66500 --> $387.92.

Using the monthly payment you state from the amortization table, the equivalent
monthly interest rate would be 0.6375% or 7.65% annually.


--ron

 

[Don I]

<<Are you from other than the US?>>
No. Near Phila, PA.

Ok I just found that I was looking at 7.5% in the table. This time, I made
sure to look at 7% and got REAL close.

The ammortization table I'm looking at says that on a 30 year loan at 7%, my
monthly pmt would be 6.65 for every $1,000. That multiplied by 66.5 (loan
of 66,500) is 442.23. Excel produced 442.43 (20 cent diff).

 

[Ron Rosenfeld]

<<Are you from other than the US?>>
No. Near Phila, PA.

Ok I just found that I was looking at 7.5% in the table. This time, I made
sure to look at 7% and got REAL close.

The ammortization table I'm looking at says that on a 30 year loan at 7%, my
monthly pmt would be 6.65 for every $1,000. That multiplied by 66.5 (loan
of 66,500) is 442.23. Excel produced 442.43 (20 cent diff).

Hmmm, is there any kind of balloon payment due with or after the 360th payment?

If I use 442.23 for a payment, and set up a simple amortization table, where
each period's interest is computed by 7%/12 * remaining principal, I get a
balance of $239.31 due after the 360th payment. If there's no balance due at
the end, they must be figuring things a bit differently (or they made an
error).


--ron

 

[Ken Russell]

My 30 year old Sharp financial calculator says 442.4261594, so I'd say Excel
is spot on!

--
Ken Russell

(e-mail address removed)
Remove yourhat to reply by e-mail
..

 

[Ken Russell]

I just realised that your table is rounding off the payments per $1,000. If
you divide the correct answer of $442.43 by 66.5 you get 6.65308 per $1,000
which rounds to 6.65

--
Ken Russell

(e-mail address removed)
Remove yourhat to reply by e-mail
..

 

[Don I]

AHHHH

Mr. Russell finds it! Thanks.

Ok then I'm not going nuts (at least not this time). I was typing in what I
thought it was asking me for--but it was giving me a different answer and
NOW I know why!.