Mac Numbers Amortization Calculate Extra Payment

What information do you need for the amortization with extra payment?

You need two pieces of information for every loan: the annual percentage rate (APR), and the loan amount. You use the APR to determine the interest amount per month. And the loan amount helps calculate the remaining principal.

How to set up an amortization schedule with extra payment in Mac Numbers?

Because you want to pay extra every month, your monthly payment will change from one period to the other. There is no single function in Mac Numbers able to do so. You must set up your Numbers sheet differently. The steps are as follows.

• Step 1: Create amortization schedule table
• Step 2: Create formula to calculate the interest
• Step 3: Create formula to calculate the paid principal
• Step 4: Create formula to calculate the remaining balance 

Your amortization schedule table will look like the below. The table header has the date of payment (Date), the custom payment amount (Payment), the interest amount (Interest), and the remaining balance (Remain Balance). 

The first row is the date you took out the loan. You did not make any payment on that date. Therefore, the Payment column, the Principal column, and the interest column is blank.

You enter a new line every time you make a payment. The formula for each line is as follows. The interest amount is the previous remaining balance multiplied by the APR and divided by 12.

After paying for the interest, the leftover in the payment amount is applied to the principal. So the principal amount is the payment subtracting the interest.

The principal will reduce your remaining balance. So the remaining balance for the new period is the previous remaining balance minus the current paid principal.

From now on, every month you make an extra payment, you copy the current line and paste it to a new line. Mac Numbers will copy all the formulas for you. Then you just change the Date and the Payment amount. Then Mac Numbers recalculates all the amounts for you.

The moment you see a zero in your remaining balance (or a near zero), you are free of debt. Congratulations! My complete amortization schedule looks like the screenshot below.