Understanding how to compute the monthly reimbursement amount of a debt is crucial for financial planning. Let’s explore two methods to achieve this:
Method 1: The Formula
Using a straightforward formula, you can easily calculate the monthly reimbursement amount.
Méthode 2: Debt Amortization Schedule
This method involves computing the amount of capital reimbursed and the interest paid each month. Let’s create a table with headers and rows for each month (N = 60 for a 5-year term). The table will have six columns:
- Period: Sequential month number
- Beginning Loan Balance: Ending balance from the previous month
- Payment: Total Payment – Interest
- Interest: Calculate the interest paid
- Total Payment: we will find the correct value using Goal Seek
- End Loan Balance: Beginning balance minus the capital reimbursed
See below the formulas for each column:
To find the monthly reimbursement amount of €1,797, we use the Goal Seek function. Set the capital due to zero at the end of the period, and let Goal Seek adjust the monthly reimbursement amount accordingly.
With these calculations, you can simulate the financial results for any given period. For a P&L simulation, consider the interest paid. For cash flow or balance sheet simulations, use both the capital reimbursed and the interest paid.
Note: If you have a grace period (e.g., 6 months without payments), you can easily incorporate this into your simulation.
