Loan Schedule

Use Jirav to forecast your Loan balance and associated Interest Expenses through the Custom Table

This article walks through the steps to setting up a Loan balance schedule, forecasting the Payments to the balance, and expensing the Interest.

• Step 1: Create a Debt Schedule Custom table
• Step 2: Define related Assumptions
• Step 3: Populate Debt Assumptions
• Step 4: Add Drivers to the Debt Schedule Custom Table
• Step 5: Link Interest Expense to the Income Statement
• Step 6: Review Plan Liabilities
• Step 7: Review Balance Sheet Report

Here are the Key Assumptions that will be used for the Loan Schedule article:

Loan Amount: $1.000.000

Loan Term: 21 Months

Monthly Payment: $50.000

Annual Interest Rate: 5.38%

Drawdown Month: April 2024

First Payment: May 2024

Step 1: Create a Debt Schedule Custom table

To create Custom Tables navigate to Settings ⚙️> Custom Tables > click on Manage Tables > + Add Custom Table and call it as Debt Schedule:

Once the Debt Schedule Custom Table is created, add new Sections to it.

The Loan Assumptions Section should maintain the following Custom Lines:

• Interest Loan Addition with Money ($) Format and Sum as Period Value
• Monthly Interest Rate with Percent (%) Format and Average Period Value

Interest Loan Schedule Section should include Custom Lines such as:

• Beginning Balance with Money ($) Format and Ending Balance as Period Value
• Payment with Money ($) Format and Sum as Period Value
• Interests with Money ($) Format and Sum as Period Value
• Principal with Money ($) Format and Sum as Period Value
• Ending Balance with Money ($) Format and Ending Balance as Period Value

Step 2: Define related Assumptions

To create Assumptions for use in the Debt Schedule Custom Table go to Settings ⚙️ > Assumptions.

Define the Assumptions Table by clicking on the Manage Tables > + Add Assumption Table and call it Debt Assumptions:

Add Assumptions for Loan Addition $, Loan Monthly Payment $ and Loan Annual Interest Rate %:

Step 3: Populate Debt Assumptions

Once Debt Assumptions are defined we can go ahead and populate them by navigating to the 📈 Plan > Assumptions under the right Navigation Panel > All > Debt Assumptions:

Step 4: Add Drivers to the Debt Schedule Custom Table

Now when the Debt Schedule Custom Table is added, all the related Assumptions are defined and populated we need to add Drivers for the Forecast calculation. 

Based on our Key Assumptions list, we plan that the Drawdown Month is April 2024. To include that information under the Debt Schedule Custom table click on the ellipsis . . . next to the Interest Loan Addition and add Custom Plan Driver. Note that Debt Schedule can vary between Plans that's why we should use Plan Drivers, not Global Drivers:

Plan Driver Name: Loan Amount per Drawdown Month
Input 1: Key Assumptions ⋄ Debt Assumptions ⋄ Loan Addition $
fx: x
Input 2: Constant 1
Start Date Type: 2024 Apr
End Date Type: 2024 Apr


The Monthly Interest Rate should be calculated as the Annual Interest Rate Assumption divided by 12 months. This formula remains constant and doesn't change from Plan to Plan, so we can use a Global Driver this time:

Global Driver Name: Annual Interest Rate / 12
Input 1: Key Assumptions ⋄ Debt Assumptions ⋄ Loan Annual Interest Rate %
fx: /
Input 2: Constant 12
Start Date Type: Cutover Date
End Date Type: Max Date

Interest Loan Beginning Balance should include Interest Loan Addition and then going forward should be based on the Last Month's Ending Balance. The formula should look the same per each Plan so we can add Global Driver for this Custom Line.

Global Driver Name: Addition & LM Ending Balance
Input 1: Custom Tables ⋄ Debt Schedule ⋄ Interest Loan Addition
fx: +
Input 2: Custom Tables ⋄ Debt Schedule ⋄ Ending Balance with Range as Last Month
Start Date Type: Cutover Date
End Date Type: Max Date

Payment Custom Line should be based on the Loan Monthly Payment $ Assumption. The first Payment Date can vary depending on Plan so it's recommended to use Plan Driver. Based on the Loan Key Assumptions, we know that the first payment will take place in May 2024. In reference to that we need to adjust the Driver's Start Date Type to be 2024 May. The Loan Term is 21 Months, so the Driver's End Date Type is January 2026.

