9. Payment Received – Interest
At this point, we are about to compute the monthly interests due on each installment payment. Recall that the formula for interest is the basic I = Principal x Rate x Time. For this purpose, however, the principal is based on the diminished balance; hence, the values used are retrieved from columns D and C. Encode the formula =(D10*$C$6)*C10/365 for Cell F10.Use the same rule by dragging this formula from F11 through F33, then press enter.
The rule is translated as--- Outstanding Balance, Beg (D10) x 5% Interest rate (C6) x Number of days (C10) over 365 days. Take note that the dollar ($) symbol for C6 was used, because the value for this cell remains constant throughout the F columns from F10 to F33. Each of these cells now has the capability to generate the monthly interest due from the borrower.
10. Payment Received Total
This column automatically calculates the installment amount or monthly amortization payments due from the borrower, by encoding the formula =$E$10+F10+G10 on Cell H10. Use the same procedure of dragging down the rule down to H33.
11. Payment Received – Penalty
Use this column for any penalty charges due in the event that the customer defaults in any installment payment. Although the input will not affect the balances, it would be best to compute this item manually since the values are likely to vary.
12. The Downloadable Sample
Users may choose to utilize the sample we used to illustrate the guidelines on how to make a loan amortization table in Excel by downloading a copy at Bright Hub's Media Gallery. One can simply modify the inputs specified in the earlier part of these guidelines. The cells with automated functions will work on the new data in accordance with the rules integrated in each unit to come up with the values. Still, it would be best to check them out randomly as well as prove the total of each column.