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. Brief steps are outlined below.
Add a Manual Entry driver tab (in this example, the tab is called Loan Examples) and add rows for the loan data and to calculate the monthly payment, and interest and principal that will be repaid. See the tab setup below.
Back in the Balance Sheet budget, reference the data in the Manual Entry tab:
In the Long-Term Loan section, use a formula to reference the Loan Repayment (-Principal).
In the Bank section, use formulas to reference the Loan Amount ($1,000,000), the Repayment of Principal (-Principal) and Repayment of Interest (-Interest).
In the Profit and Loss budget (Main tab), in the Interest section, use a formula to enter the Interest Paid on Loan (-Interest).
Run scenarios as required:
In the Balance Sheet, change the Loan Amount and see how that impacts the Loan Repayment, Repayment of Principal and Repayment of Interest.
In the Manual Entry tab, change the loan assumptions, such as the Total Months or Interest Rate, and see how the impacts flow through the budget.
Manual Entry tab setup
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)
P is the loan amount (principal) r is the monthly interest rate (annual interest rate divided by 12) n is the total number of payment periods (number of years multiplied by 12)