Calculate and budget loan repayments
- Denise McGettigan
You can use a Manual Entry driver tab to manage your loan data and calculate the loan repayment amount and the loan interest amount per period. You can then use that data as the basis of a loan financing mini-driver for connecting the Profit and Loss and Balance Sheet.
The video shows an example of how to do this.
The example uses the following data:
Loan Amount: $1,000,000
Repayment Term: 10 Years (120 Months)
Interest Rate: 6.25%
Start Month: December
The Manual Entry tab setup is as follows:
Row | Action | Details |
|---|---|---|
Interest Rate | Enter the interest rate | 6.25% per month |
Remaining Loan Amount | Use a formula to reference to the Loan Amount on the Balance Sheet tab | ($1,000,000 in the first year, starting in December) |
Interest | Use a formula to calculate the interest | = Remaining Loan Amount * ((Interest Rate / 100) / 12) |
Monthly Payment Amount Total | Use this logic to calculate the monthly payment: = (P * r * (1 + r)^n) / ((1 + r)^n - 1) Where: P is the loan amount (principal) | = (1000000 * 0.0625 / 12 * (1 + 0.0625 / 12)^120) / ((1 + 0.0625/12)^120 – 1) |
Principal | Use a formula to calculate the principal | = Monthly Repayment Amount Total - Interest |
