Manually calculating interest and principal.

To perform calculations for lump sum payments, the interest and principal on a loan needs to be calculated manually. This means not using the IPMT() or PPMT() functions, and instead, calculating the interest by multiplying the opening balance by the periodic interest rate.

For this example, we will be working with a 2-year loan, amortizing monthly at a 5% interest rate with $100 in amortized fees.

The APR has already been calculated as part of the schedule, and opening and closing balance formulas have been entered.