Term Loan Repayment Schedule with Moratorium Period in Excel

Creating a term loan repayment schedule with a moratorium period in Excel involves several key steps and considerations. This article will guide you through the process, explaining how to set up the schedule, calculate payments, and manage the moratorium period effectively.

Introduction to Term Loan Repayment Schedules

A term loan is a financial product where the borrower receives a lump sum of money and agrees to repay it over a fixed period. The repayment schedule is crucial for managing your loan efficiently. When a moratorium period is involved, it adds an extra layer of complexity to the schedule. This period is a break during which you do not make payments or make reduced payments.

Key Components of a Term Loan Repayment Schedule

1. Loan Amount: The total principal borrowed.
2. Interest Rate: The percentage of the loan amount charged as interest.
3. Term Length: The total duration of the loan.
4. Moratorium Period: The duration for which payments are postponed or reduced.
5. Repayment Frequency: The interval at which payments are made (e.g., monthly, quarterly).

Step-by-Step Guide to Creating a Repayment Schedule in Excel

1. Setting Up the Spreadsheet

Open Excel and create a new spreadsheet. Label the columns for clarity:

• A: Period
• B: Payment Date
• C: Beginning Balance
• D: Payment Amount
• E: Interest
• F: Principal
• G: Ending Balance

2. Entering Loan Details

In the first few rows, enter your loan details:

• Loan Amount: Enter the total amount borrowed in a cell, e.g., B1.
• Annual Interest Rate: Enter the annual interest rate in a cell, e.g., B2.
• Term Length (Months): Enter the loan term in months, e.g., B3.
• Moratorium Period (Months): Enter the duration of the moratorium period, e.g., B4.

3. Calculating Payment Amount

To calculate the monthly payment amount, use the PMT function in Excel:

scss
=PMT(interest_rate/12, total_periods, -loan_amount)

For example:

scss
=PMT(B2/12, B3, -B1)

This formula will give you the fixed monthly payment amount, which includes both principal and interest.

4. Setting Up the Payment Schedule

Start filling in the repayment schedule.

• Period: List each period (e.g., 1, 2, 3, ...).
• Payment Date: Enter the date of each payment.
• Beginning Balance: The initial loan amount for the first period. For subsequent periods, it will be the ending balance from the previous period.
• Payment Amount: The value calculated using the PMT function.
• Interest: Calculate the interest for the period using:

  scss
  Beginning Balance * (Annual Interest Rate / 12)
  

• Principal: Subtract the interest from the payment amount:

  Payment Amount - Interest
  

• Ending Balance: Subtract the principal payment from the beginning balance:

  Beginning Balance - Principal
  

5. Adjusting for the Moratorium Period

During the moratorium period, the schedule will differ:

• Payments: You may not make payments during this time, or you might make reduced payments.
• Interest Accrual: Interest may continue to accrue during the moratorium, increasing the balance.

Adjust the payment schedule to reflect these changes. For example:

• Set the payment amount to zero during the moratorium.
• Update the ending balance to include accrued interest.

Example Repayment Schedule

Period	Payment Date	Beginning Balance	Payment Amount	Interest	Principal	Ending Balance
1	01/01/2024	$100,000	$1,931.21	$833.33	$1,097.88	$98,902.12
2	02/01/2024	$98,902.12	$1,931.21	$824.18	$1,107.03	$97,795.09
...	...	...	...	...	...	...

Note: During the moratorium, adjust the schedule accordingly by skipping payments and adding interest accrued.

Managing Your Loan During the Moratorium

1. Monitor Interest Accrual: Keep track of the interest that accumulates during the moratorium period to understand how it affects your balance.
2. Plan for Increased Payments: Post-moratorium, you might need to increase payments to compensate for the additional interest.

Conclusion

Creating a term loan repayment schedule with a moratorium period in Excel requires careful planning and accurate calculations. By setting up a detailed schedule, adjusting for the moratorium, and monitoring your loan, you can manage your repayments effectively and avoid surprises.