Excel Amort table

A standard table as in a fixed rate loan schedule? I have no real estate experience, so please excuse me if my answer is not what you're looking for. With that being said, I'll add my two cents as to how I view amortization. If it helps you, great. If it doesn't help you, it will help someone else in the future.

Your payment in an amortization schedule shouldn't be decreasing by anything, your principal balance and interest part should be decreasing. Utilize Excel's "=PMT()" function to calculate the payment; this amount will stay constant.

For example, if this was a $100,000 5 year fixed rate loan at 5% with monthly payments it would come out to $1,887.12 (=PMT(5%/12,12*5,-100,000,0) Note, if each piece of this formula is referencing a cell, each cell reference should be fully locked ($A$1).

The interest part of this would simply be the principal balance multiplied by your interest rate divided by your payments per year. Maintaining the numbers from above, (=100,000*(5%/12)) you would have $416.67 as the interest part in period one. For this, the principal balance should not be locked, but if you are referencing cells for the IR and PPY they should be.

The principal part is easy, as you just subtract that period's interest part from the payment you first calculated =PMT(). This gives you $1,470.46 for your principal part in period one.

Finally, your principal balance will be last periods balance minus the current periods principal part. For period one, last periods balance is the original loan amount. This should be shown on a row noted as period 0. There will be no payments, interest part, or principal part shown in period 0. The calculation of principal balance should not be locked, as you need to reference the cells for this to work.

To reiterate, if done correctly, your payment will stay the same throughout the schedule, the interest part of your payment should decrease, and the principal part will increase. As this happens, your principal balance is reduced until 0 is shown in the final period.

For reference, your first 2 periods should calculate as shown below (hopefully no formatting errors occur upon posting). When you begin your schedule, I first suggest labeling each period in your first column. In this case it would go to 60 (it will always be your number of years multiplied by your payments per year). There are ways to make this dynamic with your inputs, but we'll keep this as simple as possible. If you built it out correctly, after dragging your cells down to the last period, you'll find a 0 in the last principal balance cell.

Hope this helps someone out there.

Period Payment Interest Part Principal Part Principal Balance 0 100,000.00 1 $1,887.12 $416.6 $1,470.46 98,529.54 2 $1,887.12 $410.54 $1,476.58 97,052.96