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 Standard Drivers article for more details on driver creation
Create the loan balance with a plan driver by going to:
Plans-> Drivers -> then scroll down to Balance Sheet
A - 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 in this driver.
B - The driver and rate are multiplied together resulting in a $1,500,000 increase to the Notes Payable account that was selected in A above.
C - The Start and End are the same month due to the loan being drawn down for in the selected month, in this instance April of 2020.
Create a plan driver to apply principle payments against the loan balance.
A - 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.
B - The driver and rate are multiplied together resulting in a $25,000 decrease to the Notes Payable account that was selected in A above.
C - 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.
Review the forecasted balance sheet detail by going to the planning table within:
A - 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 detail of the increase and decrease to the account that is rolling up into the balance.
B & C - These are the drivers we created to draw the loan down in April 2020 and to apply principle payments starting in May 2020. You can also view or edit the driver from the table by double-clicking in the row.
You will also note that the cash balance increases in April 2020 due to the liabilities being increased for the loan with cash decreasing $25,000 each month thereafter for the payments being made.
Go to Reports and navigate to a balance sheet report.
Create a plan driver for the monthly interest expense:
Plans-> Drivers -> then scroll down to Other Income & Expenses
A - Select the Account and Department in which you plan to book the actual interest expense against. Given that the account is an Income Statement account there is no need to select an increase or decrease to the account as the account is a periodic type account.
B - 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.
C - 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%.
D - The Start and End dates coincide with the loan term which in this example is 5 years as set up above.
Review the forecasted expense detail by going to the planning table within:
Ensure that the table is either selected to show "All" Departments or the selected output Department that was chosen during driver creation to see the impact.