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.
The example uses the following data:
Loan Amount: $1,000,000
Repayment Term: 10 Years (120 Months)
Interest Rate: 6.25%
Start Month: December
Add the Balance Sheet budget and enter the Loan Amount in the applicable month in the Long-Term Loan section.
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
The Manual Entry tab setup is as follows:
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) |
Interest | Use a formula to calculate the interest | = Remaining Loan Amount * ((Interest Rate / 100) / 12) |
Monthly Payment Amount Total | Use this logic to calculate the monthly payment: = (P * r * (1 + r)^n) / ((1 + r)^n - 1) Where: P is the loan amount (principal) | = (1000000 * 0.0625 / 12 * (1 + 0.0625 / 12)^120) / ((1 + 0.0625/12)^120 – 1) |
Principal | Use a formula to calculate the principal | = Monthly Repayment Amount Total - Interest |