Create the column headings and our opening balance ( pv ) for period 0.

Now calculate our first payment.

• Principal - We will be using Excel's function PPMT(). Notice the second parameter in the funtion is the period ( cell B12 )
• Interest - equals the total payment minus the principal portion of the payment. Note: we also could have used Excel's function IPMT().
• Balance - equals our previous balance minus the principal portion of the current payment. Note: since the principal portion is already negative, we add this amount to our previous balance.

Fill down this formula for the number of total payments ( nper ) in your loan. In our example we had a total of 12 payments.

Step 4: The Results