Loan Amortization Table with Extra Loan Payment Calculation

[legalhustler]

I designed a amortization table (see link below). The top portion provides a summary of the loan, where the green color cells are hard coded input cells (except cell B8, because I do not know how to calculate the last year of a loan when a fixed extra amount is paid monthly).

Once the input cells have been complete in the summary section then the amortization below it updates accordingly; However, the amortization table goes into negative for the Principal Ending Balance column whenever I enter the "Extra Payment Amount per Month" in cell B7. The amortization table shows the correct payoff year when I do not have any extra payment amount per month. Can someone fix my formulas for each cell in the amortization table when there is fixed extra monthly payment inputted in cell B7 so that the correct information displays.

Dropbox - Loan Calculation.xlsx

Thanks.

 

[joeu2004]

Your primary mistake is using PPMT and IPMT to calculate the monthly principal and interest amounts. Those functions calculate the monthly payment internally using PMT. They do not rely on what you calculate in B9.

Instead, interest in I17 should be calculated as L17*$B$10. And principal in F17 should be calculated as E17-I17.

 

[legalhustler]

Your primary mistake is using PPMT and IPMT to calculate the monthly principal and interest amounts. Those functions calculate the monthly payment internally using PMT. They do not rely on what you calculate in B9.

Instead, interest in I17 should be calculated as L17*$B$10. And principal in F17 should be calculated as E17-I17.

Ok but I need a complete formula that accounts if I make a fixed extra monthly payment each month in cell B7.

 

[joeu2004]

Your primary mistake is using PPMT and IPMT to calculate the monthly principal and interest amounts. Those functions calculate the monthly payment internally using PMT. They do not rely on what you calculate in B9.

Instead, interest in I17 should be calculated as L17*$B$10. And principal in F17 should be calculated as E17-I17.

Ok but I need a complete formula that accounts if I make a fixed extra monthly payment each month in cell B7.

Change I17 to the following and copy down the column:

=IF(D17="","",L17*$B$10)

Change F17 to the following and copy down the column:

=IF(D17="","",E17-I17)

Note that E17 is usually the value in B9, and B9 combines the regular monthly loan payment (PMT) plus any fixed extra monthly payment in B7.