Use Jirav to forecast your loan balance and associated interest expense by creating plan drivers
This article walks through the steps to setting up a Loan balance, forecasting the Payments to the balance, and expensing the Interest.
Here are the Key Assumptions:
Loan Amount: $1.5M
Loan Term: 5 Years
Annual Interest Rate: 5%
Drawdown Month: April 2020
First Payment: May 2020
Reference: See our Balance Sheet Driver Articles for more details on driver creation
Step 1: Add a Driver Increase for Loan Addition
To create the Loan balance with a Balance Sheet Driver navigate to Plan > Drivers > Balance Sheet:
Click on + Add Driver and include the below inputs:
Name: Loan
Output: 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: Constant 1
fx: x
Rate: Constant $1,500,000
The Driver and Rate are multiplied together resulting in a $1,500,000 increase to the Notes Payable account that was selected above.
Div /0: default (On)
Start & End: 2024-04
The Start and End are the same month due to the loan being drawn down for the selected month, in this instance April 2024.
The Loan Balance Sheet Driver should look like below:
Step 2: Add a Driver Decrease for Loan Payments
To create a Balance Sheet Driver including Principle Payment against the loan balance, click on + Add Driver and include the below inputs:
Name: Loan Payment
Output: 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: Constant 1
fx: x
Rate: Constant $25,000
The Driver and Tate are multiplied together resulting in a $25,000 decrease to the Notes Payable account that was selected above.
Div /0: default (On)
Start: 2024-05
& End: 2029-04
The start month is the month in which the first payment is made and the end month is 60 months later, assuming this is a 5-year loan.
The Loan Balance Sheet Driver should look like below:
Step 3:
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 principle 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.
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 $25,000 each month after for the payments being made.
Go to Reports and navigate to a Balance Sheet Report.
Step 4:
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
Driver Name: Loan Interest
Input 1: Liabilities as Category, Account: Notes Payable, Line Type: Subtotal, Range: This Month, Reduction: Sum
The amount that will be Driving the expense is the balance of the Loan which is in the account we selected when we set up the Loan and associated Payments. Note that the Line Type is a Subtotal, meaning we would like to select the Cumulative balance of the account, not the changes that occurred in the month.
fx: x
Input 2: Constant 0.00416667
This is the Interest rate to be applied against the loan balance on a monthly basis. Note, that in the description we have noted that the per Annum Amount is 5% which divided by 12 equals 0.00416667.
Step 5:
Review the forecasted Interest Expense details