Plan Driver Name: Monthly Payment $ Assumption
Input 1: Key Assumptions ⋄ Debt Assumptions ⋄ Loan Monthly Payment $
fx: x
Input 2: Constant 1
Start Date Type: 2024 May
End Date Type: 2026 Jan

Interests are forecasted based on the formula as the Beginning Balance multiplied by the Monthly Interest Rate. Their start is the same as the first Payment so May 2024.

Plan Driver Name: Beginning Balance x Monthly Interest %
Input 1: Custom Tables ⋄ Debt Schedule ⋄ Beginning Balance 
fx: x
Input 2: Custom Tables ⋄ Debt Schedule ⋄ Monthly Interest Rate
Start Date Type: 2024 May
End Date Type: Max Date

The Principal should be calculated as a difference between Payment and Interest. The formula is the same so we can apply a Global Driver for this Custom Line. 

Global Driver Name: Payment - Interests
Input 1: Custom Tables ⋄ Debt Schedule ⋄ Payment 
fx: -
Input 2: Custom Tables ⋄ Debt Schedule ⋄ Interests
Start Date Type: Cutover Date
End Date Type: Max Date

The Ending Balance is the difference between the Beginning Balance and the Principal. The formula should be the same within all the Plans so we can use a Global Driver for this Custom Line either.

Global Driver Name: Beginning Balance - Principal
Input 1: Custom Tables ⋄ Debt Schedule ⋄ Beginning Balance 
fx: -
Input 2: Custom Tables ⋄ Debt Schedule ⋄ Principal
Start Date Type: Cutover Date
End Date Type: Max Date

Step 5: Link Interest Expense to the Income Statement

Create a Plan Driver for the Monthly Interest Expense by navigating to Plans > Other Section of the Income Statement. Click on . . . next to the Account (here: Interest Expense) and Department (here: G&A) in which you plan to book the actual Interest expense against.

Input 1 of the Driver should be simply based on the Interests Custom Line from the Debt Schedule Custom Table:

 

Step 5: Link the Interest Loan Schedule to the Balance Sheet

Loan's Addition and Principal should be linked to the Balance via Balance Sheet Drivers. Navigate to Plan > Drivers > Balance Sheet:

Click on + Add Driver to add Notes Payable Driver Increase:

Name: Loan Addition
Output: Liabilities $ ⋄ Notes Payable ⋄ Driver Increase
The Output of the Driver is the Balance Sheet Account that you plan to use when booking the actual loan into your accounting system.  Note that the output is a "Driver Increase" to the account; this is because we are taking the loan on this Driver.
Driver: Custom Tables ⋄ Debt Schedule ⋄ Interest Loan Addition
fx: x 
Rate: Constant 1
Div/0: On
Start: Cutover Date
End: Max Date

To create a Balance Sheet Driver including Principal Payment against the Loan balance, click on + Add Driver and include the below Inputs:

Name: Loan Principal
Output: Liabilities $ ⋄ Notes Payable ⋄ Driver Decrease
The Output of the Driver is the Balance Sheet Account that you used when you set up the loan draw. Note that the output is a "Driver Decrease" to the account; this is because we are paying down the loan.
Driver: Custom Tables ⋄ Debt Schedule ⋄ Principal
fx: x 
Rate: Constant 1
Div/0: On
Start: Cutover Date
End: Max Date

 

Step 6: Review Plan Liabilities 

Review the forecasted Balance Dheet detail by going to the planning table within Plan > Liabilities

Balance Sheet Driver Output: This is the row in the table that is the account "Notes Payable", which is what was selected as the Output when the drivers were created.  Note that the balance in the row for the forecasted months is additive of all the rows beneath it; in other words - Jirav is giving you all the details of the increase and decrease to the account that is rolling up into the balance.

Balance Sheet Driver Increase & Decrease - These are the Drivers we created to draw the Loan down in April 2024 and to apply Principal Payments starting in May 2024.  You can also view the Driver from the table by clicking once on the row. To edit the Driver you need to double-click on the row or click on ellipsis . . . next to the Driver > Edit Driver.

Step 7: Review Balance Sheet Report

You will also note that the Cash Balance increases in April 2024 due to the Liabilities being increased for the Loan with Cash decreasing by $50,000 each month after the payments are made. Interest Expense does not impact Cash as it decreases Cash through the Income Statement and increases Cash via Notes Payable balance increase (Principal is calculated as Payment less Interests), so the total Interest Expense Cash impact is netted to $0.

Go to Reports and navigate to a Balance Sheet Report: