Problem with Amortization Table using beginning of period

[jcooper]

I am trying to create a amortization table in which payments start at
the beginning of the period. The problem is that principal doesn't
zero out at the end of the term. In fact, it overpays by a small
amount. Yet, when I calculate the same table on a financial calculator
which shows an amortization table, it zeroes out.

I'm guessing, but the problem seems to be with the future value. When
I make the future value "0", it zeroes out. My calculation includes a
balloon payout at the end of the term.

Any thoughts?

Under the Interest column per period, my formula looks like this:

IPMT(Annual Interest Rate/12, current period, # periods, - current
value, + future value (for a balloon payment at the end), 1 (to
indicate beginning of the period).

Under the Principal column per period, my formula looks like this:

PPMT(Annual Interest Rate/12, current period, # periods, - current
value, + future value (for a balloon payment at the end), 1 (to
indicate beginning of the period).

Essentially, both are the same except the name of the function.

 

[Fred Smith]

I think your problem is rounding, not the payment starting at the beginning
of the period. I expect your financial calculator will be rounding your
payments to the penny, but Excel is not.

Here's some ideas which might simplify things for you or point you in the
right direction.

1. Rather than payments at the beginning of the period, you can simply
adjust the principal. For example, you borrow $10,000 with payments of
$250/mo starting immediately is the same as borrowing $9750 with payments
starting a month from now.

2. The calculations are much simpler than you are making them. You need five
columns:

StartAmount (Original loan amount if first line, previous EndAmount if not)
Payment (fixed monthly amount or including balloon payment)
Interest = StartAmount * rate / 12, which you likely will want to round to 2
decimal places
Principal = Payment - Interest
EndAmount = StartAmount - Payment + Interest

Copy down until EndAmount gets to zero.

3. Use PPMT and IPMT only if you *don't* want a full amortization table.

 

[jcooper]

Fred,

Thanks. You may be right regarding the rounding issue. It just
surprised me because it worked perfectly when paying in arrears and
also when I had no FV.

I had originally set up my table as you indicated, but I was having
problems getting it to work for payment in the beginning. I found
those formulas in a book and it seemed to work well.

Jim

 

[henrymusoke]

I seem to have the same problem but cannot understand why excel doesn't zero or happens to skip rows. giving different values.
I'm using, pmt, want to buy a house for $429900, just paid a 50% deposit, everything seems fine and clear but I'm stuck. interest rate is 4.35%, my decision is between 15 and 30 years. for 15 years rate is 3.55% and for 30 years is 4.35%. any suggestion please?

 

[PRutherford]

Answering an old thread since this ends up at the top of Google for this issue. More than likely the issue is you have an interest payment in the first period. Since first payment is due at the start of the loan there isn't time period where interest is applied. The entire first payment would be credited to principal.